List of unique texts and numbers

[vsoler]

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?

 

[Bernie Deitrick]

v,

You can do this with three columns of formulas.

In cell B1, enter the formula

=IF(COUNTIF($A$1:A1,A1)=1,A1,"")

In cell C1, enter the formula

=IF(B1<>"",SUMPRODUCT(($B$1:$B$1000<>"")*($B$1:$B$1000<B1)*1)+1,"")

In cell D1, enter the formula

=IF(NOT(ISERROR(MATCH(ROW(),C:C,FALSE))),INDEX(B:B,MATCH(ROW(),C:C,FALSE)),"")

And copy all three cells down to match your list in column A


HTH,
Bernie
MS Excel MVP

 

[Teethless mama]

=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(INDIRECT("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(INDIRECT("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]

=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(INDIRECT("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(INDIRECT("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










- 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

 

[T. Valko]

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

=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(INDIRECT("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(INDIRECT("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










- 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

 

[Teethless mama]

This formula ignore blank cells in a range, and give you the unique texts and
numbers

"uniq" is a define name range, of course no quotes

=IF(ISERR(SMALL(IF(FREQUENCY(IF(uniq<>"",MATCH(uniq&"",uniq&"",0)),MATCH(uniq&"",uniq&"",0))>0,ROW(INDIRECT("1:"&ROWS(uniq)))),ROWS($1:1))),"",INDEX(uniq,SMALL(IF(FREQUENCY(IF(uniq<>"",MATCH(uniq&"",uniq&"",0)),MATCH(uniq&"",uniq&"",0))>0,ROW(INDIRECT("1:"&ROWS(uniq)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down as far as needed

 

[vsoler]

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







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

 

[T. Valko]

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







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

=SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&""))

It prevents empty cells from causing errors in the formula.

Let's break the formula down and see what's happening. Try this experiement.

A1 = leave this cell empty
A2 = X
A3 = Y
A4 = X
A5 = 1

Enter this formula in C1 and copy down to C5:

=A1<>""

Enter this formula in D1 and copy down to D5:

=COUNTIF(A$1:A$5,A1)

Enter this formula in E1 and copy down to E5:

=C1/D1

Enter this formula in F1:

=SUM(E1:E5)

As you'll see, the result is a #DIV/0! error.

Now, change the formula in D1 to:

=COUNTIF(A$1:A$5,A1&"")

Copy down to D5. See the difference?

Biff