Return Multiple Text Reslts Sorted Based Unique Values in another Column

I attach the test workbook. Please look at column E. That's results I'm looking for.

Put this array* formula in E2:

=IFERROR(INDEX($A$2:$A$15&$B$2:$B$15,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$15&$B$2:$B$15),0)),"")

and copy down until you get blanks.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual <Enter>.

Hope this helps.

Pete

Please look at this workbook.

I used this formula in cell E2: It almost works.

=INDEX(A$2:A$15,MATCH(SMALL(IF(A$2:A$15<>"",IF(MATCH(B$2:B$15,IF($A$2:$A$15<>"",B$2:B$15),0)=ROW(A$2:A$15)-ROW($B$2)+1,MMULT((A$2:A$15>TRANSPOSE(A$2:A$15))+0,ROW(A$2:A$15)^0))),ROWS(E$2:E2)),MMULT((A$2:A$15>TRANSPOSE(A$2:A$15))+0,ROW(A$2:A$15)^0),0))

But the formula doesn't give me the desired results. In cell F2 is what I'm after.

I think it's ok, because you have six unique serials not seven, so you have six EQ as result.
but maybe I'm wrong

Originally Posted by sandy666

I think it's ok, because you have six unique serials not seven, so you have six EQ as result.
but maybe I'm wrong

No it's actually 7. I colored the cells in colum A & B to show the results.

I was able to figure it out. Here's the working formula.


Input formula in cell F2 and copy down...


=INDEX(A$2:A$15,MATCH(SMALL(IF(A$2:A$15<>"",IF(MATCH(A$2:A$15&B$2:B$15,IF($A$2:$A$15<>"",A$2:A$15&B$2:B$15),0)=ROW(A$2:A$15)-ROW($B$2)+1,MMULT((A$2:A$15>TRANSPOSE(A$2:A$15))+0,ROW(A$2:A$15)^0))),ROWS(F$2:F2)),MMULT((A$2:A$15>TRANSPOSE(A$2:A$15))+0,ROW(A$2:A$15)^0),0))