# Sorting unique records with formula

#### cogumelo

##### Board Regular
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?

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

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

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

thank you both

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

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!

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.

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.

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!

Replies
1
Views
378
Replies
23
Views
773
Replies
1
Views
471
Replies
2
Views
1K
Replies
1
Views
441

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.

### Which adblocker are you using?

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

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