Arrgh-
I've been using the following snippet to create a list of unique values in a column. It works great as long as the list of unique values and the column being parsed are on the same table. I'm having the darndeest time doing this where the list is on one table in a workbook and the column of mixed values in another table in the same workbook. Here's the formula where both columns are on the same page:
{=IF(ISNA(OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)),"",OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1))}
The above goes in the second cell down (B2) where B1 would be the first value in the mixed column, (eg B1=A1)
...and here's the one I've been putzing with between pages:
=IF(ISNA(OFFSET(data!D2:D100,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(data!D2:D100)=A$3:A3),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)),"",OFFSET(data!D2:D100,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE(data!D2:D100)=A$3:A3),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1))
...where "data" is the name of the table with the mixed column.
Bookmarks