List of unique texts and numbers
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1: $A$4,0),MATCH($A$1:$A$4,$A$1:$A$4,0))0,ROW(INDIRE CT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))),"",INDEX($ A$1:$A$4,SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1:$ A$4,0),MATCH($A$1:$A$4,$A$1:$A$4,0))0,ROW(INDIREC T("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))))
Adjust your range to suit
ctrl+shift+enter, not just enter
copy down as far as needed
"vsoler" wrote:
In A1:A1000 I have cells with text and blank cells. Texts are often
repeated.
I would like in B1:B1000 all texts appearing at the top, without any
duplicates, blanl at the bottom.
What formula should I use in B1:B1000 ?
Thank you
A B
1 ABC ABC
2 XYZ XYZ
3 GHT GHT
4 GHT
5
Is it possible that B1:B1000 appear already sorted?
|