List of unique texts and numbers
On 19 mayo, 03:57, "T. Valko" wrote:
Try this:
Give your range of data a named range called rng.
Enter this formula in B1:
=SUMPRODUCT((rng<"")/COUNTIF(rng,rng&""))
Enter this array** formula in D1 and copy down until you get blanks:
=IF(ROWS($1:1)<=B$1,INDEX(rng,SMALL(IF(rng<"",IF( ROW(rng)-MIN(ROW(rng))+1=*MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"")
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
Biff
"vsoler" wrote in message
oups.com...
On 18 mayo, 19:28, Teethless mama
wrote:
=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(INDI RECT("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(I NDIRECT("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?- Ocultar texto de la
cita -
- Mostrar texto de la cita -
Teethless mama,
Your formula seems really interesting.
However, my range contains blank cells and I get #N/A as result.
Is there anyway to fix it?
Thanks in advance- Ocultar texto de la cita -
- Mostrar texto de la cita -
Une more question, if you don't mind:
Why do you add the expression &"" in your formula? for me it's a
mistery
|