# EXCEL - Extract unique entries

#### hhenrion

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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Richard Schollar

##### MrExcel MVP
Hi H

Have you considered using a Pivot Table?

Richard

#### hhenrion

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

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
Hi. Please notice that I have edited the formula.

#### Domenic

##### MrExcel MVP
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
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
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

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

I will have a look at it right away.

Replies
3
Views
206
Replies
5
Views
1K
Replies
1
Views
193
Replies
4
Views
185
Replies
2
Views
405

1,171,686
Messages
5,876,892
Members
433,217
Latest member

### 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