Return unique data from a list
Teethless mama wrote...
It is an array formula you have to commit with ctrl+shift+enter, not
just enter
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$2:$A$10,$A$ 2:$A$10,0),
MATCH($A$2:$A$10,$A$2:$A$10,0))0,
ROW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1)) ),"",
INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(MATCH($A$2:$A $10,$A$2:$A$10,0),
MATCH($A$2:$A$10,$A$2:$A$10,0))0,
ROW(INDIRECT("1:"&ROWS($A$2:$A$10)))),ROWS($1:1)) ))
....
Shorter formulas are possible. Since this formula would need to be
filled down into other cells (the ROWS($1:1) term gives this away),
why not use different formulas in the first and subsequent cells?
If the data to be condensed were in a range named D, and if the
topmost cell of the result range were G3, try
G3:
=T(D)
G4 [array formula]:
=IF(IF(G3<"",MAX(MATCH(D,D,0))MATCH(G3,D,0)),
INDEX(D,SMALL(IF(MATCH(D,D,0)=ROW(D)-MIN(ROW(D))+1,
ROW(D)-MIN(ROW(D))+1),ROWS(G$3:G4))),"")
Fill G4 down as far as needed. Even with all instances of D replaced
with $A$2:$A$10, this is still a smaller formula, it uses no volatile
function calls, and it avoids unnecessary ISERROR calls.
|