**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have a database extract that is showing the same clients multiple times in the list. How can I write a formula in excel to look at the column with the client names and return only unique records on another sheet? I want to run this over several files so wanting to avoid using pivot tables which is how I would normal do this.

Solved by X. J. in 20 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
10/08/2018 - 01:34

I have a database extract that is showing the same clients multiple times in the list. How can I write a formula in excel to look at the column with the client names and return only unique records on another sheet? I want to run this over several files so wanting to avoid using pivot tables which is how I would normal do this.

Excelchat Expert
10/08/2018 - 01:35

Hello, I understand that you need to extract a unique list off your column using a formula, right?

User
10/08/2018 - 01:35

yes

Excelchat Expert
10/08/2018 - 01:35

Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.

Excelchat Expert
10/08/2018 - 01:36

To be honest, getting the unique list is still best done using a Pivot Table but since you prefer a formula, we can do that as well, just a heads up. The formula will be an array formula which is notorious for slowing down entire sheets.

Excelchat Expert
10/08/2018 - 01:36

Are you able to share a sample file that we can work on?

User
10/08/2018 - 01:36

I better not

Excelchat Expert
10/08/2018 - 01:37

It doesn't have to be your actual file. Just a sample with the same format.

User
10/08/2018 - 01:37

but its just a list of 500 clients repeated over 4000 lines

Excelchat Expert
10/08/2018 - 01:37

Like sheet names, columns and starting rows.

Excelchat Expert
10/08/2018 - 01:37

That way, I can provide a formula that you can easily plug in.

User
10/08/2018 - 01:37

some a unique but some are there multiple times

User
10/08/2018 - 01:37

ok, one sec

User
10/08/2018 - 01:41

[Uploaded an Excel file]

Excelchat Expert
10/08/2018 - 01:41

Thank you. One moment.

Excelchat Expert
10/08/2018 - 01:43

About how many uniques do you expect?

User
10/08/2018 - 01:44

15

User
10/08/2018 - 01:44

Row Labels
Client 1
Client 10
Client 11
Client 12
Client 13
Client 14
Client 15
Client 16
Client 18
Client 19
Client 2
Client 204
Client 4
Client 5
Client 8
Grand Total

Excelchat Expert
10/08/2018 - 01:44

I meant, in your actual files?

User
10/08/2018 - 01:45

about 500

User
10/08/2018 - 01:45

maybe a few less

Excelchat Expert
10/08/2018 - 01:46

Please download this file.

[Uploaded an Excel file]

Excelchat Expert
10/08/2018 - 01:46

It will have the formula in Sheet2 that extracts the unique data from Sheet1.

User
10/08/2018 - 01:47

my company firewall is blocking the download

User
10/08/2018 - 01:47

can you tell me what formula you used?

User
10/08/2018 - 01:47

I can set the array up

Excelchat Expert
10/08/2018 - 01:47

Okay. One moment.

Excelchat Expert
10/08/2018 - 01:48

Use this in the sample file you sent me first. Cell B6

Excelchat Expert
10/08/2018 - 01:48

=IFERROR(INDEX(Sheet1!$B$7:$B$26,MATCH(0,COUNTIF(Sheet2!$B$5:B5,Sheet1!$B$7:$B$26),0)),"")

Excelchat Expert
10/08/2018 - 01:48

The formulas used are array formulas so they need to be confirmed by using CTRL+SHIFT+ENTER instead of just ENTER. You'll know that you've done it correct if the formula gets automatically enclosed in { }

User
10/08/2018 - 01:48

the array is called INDEX?

Excelchat Expert
10/08/2018 - 01:49

No, Index is just part of the array formula.

Excelchat Expert
10/08/2018 - 01:49

The entire formula is called an array formula because of the way it calculates the data.

User
10/08/2018 - 01:51

perfect

User
10/08/2018 - 01:51

it works, thanks

Excelchat Expert
10/08/2018 - 01:52

Glad to have helped. There is also another approach which does not use an array formula but I'm not very familiar with it.

Excelchat Expert
10/08/2018 - 01:52

Try this as well.

Excelchat Expert
10/08/2018 - 01:52

=IFERROR(LOOKUP(2,1/(COUNTIF($B$5:B5,Sheet1!$B$7:$B$26)=0),Sheet1!$B$7:$B$26),"")

Excelchat Expert
10/08/2018 - 01:52

I'll let you decide which one you want to use.

Excelchat Expert
10/08/2018 - 01:52

Would there be anything else that I can help you with regards to the original question?

User
10/08/2018 - 01:52

is there an advantage of using one over the other?

Excelchat Expert
10/08/2018 - 01:53

The second one does not require you to use CTRL+SHIFT+ENTER

Excelchat Expert
10/08/2018 - 01:53

Other than that, I haven't looked into it enough yet to offer a confident answer.,

User
10/08/2018 - 01:53

ok, thankyou

Excelchat Expert
10/08/2018 - 01:54

Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.

Excelchat Expert
10/08/2018 - 01:54

I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.

Excelchat Expert
10/08/2018 - 01:54

Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*