Removing duplicate rows

  • Thread starter Thread starter Roger Bell
  • Start date Start date
R

Roger Bell

I have inherited a spreadsheet which has lots of duplicated rows. However,
some information is missing for certain fields. For example one row will
list:
Firstname (Jim), Lastname (Jones), Phone (1213111) and the duplicate will
just have say the Firstname (Jim) & Lastname (Jones).

What I would like to do is remove the second record from the spread sheet.
There are many instances of this throughout.
Is there a way that this can be achieved?

Thanks for any help
 
You could use a helper column to do a counta of all the active cells in each
row, and then sort and delete all rows that have only two cells
occupied........

Vaya con Dios,
Chuck, CABGx3
 
I have inherited a spreadsheet which has lots of duplicated rows. However,
some information is missing for certain fields. For example one row will
list:
Firstname (Jim), Lastname (Jones), Phone (1213111) and the duplicate will
just have say the Firstname (Jim) & Lastname (Jones).

What I would like to do is remove the second record from the spread sheet.
There are many instances of this throughout.
Is there a way that this can be achieved?

Thanks for any help
Click to expand...

If Firstname is in column A, Lastname is in column B and Phone is in
column C, and assuming that the combination of First and Last Names is
enough to identify a unique record, then the following formula in the
second row of a spare column will return either Keep" or "Delete"...

=IF(OR(SUMPRODUCT(--($A$2:$A$10000=A2),--($B$2:$B
$10000=B2))=1,AND(SUMPRODUCT(--($A$2:$A$10000=A2),--($B$2:$B
$10000=B2))>1,COUNTA($A2:$C2)=3)),"Keep","Delete")

Fill this formula down to the last row of data (increase the 10000s if
data goes beyond row 10000)
It returns "Keep" in rows where there is a record that is unique or
there is a record that has been duplicated and contains information in
all three columns.
It returns "Delete" in rows where it is a duplicate of combined First
and Last names and the phone number is missing.
You could then use autofilter to hide the rows with "Delete" in that
column then copy the "Keep" rows.

Changes would need to be made to the formula if there are more than 3
fields, eg if there are 6 fields then the...

COUNTA($A2:$C2)=3 would need to be changed to...

COUNTA($A2:$F2)=6


Ken Johnson
 
Back
Top