Multiple Sheet Common Report

Courtney Caswell

I am looking to set up a report or another function that can pull any names that are on two different sheets. I have two different groups and I want to see if the name from someone on list A is also on list B. Is there a way to do this?

One list has about 2500 individuals and the other list has about 13,000.

M Underbrink

So on one sheet have a formula column that looks something like this,

=IF(COUNTIF({ReferenceNameColumnFromOtherSheet}, [Name Row on this sheet]@row)> 0, "DUPLICATE", "")

Basically this says in English - Count how many times this email/name is on the other sheet - if that count is greater than 0, then set the column to the text "Duplicate"

You can use conditional formatting to change the cell or row red/yellow based on the "Duplicate" text.

You can make this formula a "Column Formula" by right clicking it and selecting Column formula.

Let me know if it works!

Ramzi K

@Courtney Caswell

One way to do it -

Create a helper column in one of the sheets that does a countif on the other sheet for each name.

Any name with a countif >0 means the name exists in both.

You van then create a report off of one of the sheets that filters on the countif >0

I hope that helps.

Cheers,

Ramzi

M Underbrink

Yes, I believe you can do it, but it's not necessarily going to be pretty. 😂 . Are there duplicates of the names on each sheet?

Courtney Caswell

@M Underbrink Essentially what I am doing is making sure that the individuals on sheet A are not the same individuals as sheet B. This is for an exclusion project that I am working on and to start I have to go based on Firstname/LastName and then verify those that match with birthdays and such. No need to be pretty I just don't want to use access anymore!

M Underbrink

So on one sheet have a formula column that looks something like this,

=IF(COUNTIF({ReferenceNameColumnFromOtherSheet}, [Name Row on this sheet]@row)> 0, "DUPLICATE", "")

Basically this says in English - Count how many times this email/name is on the other sheet - if that count is greater than 0, then set the column to the text "Duplicate"

You can use conditional formatting to change the cell or row red/yellow based on the "Duplicate" text.

You can make this formula a "Column Formula" by right clicking it and selecting Column formula.

Let me know if it works!