Forum Discussion
Find duplicates of 2 columns
I cant seem to find anything specific to my question on finding duplicates so here goes I have a large spreadsheet of 80000+ rows and maybe 20 columns. Sample of what the first 6 columns look li...
Hi,
I am sure there is a much smarter formulaic method to do this but here is a crude way to do it. If you were to create a column 7 which is a concat of column 1 and column 5 (assuming you are looking for duplicates where col 1 and col 5 match). Then use conditional formatting in column 7 to look for duplicates. The rows that are then highlighted with your conditional formatting colors are rows with duplicate values. Again I am sure there is a much better way to accomplish this.
Best,
Bp
I am sure there is a much smarter formulaic method to do this but here is a crude way to do it. If you were to create a column 7 which is a concat of column 1 and column 5 (assuming you are looking for duplicates where col 1 and col 5 match). Then use conditional formatting in column 7 to look for duplicates. The rows that are then highlighted with your conditional formatting colors are rows with duplicate values. Again I am sure there is a much better way to accomplish this.
Best,
Bp
Bipin-prakash Thanks. That would definitely work for my immediate needs. I'll try that until anyone else has another way of doing this that might be a little more straightforward.
An alternative:
Select the data range. In the following, I will assume that it starts in row 2 and that the active cell in the selection is in row 2, for example A2.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=COUNTIFS($A:$A,$A2,$E:$E,$E2)>1
(A and E are the columns you want to look for duplicates in).
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
If you turn on Filter, you can filter by color to show only the duplicate rows:
