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.
