Duplicates and Replacement Formulas

[Guest]

Hello. I use Excell 2003 on my work comp. On this particular worksheet there
is a Column A for Last Names, a Column B for first names, a Column P for one
of the data resources, and a Column Q for the other data resource. There are
about 16,000 names here and I would be very grateful for anyone that might be
able to suggest a formula for looking up duplicate names and, if both entries
have an X in Column P then it could delete one of the 2 entries, and if one
entry has an X in both Column P and Column Q then it could delete the entry
with the X in Column P and add an X to column P of the second entry. I am
even willing to buy a seperate program if need be. I would be so grateful of
anyone with a solution. Thank You.

 

[Bob Phillips]

Don't buy new software, try this VBA

Sub Reformat()
Dim iLastRow As Long
Dim i As Long
Dim sFormula As String

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
sFormula = _
"=IF(SUMPRODUCT(--($A$1:$A$" & iLastRow & "=A2),--($B$1:$B$" & _
iLastRow & "=B2))>1,""Dup"","""")"

Columns("R:R").Insert Shift:=xlToRight
With Range("R1")
.Formula = "=ROW()"
.AutoFill Destination:=.Resize(iLastRow)
.Resize(iLastRow).Value = .Resize(iLastRow).Value
.Resize(iLastRow).EntireRow.Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End With

With Range("S2")
.Formula = sFormula
.AutoFill .Resize(iLastRow)
End With

For i = iLastRow To 2 Step -1
If Cells(i, "S").Value = "Dup" Then
If Cells(i, "P").Value = "X" And Cells(i, "q").Value = "X" And _
Cells(i - 1, "P").Value = "X" And Cells(i - 1, "Q").Value =
"X" Then
Rows(i).Delete
i = i - 1
ElseIf Cells(i, "P").Value = "X" And Cells(i - 1, "Q").Value =
"X" Then
Cells(i - 1, "P").Value = "X"
Rows(i).Delete
i = i - 1
Else
Rows(i - 1).Delete
i = i - 1
End If
End If
Next i

With Range("R1")
.Resize(iLastRow).EntireRow.Sort Key1:=Range("R2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlYes, _
DataOption1:=xlSortNormal
.Resize(, 2).EntireColumn.Delete
End With

End Sub

If it works, buy me a drink <vbg>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

I'll buy You dinner and drinks all night long if it does work! lol One
question though... Where should I insert that formula?

 

[Dave Peterson]

This is a macro.

Until Bob comes back and gives you more specific help, you may want to read
David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thank You so much! You get dinner and drinks also... lol I got everything
perfect but when i try to run the macro, I keep getting a runtime 1004 error
saying that the merged cells (the header cells) need to be identically
sized... I unmerged the cells and resized to make everything the same but
it's still say this... The part of the macro that it highlights is this:

..Resize(iLastRow).EntireRow.Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Please help one last time?

 

[Bob Phillips]

It is not a formula it is VBA, so you need to store it in a standard code
module. To do this, go to the VBIDE (Alt-F11), insert a new code module
(Insert>Module), and paste the code there.

Then in Excel, goto menu Tools>Macro>Macros... and select Reformat from the
list, and click Run.

You will need to test it carefully, so take a backup, as I wasn't absolutely
sure about the P/X, Q/X rules, but they can be easily adjusted.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thank You so much again... *smiles* I did everything as You said but I'm
getting this Runtime 1004 Error... The portion of the macro that it
highlights is:

..Resize(iLastRow).EntireRow.Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

What am I doing wrong? I've tried unmerging the cells and resizing them but
that's not seeming to work...

 

[Bob Phillips]

Not sure mate. I did test it as best I could and it worked.

Can you post me directly with your workbook?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)