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

Question

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

Answer 1

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

    'Change sheet name and top-left cell address to match your data.
    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

Answer 2

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: