EXCEL - Extract unique entries

[hhenrion]

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.

 

[Richard Schollar]

Hi H

Have you considered using a Pivot Table?

Richard

 

[hhenrion]

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]

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:

=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
	A	B	C	D
1	Name1		Name1
2	Name2		Name2
3	Name3		Name3
4	Name2		Name4
5	Name3		Name5
6	Name2		Name6
7	Name4		Name7
8	Name1		Name8
9	Name4
10	Name5
11	Name1
12	Name4
13	Name3
14	Name6
15	Name3
16	Name7
17	Name8
18	Name6
19	Name2
20
Sheet1

 

[pgc01]

Hi. Please notice that I have edited the formula.

 

[Domenic]

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]

Hi Domenic

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

Cheers
PGC

 

[Shinano]

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.

 

[Aladin Akyurek]

See also:

http://www.mrexcel.com/forum/showthread.php?t=360717

 

[Shinano]

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.