**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.*