Sorting unique records with formula

cogumelo

Board Regular
Joined
Mar 23, 2006
Messages
181
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Maybe like this, copy the formulas in A2 and C2 down the columns:
Excel Workbook
ABCD
1
21ESPANAESPANA
32ITALIAITALIA
42ITALIAUK
53UKGERMANY
63UKUSA
74GERMANY
84UK
94UK
105USA
115UK
125ITALIA
135ITALIA
145UK
155ITALIA
16
Sheet1
 
Upvote 0
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
 
Upvote 0
lewiy's solution is nearer the solution i'm looking for.

thank you both
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,214,285
Messages
6,118,679
Members
448,845
Latest member
MrEbzz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top