Find duplicate over a range.

[Lythande]

In sheet 2's A column I have about 700 rows of numbers. In sheet one in G column I have over 2000 rows of numbers. Tried Conditional Formatting>Highlight Cell Rules>Text That Contains...then selected the range of 700 rows in the other sheet. No luck--it seems I can only highlight one cell at a time for this to work and it's rather impractical.

Anyone know of a way to highlight duplicates in a column from a range in another column?
I bet it's something simple that I overlooked.

 

[Trevor G]

Have you considered using a CountIF formula that way you can determine if a cell has the same contents.

 

[Lythande]

Never tried Countif via selecting a bunch of cells in in a column, and not sure how it would work towards my goal. Can you elaborate?

 

[Trevor G]

The countif function can help identify if duplicate references appear in different lists, for example sheet one has a list of account numbers and you need to check to see how many times the account appears on another list in another sheet so you would select to create a countif formula in a blank column but refer to say cell A2 (first check) then the additional range would be selecting Sheet 2 and Column A (where the accounts are listed). You then get a number indicating how many times it is found, then fill the formula down by double clicking the AutoFill handler at the bottom right of the cell.

Then you could filter and do what you need to based on the results.

Small example shown here.

Excel Workbook
	A	B
1	Account Number	Countif
2	1234	4
3	2345	3
4	3456	3
5	3457	4
Sheet1

Excel Workbook
	A
1	Master list of Account Number
2	1234
3	2345
4	3457
5	1234
6	2345
7	3456
8	3457
9	1234
10	3456
11	3457
12	1234
13	2345
14	3456
15	3457
Sheet2