Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
    
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
up vote 1 down vote accepted

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
share|improve this answer

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:

enter image description here

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .