Duplicate rows (97)

Topic

New Reply

Is there a quick way to find if there are any duplicate rows on a worksheet?

I wish to find out using VBA code/formulae without doing a paste of unique values to another area ..

Ron

Reply | Quote

Replies

You ca add another column with a formula similar to
=COUNTIF(Whole_of used_range,Row_to_be_compared).
Enter it as an array formula (press crrtl-shift-enter) when you have entered it and it will return the number of times that the row occurs.
If you use the right combination absolute and relative range references, the formula will copy down to all affected rows.
(Whole_of used_range is an absolute reference, Row_to_be_compared is a relative reference)

eg
=COUNTIF($A$1:$D$5,A1:D1) (note that when is an array formula there will be ‘curly’ braces added to the formula by excel.

HIH

Reply | Quote