Sorting unique records with formula

[cogumelo]

hi.

I have this list o f countries:

ESPANA
ITALIA
ITALIA
UK
UK
GERMANY
UK
UK
USA
UK
ITALIA
ITALIA
UK
ITALIA

What i'm trying to do is (with a formula) run down the list and have a new list with every unique record, to get something like this:

ESPANA
ITALIA
UK
GERMANY
USA

Can it be done?


thx in advance,
cogumelo

 

[Lewiy]

Maybe like this, copy the formulas in A2 and C2 down the columns:

Excel Workbook
	A	B	C	D
1
2	1	ESPANA	ESPANA
3	2	ITALIA	ITALIA
4	2	ITALIA	UK
5	3	UK	GERMANY
6	3	UK	USA
7	4	GERMANY
8	4	UK
9	4	UK
10	5	USA
11	5	UK
12	5	ITALIA
13	5	ITALIA
14	5	UK
15	5	ITALIA
16
Sheet1

 

[ADAMC]

Hi,

You can use conditional formatting to highlight all the duplicates in your range:

Highlight your column> Choose format> Conditional formatting>
Formula is =COUNTIF(B:B,B1)>1
click format select a colour> Click OK

All duplicates will be your chosen colour you can then delete them all and be left with a unique list.

This site might help:

http://www.cpearson.com/excel/Duplicates.aspx

 

[cogumelo]

lewiy's solution is nearer the solution i'm looking for.

thank you both

 

[pgc01]

Hi cogumelo

lewiy's solution is nearer the solution i'm looking for.

In what is Lewiy's solution not yet what you're looking for?

Kind regards
PGC

 

[Domenic]

Assuming that A2:A15 contains the data, try the folloiwng formulas that need to be confirmed with CONTROL+SHIFT+ENTER...

C2:

=INDEX(A2:A15,MATCH(0,COUNTIF(A2:A15,"<"&A2:A15),0))

C3, copied down:

=IF(COUNTIF($A$2:$A$15,">"&C2),INDEX($A$2:$A$15,MATCH(COUNTIF($A$2:$A$15,"<="&C2),COUNTIF($A$2:$A$15,"<"&$A$2:$A$15),0)),"")

Hope this helps!

 

[cogumelo]

hi.

I was looking to do it in one formula only

Thanx for the solution you provided domenic

Lewiy's solution seems to be simpler.

 

[Johnny C]

If you want them as a single block, Lewiy's solution is better.

a simpler way still would be to copy a formula down column C

=if(iserror(match($B2,$C$1:$C1,0)),$B2,"")

But this will only pick out each new name on the line it occurs.

 

[Domenic]

hi.

I was looking to do it in one formula only

Thanx for the solution you provided domenic

Lewiy's solution seems to be simpler.

Sorry, I thought the unique records needed to be sorted in ascending order. Since they don't need to be sorted, here's another way...

C2:

=SUM(IF(A2:A14<>"",IF(MATCH("~"&A2:A14,A2:A14&"",0)=ROW(A2:A14)-ROW(A2)+1,1)))

...confirmed with CONTROL+SHIFT+ENTER

D2, copied down:

=IF(ROWS(D$2:D2)<=$C$2,INDEX($A$2:$A$14,SMALL(IF(MATCH("~"&$A$2:$A$14,$A$2:$A$14&"",0)=ROW($A$2:$A$14)-ROW($A$2)+1,ROW($A$2:$A$14)-ROW($A$2)+1),ROWS(D$2:D2))),"")

...confirmed with CONTROL+SHIFT+ENTER

Hope this helps!