EXCEL - Extract unique entries

hhenrion

Board Regular
Joined
Dec 19, 2005
Messages
73
Hi experts,

I have a list with many names, several of them appearing more than once. I would like to extract the unique entries an then make the list of them.

I can do that using the filter. But can I do it with formulas ? (In order to, whaterever change is made to the first list, get the extracted one immediately updated).

Thanks,
regards,

H.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

hhenrion

Board Regular
Joined
Dec 19, 2005
Messages
73
PIVOT TABLE

Actually not...

I am not very familiar with PIVOT tables.

And I am looking for a 'simple' extract.
I have something like 20 entries.
And the unique_entries list contains 5 names.

regards,

H.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi hhenrion

IUf you want a solution with formulas you have 2 options: with auxilliary columns or without auxilliary columns.

The first is more efficient. The second is more compact, depends only on the source data.

Since you did not say how many names you have I'll give you the compact solution. If you have lots of names this formula may slow down excel. In that case post again for a solution with auxilliary columns.

This is one possible solution. The result will automatically update when you change the source.

In C1:

Code:
=IF(SUMPRODUCT(IF($A$1:$A$19<>"",1/COUNTIF($A$1:$A$19,$A$1:$A$19)))<ROWS($A$1:A1),"",INDEX($A:$A,SMALL(IF($A$1:$A$19<>"",IF(COUNTIF(OFFSET($A$1,0,0,ROW($A$1:$A$19)-ROW($A$1)+1,1),$A$1:$A$19)=1,ROW($A$1:$A$19))),ROWS($A$1:A1))))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy down

Hope this helps
PGC

EDIT: Made the formula robust
NoDups.xls
ABCD
1Name1Name1
2Name2Name2
3Name3Name3
4Name2Name4
5Name3Name5
6Name2Name6
7Name4Name7
8Name1Name8
9Name4 
10Name5 
11Name1 
12Name4 
13Name3
14Name6
15Name3
16Name7
17Name8
18Name6
19Name2
20
Sheet1
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi. Please notice that I have edited the formula.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
Assuming that A2:A10 contains the data, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

B2, copied down:

=IF(OR(COUNTIF(B$1:B1,A2:A$10&"")=0),INDEX(A2:A$10,MATCH(0,COUNTIF(B$1:B1,A2:A$10&""),0)),"")

Hope this helps!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

Hi Domenic

Thanks for looking in and posting your great formula, so much shorter than mine and so much cleverer.

Cheers
PGC
 

Shinano

Board Regular
Joined
Dec 5, 2004
Messages
62
Domenic,

I am trying to make your formula work, but unsuccessfully so.

I have a task simular to hhenrio's at hand, and tried out your formula using pgc01's examples with a column of names. Name1, Name2, Name3 and so forth.

My list of multiple entries is the column A on Sheet1, and I need to have the list of unique entries in column A on Sheet2.

Could you walk me through your formula. -Or is there anybody else that can do it?

Obviously I am doing something wrong, but I am not sure what.

Thank you very much in advance.
 

Shinano

Board Regular
Joined
Dec 5, 2004
Messages
62
Hi Aladin,

Thank you very much for drawing my attention to this solution. I appreciate it.

I will have a look at it right away.
 

Forum statistics

Threads
1,137,204
Messages
5,680,160
Members
419,887
Latest member
Vasokir

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