# Excel: Remove lines with duplicates in column B, based on data in Column A

I need to find duplicates in Column B; and then delete the entire line if the the value in Column A is the number 4. There are lines that need to be kept and have "4" in Column A.

A  B
1, John
4, John
1, Mary
4, Mary
1, Aaron
1, Scott
4, Jeff
4, Bob

Should end up looking like this when I'm done:

A  B
1, John
1, Mary
1, Aaron
1, Scott
4, Jeff
4, Bob
-
What would you like to do if it is a duplicate in B but value in A is NOT 4? Your example does not have any of these, so as it stands you could simply sort on column A and use the duplicate removal tool to remove lines which have a dupe in B, and you would get the result you have shown. –  AdamV Jul 15 '11 at 8:03

Try this. Note that you need to change the sheet name and top-left cell address to match your data.

Sub RemoveRows()
Dim rng1 As Range, sht1 As Worksheet
Dim iRow As Long

Set sht1 = Sheets("Sheet1")
Set rng1 = sht1.Range("A1")

For iRow = rng1.End(xlDown).Row To rng1.Row Step -1
If Cells(iRow, 1) = 4 Then
If Application.WorksheetFunction.CountIf(rng1.Resize(rng1.End(xlDown).Row, 1).Offset(0, 1), Cells(iRow, 2)) > 1 Then
Cells(iRow, 1).EntireRow.Delete
End If
End If
Next iRow
End Sub
-

With a helper column and a pivot table, I was able to get your desired result. I assumed that the only values in column A can be 1 or 4. (Maybe that's a bad assumption???)

In C1, I put a column name "nameID".

In C2, I put the value 1.

In C3, I put this formula and copied it all the way down:

=IF(B3=B2,C2,C2+1)

Next, I pivoted the data in columns A:C, choosing the "min" of "a" option:

Finally, next to the pivot table, I wrote this concatenation formula, assuming the first pivot cell is F3:

=H3 & ", " & G3

Maybe this screenshot can help clarify. Final result is in column J:

-