Comparing two lists to find duplication

• I am trying to find duplication by comparing two lists. One list is a subset of the other. In other words, if they were side by side, they would not line up because one list may contain 15 rows where the other would contain 60.

Would I use a combination of the index and match function? If so, how would it look?

Thank

• Re: Comparing two lists to find duplication

thanks Tom,

I will check it out.

• Re: Comparing two lists to find duplication

Quote from bearcub

I am trying to find duplication by comparing two lists. One list is a subset of the other. In other words, if they were side by side, they would not line up because one list may contain 15 rows where the other would contain 60.

Would I use a combination of the index and match function? If so, how would it look?

Thank

You can use a countif() function to identify the duplicates. You can either use the function in a cell or with conditional formatting to highlight the duplicates

Take a look at the sample. The list on sheet 2 is compared to the list on sheet 1 and highlights the duplicates with conditional formatting. Col B on sheet 2 uses the countif() formula in the cell to id the duplicates (value greater than 0).

Note: To use the conditional formatting method you have to name the range if it is on a seperate sheet or workbook.

Files

• Re: Comparing two lists to find duplication

yes, I can see where the countif would work but I don't think it could be used to two non-merged lists, or could it?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!