Code help?

[Guest]

I have a data sample: 3000+ rows, and cols A:J. Col G is a phone number and
Col H is a qualifier. I'm looking for a macro that will look at col G,
identify all duplicate numbers then look at col H for duplicates within that
set. Example:

Col G Col H
Number Qualifier
123-456-7890 Apple
123-456-7890 Grape
123-456-7890 Apple

I want to cut and paste one of the "apple" rows into another sheet leaving 2
unique entries for that number. Is this possible?

Any help would be GREATLY appreciated. Thanks!

 

[Bernie Deitrick]

No need for a macro: in cell K2, use this formula (assumes that headings are in row 1, and data
starts in row2)

=SUMPRODUCT(($G$2:G2=G2)*($H$2:H2=H2))=1

Then copy that cell down to match your data set. Select your entire table, use Data / Filter /
Autofilter, then select the dropdown at the top of col K and choose TRUE. Then use Edit / Go To...
Special "Visible Cells" OK, copy using Ctrl-C, and then paste onto a new sheet.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Sub ABC()
Dim rng As Range, rng1 As Range
Set rng = Range("G2", Range("G2").End(xlDown))
rng.Offset(0, 4).Formula = "=G2&H2"
rng.Offset(0, 5).Formula = "=if(Countif($K$2:K2,K2)>1,na(),1)"
On Error Resume Next
Set rng1 = rng.Offset(0, 5).SpecialCells(xlFormulas, xlErrors)
rng1.Select
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.EntireRow.Copy Worksheets(2).Range("A2")
rng1.EntireRow.Delete
Range("K:L").Delete
Worksheets("sheet2").Range("K:L").Delete
Else
Range("K:L").Delete
End If

End Sub

worked for me.

Test it on a copy of your data.