Question description:
This user has given permission to use the problem statement for this
blog.
I have three spreadsheets with names of registered people for an event. I need to combine them and then filter so that I know if any of the people in sheet 1 are also listed on sheet 2 and sheet 3
Solved by C. A. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/06/2018 - 01:19
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern.
User
16/06/2018 - 01:19
Thank you
Excelchat Expert
16/06/2018 - 01:20
I understand that you need help with consolidating data to check for duplicates is that correct? May you please forward the file you are working on so I can help you with your concern?
User
16/06/2018 - 01:20
Yes that's what I need
Excelchat Expert
16/06/2018 - 01:20
Got it. I can help you with that. Before we proceed, just a friendly 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. Thank you for understanding.
Excelchat Expert
16/06/2018 - 01:21
Can you forward the file you're working on?
User
16/06/2018 - 01:22
Can you not just list the steps I need to take to accompish that
Excelchat Expert
16/06/2018 - 01:22
Alright, but I would need an idea how your worksheet looks like to make sure I can give you the right solution.
Excelchat Expert
16/06/2018 - 01:23
Can you paste a sample data of how it looks like on the Document Preview?
Excelchat Expert
16/06/2018 - 01:24
Are the headers/columns for your data from Sheet 1-3 the same?
User
16/06/2018 - 01:24
Yes. They are all Name
Excelchat Expert
16/06/2018 - 01:25
Just to verify, are you using excel or google sheet for your file?
User
16/06/2018 - 01:25
Excel
Excelchat Expert
16/06/2018 - 01:26
Alright, last request, can you put sample data in Sheet3 as well?
Excelchat Expert
16/06/2018 - 01:26
Thank you
User
16/06/2018 - 01:27
So what steps do I need to take
Excelchat Expert
16/06/2018 - 01:29
Alright, do you have your data open now?
User
16/06/2018 - 01:29
yes
Excelchat Expert
16/06/2018 - 01:31
Please give me a moment to test the solution on my end first, thank you
User
16/06/2018 - 01:31
ok
Excelchat Expert
16/06/2018 - 01:36
Ok Here's the step to follow:
Excelchat Expert
16/06/2018 - 01:37
Combine all the data first into 1 sheet named "Master Sheet"
Excelchat Expert
16/06/2018 - 01:38
You can add new header in Master Sheet for the Location or Sheet Name where your data came from
User
16/06/2018 - 01:39
ok
Excelchat Expert
16/06/2018 - 01:40
Once you are able to combine it. add another column in Master Sheet named Full Name
Excelchat Expert
16/06/2018 - 01:40
We will combine last name and full name so we can check duplicates by full name
Excelchat Expert
16/06/2018 - 01:41
This is the formula to do that:
Excelchat Expert
16/06/2018 - 01:41
=CONCATENATE(A2,B2)
Excelchat Expert
16/06/2018 - 01:41
Put it in cell D2 in Master Sheet
Excelchat Expert
16/06/2018 - 01:41
Then drag down the formula to the last row
User
16/06/2018 - 01:42
Okay Got that
Excelchat Expert
16/06/2018 - 01:42
Let me know if you're done
Excelchat Expert
16/06/2018 - 01:43
Just to verify further, you only need to find duplicate for Sheet 1-3 or you also need to find duplicate within 1 sheet (Ex. within Sheet1 only)
User
16/06/2018 - 01:44
I am needing to know if any of the names on sheet 2 or 3 are also on sheet 1
Excelchat Expert
16/06/2018 - 01:44
Ok here's the formula: =COUNTIF(D:D,D2)
Excelchat Expert
16/06/2018 - 01:45
Add that in Column E after the Full Name
Excelchat Expert
16/06/2018 - 01:45
Name it as COUNT OF DUPLICATE
Excelchat Expert
16/06/2018 - 01:45
Then drag the formula to last row
User
16/06/2018 - 01:46
That formula didnt work
Excelchat Expert
16/06/2018 - 01:46
What do you mean?
Excelchat Expert
16/06/2018 - 01:47
It is working on our sample data in Document Preview
User
16/06/2018 - 01:47
I only see blank cells on the document preview
Excelchat Expert
16/06/2018 - 01:48
Maybe the file is still loading, did you try the formula in excel?
User
16/06/2018 - 01:48
Yes, it shows all 0's when I use it
Excelchat Expert
16/06/2018 - 01:49
Ok, I will try and send you the file on my end
Excelchat Expert
16/06/2018 - 01:49
It should show 1 if it doesn't have any duplicate, then 2 and above if it has duplicate
User
16/06/2018 - 01:50
I got it
Excelchat Expert
16/06/2018 - 01:53
Here you go
[Uploaded an Excel file]
Excelchat Expert
16/06/2018 - 01:53
Here's a sample of the file with the formulas and I also added a Pivot Chart where you can see a summary of Names and the Sheet where that name appear
User
16/06/2018 - 01:54
Thank you
Excelchat Expert
16/06/2018 - 01:54
You're welcome!
Excelchat Expert
16/06/2018 - 01:54
Is there anything else that I can assist you with regarding this issue?
Excelchat Expert
16/06/2018 - 01:57
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon.. I sincerely hope we were able to address your concern.
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.
