Search code examples
excelexcel-formuladuplicatesdelete-rowworksheet-function

Delete duplicate rows based on 2 column values

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.

Solution

  • 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