0

I have this Excel archive with 5 columns of data, and then, about 4,000 rows of different data for each column, but I can’t find a way to make that Excel marks the repeated rows?

Excel I have tried using the Conditional Formating Rules, but it only marks the repeated cells, not rows.

1

You could try applying the concatenate function before using conditional formatting. http://blog.contextures.com/archives/2013/04/11/highlight-duplicate-records-in-an-excel-list/

| improve this answer | |
  • Thank you so much, that Concatenate formula is awesome :D – Victor F Salazar G Dec 3 '14 at 5:46
1

Go back to Conditional Formatting and delete any prior rules you have created for this purpose, then (full columns A:H selected) create a new rule using the option to Use a formula to determine which cells to format. Supply the following to the Format values where this formula is true textbox:

=COUNTIFS($C:$C,$C1,$D:$D,$D1,$E:$E,$E1,$F:$F,$F1,$G:$G,$G1)>1

You likened your requirement to the existing Format only unique or duplicate items options so I assumed that you wanted both the first and any subsequent duplicates highlighted.

Caveat: If you have more than ~1000 rows then you may experience some latent calculation lag. This could be reduced with a helper column holding a concatenated value as mentioned in the previous reply. Which method you choose will depend upon the number of row within the conditional formatting rule's Applies to: region and what you feel you can live with.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.