Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.