Delete duplicate rows based on 2 column values

0
votes

I have a sheet with 8 columns A to H and over 1000 rows. I would like to delete all rows that has duplicate values in column A and check if column H is an empty (zero value) cell.

If the column H is empty then delete the entire row, not the row that has a value in column H.

Example -
Row 1 has ABC in column A and MyText in column H
Row 2 has ABC in column A and no value in column H
Delete row 2 as the duplicate.

1
excelexcel-formuladuplicatesdelete-rowworksheet-function

1 Answers

1
votes

Use this formula in I1:

=AND(COUNTIF(A:A,A1)>1,H1=0)

Then delete only rows where in I column you get TRUE

Detailed steps

  1. Create the formula:

enter image description here

  1. Create 1 row at the top

  2. Select everything including the first row

  3. "Data" -> "Filter"

  4. Leave only TRUE on column I

  5. Select those rows:

enter image description here

  1. "Home" -> "Delete"

enter image description here