Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Australia
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate rows (97)

    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

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate rows (97)

    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,A11) (note that when is an array formula there will be 'curly' braces added to the formula by excel.

    HIH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •