Return Sorted Unique Values From 2 Columns Using Functions

[tomas777]

Hi,

I think the title and the attached workbook are self-explanatory.

Thanks a lot for help

Tomas

[Gregb11]

Try:
=SORT(UNIQUE(IFERROR(INDEX(Table1[Product],SEQUENCE(ROWS(Table1[Product])+ROWS($D$4:$D$11))),INDEX($D$4:$D$11,SEQUENCE(ROWS(Table1[Product])+ROWS($D$4:$D$11))-ROWS(Table1[Product])))))

[Gregb11]

Another option:

=SORT(UNIQUE(LET(w,CHOOSE({1,2},Table1[Product],$D$4:$D$11),x,COLUMNS(w),y,COUNTA(w),z,IFERROR(INDEX(w,1+MOD(SEQUENCE(x*y,,0),y),1+INT(SEQUENCE(x*y,,0)/y)),""),FILTER(z,z<>""))))

[Fluff13]

Another option

Please Login or Register  to view this content.

[romperstomper]

Is E supposed to be before D? If not, and if you are on Insider Beta with the new functions:

=SORT(UNIQUE(TOCOL((Table1[Product],D4:D11))))

[wk9128]

There are two conflicts here. The description is different from the requirements for painting in yellow. It is only done according to the H4:H8 provided by the OP? The description is not considered.

Cell H4 formula

Please Login or Register  to view this content.

[Gregb11]

@rorya - Now you're just showing off
Can't wait to get those new formulas

[romperstomper]

@rorya - Now you're just showing off
Can't wait to get those new formulas

If it makes you feel better, I still have to use 2016 at work, which is very frustrating...