Sorting unique records with formula

cogumelo

Board Regular
Joined
Mar 23, 2006
Messages
175
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,166
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

Board Regular
Joined
Mar 23, 2006
Messages
175
Office Version
  1. 365
Platform
  1. Windows
lewiy's solution is nearer the solution i'm looking for.

thank you both
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,877

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

Board Regular
Joined
Mar 23, 2006
Messages
175
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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
Top