Excel - General Question on Pivot Table - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.