software

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.

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, quot;Aquot;).End(xlUp).Row
sFormula = _
quot;=IF(SUMPRODUCT(--($A$1:$A$quot; amp; iLastRow amp; quot;=A2),--($B$1:$B$quot; amp; _
iLastRow amp; quot;=B2))gt;1,quot;quot;Dupquot;quot;,quot;quot;quot;quot;)quot;

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

With Range(quot;S2quot;)
.Formula = sFormula
.AutoFill .Resize(iLastRow)
End With

For i = iLastRow To 2 Step -1
If Cells(i, quot;Squot;).Value = quot;Dupquot; Then
If Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i, quot;qquot;).Value = quot;Xquot; And _
Cells(i - 1, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value =
quot;Xquot; Then
Rows(i).Delete
i = i - 1
ElseIf Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value =
quot;Xquot; Then
Cells(i - 1, quot;Pquot;).Value = quot;Xquot;
Rows(i).Delete
i = i - 1
Else
Rows(i - 1).Delete
i = i - 1
End If
End If
Next i

With Range(quot;R1quot;)
.Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;R2quot;), _
Order1:=xlAscending, _
Key2:=Range(quot;B2quot;), _
Order2:=xlAscending, _
Header:=xlYes, _
DataOption1:=xlSortNormal
.Resize(, 2).EntireColumn.Delete
End With

End Sub

If it works, buy me a drink lt;vbggt;

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
...
gt; Hello. I use Excell 2003 on my work comp. On this particular worksheet
there
gt; is a Column A for Last Names, a Column B for first names, a Column P for
one
gt; of the data resources, and a Column Q for the other data resource. There
are
gt; about 16,000 names here and I would be very grateful for anyone that might
be
gt; able to suggest a formula for looking up duplicate names and, if both
entries
gt; have an X in Column P then it could delete one of the 2 entries, and if
one
gt; entry has an X in both Column P and Column Q then it could delete the
entry
gt; with the X in Column P and add an X to column P of the second entry. I am
gt; even willing to buy a seperate program if need be. I would be so grateful
of
gt; anyone with a solution. Thank You.
I'll buy You dinner and drinks all night long if it does work! lol One
question though... Where should I insert that formula?

quot;Bob Phillipsquot; wrote:

gt; Don't buy new software, try this VBA
gt;
gt; Sub Reformat()
gt; Dim iLastRow As Long
gt; Dim i As Long
gt; Dim sFormula As String
gt;
gt; iLastRow = Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
gt; sFormula = _
gt; quot;=IF(SUMPRODUCT(--($A$1:$A$quot; amp; iLastRow amp; quot;=A2),--($B$1:$B$quot; amp; _
gt; iLastRow amp; quot;=B2))gt;1,quot;quot;Dupquot;quot;,quot;quot;quot;quot;)quot;
gt;
gt; Columns(quot;R:Rquot;).Insert Shift:=xlToRight
gt; With Range(quot;R1quot;)
gt; .Formula = quot;=ROW()quot;
gt; .AutoFill Destination:=.Resize(iLastRow)
gt; .Resize(iLastRow).Value = .Resize(iLastRow).Value
gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;A2quot;), _
gt; Order1:=xlAscending, _
gt; Key2:=Range(quot;B2quot;), _
gt; Order2:=xlAscending, _
gt; Header:=xlYes, _
gt; OrderCustom:=1, _
gt; MatchCase:=False, _
gt; Orientation:=xlTopToBottom, _
gt; DataOption1:=xlSortNormal, _
gt; DataOption2:=xlSortNormal
gt; End With
gt;
gt; With Range(quot;S2quot;)
gt; .Formula = sFormula
gt; .AutoFill .Resize(iLastRow)
gt; End With
gt;
gt; For i = iLastRow To 2 Step -1
gt; If Cells(i, quot;Squot;).Value = quot;Dupquot; Then
gt; If Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i, quot;qquot;).Value = quot;Xquot; And _
gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value =
gt; quot;Xquot; Then
gt; Rows(i).Delete
gt; i = i - 1
gt; ElseIf Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value =
gt; quot;Xquot; Then
gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot;
gt; Rows(i).Delete
gt; i = i - 1
gt; Else
gt; Rows(i - 1).Delete
gt; i = i - 1
gt; End If
gt; End If
gt; Next i
gt;
gt; With Range(quot;R1quot;)
gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;R2quot;), _
gt; Order1:=xlAscending, _
gt; Key2:=Range(quot;B2quot;), _
gt; Order2:=xlAscending, _
gt; Header:=xlYes, _
gt; DataOption1:=xlSortNormal
gt; .Resize(, 2).EntireColumn.Delete
gt; End With
gt;
gt; End Sub
gt;
gt; If it works, buy me a drink lt;vbggt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
gt; ...
gt; gt; Hello. I use Excell 2003 on my work comp. On this particular worksheet
gt; there
gt; gt; is a Column A for Last Names, a Column B for first names, a Column P for
gt; one
gt; gt; of the data resources, and a Column Q for the other data resource. There
gt; are
gt; gt; about 16,000 names here and I would be very grateful for anyone that might
gt; be
gt; gt; able to suggest a formula for looking up duplicate names and, if both
gt; entries
gt; gt; have an X in Column P then it could delete one of the 2 entries, and if
gt; one
gt; gt; entry has an X in both Column P and Column Q then it could delete the
gt; entry
gt; gt; with the X in Column P and add an X to column P of the second entry. I am
gt; gt; even willing to buy a seperate program if need be. I would be so grateful
gt; of
gt; gt; anyone with a solution. Thank You.
gt;
gt;
gt;

This is a macro.

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

Narianna wrote:
gt;
gt; I'll buy You dinner and drinks all night long if it does work! lol One
gt; question though... Where should I insert that formula?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Don't buy new software, try this VBA
gt; gt;
gt; gt; Sub Reformat()
gt; gt; Dim iLastRow As Long
gt; gt; Dim i As Long
gt; gt; Dim sFormula As String
gt; gt;
gt; gt; iLastRow = Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
gt; gt; sFormula = _
gt; gt; quot;=IF(SUMPRODUCT(--($A$1:$A$quot; amp; iLastRow amp; quot;=A2),--($B$1:$B$quot; amp; _
gt; gt; iLastRow amp; quot;=B2))gt;1,quot;quot;Dupquot;quot;,quot;quot;quot;quot;)quot;
gt; gt;
gt; gt; Columns(quot;R:Rquot;).Insert Shift:=xlToRight
gt; gt; With Range(quot;R1quot;)
gt; gt; .Formula = quot;=ROW()quot;
gt; gt; .AutoFill Destination:=.Resize(iLastRow)
gt; gt; .Resize(iLastRow).Value = .Resize(iLastRow).Value
gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;A2quot;), _
gt; gt; Order1:=xlAscending, _
gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; Order2:=xlAscending, _
gt; gt; Header:=xlYes, _
gt; gt; OrderCustom:=1, _
gt; gt; MatchCase:=False, _
gt; gt; Orientation:=xlTopToBottom, _
gt; gt; DataOption1:=xlSortNormal, _
gt; gt; DataOption2:=xlSortNormal
gt; gt; End With
gt; gt;
gt; gt; With Range(quot;S2quot;)
gt; gt; .Formula = sFormula
gt; gt; .AutoFill .Resize(iLastRow)
gt; gt; End With
gt; gt;
gt; gt; For i = iLastRow To 2 Step -1
gt; gt; If Cells(i, quot;Squot;).Value = quot;Dupquot; Then
gt; gt; If Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i, quot;qquot;).Value = quot;Xquot; And _
gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value =
gt; gt; quot;Xquot; Then
gt; gt; Rows(i).Delete
gt; gt; i = i - 1
gt; gt; ElseIf Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value =
gt; gt; quot;Xquot; Then
gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot;
gt; gt; Rows(i).Delete
gt; gt; i = i - 1
gt; gt; Else
gt; gt; Rows(i - 1).Delete
gt; gt; i = i - 1
gt; gt; End If
gt; gt; End If
gt; gt; Next i
gt; gt;
gt; gt; With Range(quot;R1quot;)
gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;R2quot;), _
gt; gt; Order1:=xlAscending, _
gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; Order2:=xlAscending, _
gt; gt; Header:=xlYes, _
gt; gt; DataOption1:=xlSortNormal
gt; gt; .Resize(, 2).EntireColumn.Delete
gt; gt; End With
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; If it works, buy me a drink lt;vbggt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
gt; gt; ...
gt; gt; gt; Hello. I use Excell 2003 on my work comp. On this particular worksheet
gt; gt; there
gt; gt; gt; is a Column A for Last Names, a Column B for first names, a Column P for
gt; gt; one
gt; gt; gt; of the data resources, and a Column Q for the other data resource. There
gt; gt; are
gt; gt; gt; about 16,000 names here and I would be very grateful for anyone that might
gt; gt; be
gt; gt; gt; able to suggest a formula for looking up duplicate names and, if both
gt; gt; entries
gt; gt; gt; have an X in Column P then it could delete one of the 2 entries, and if
gt; gt; one
gt; gt; gt; entry has an X in both Column P and Column Q then it could delete the
gt; gt; entry
gt; gt; gt; with the X in Column P and add an X to column P of the second entry. I am
gt; gt; gt; even willing to buy a seperate program if need be. I would be so grateful
gt; gt; of
gt; gt; gt; anyone with a solution. Thank You.
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson

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(quot;A2quot;), _
Order1:=xlAscending, _
Key2:=Range(quot;B2quot;), _
Order2:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

Please help one last time?

quot;Dave Petersonquot; wrote:

gt; This is a macro.
gt;
gt; Until Bob comes back and gives you more specific help, you may want to read
gt; David McRitchie's intro at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt; Narianna wrote:
gt; gt;
gt; gt; I'll buy You dinner and drinks all night long if it does work! lol One
gt; gt; question though... Where should I insert that formula?
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Don't buy new software, try this VBA
gt; gt; gt;
gt; gt; gt; Sub Reformat()
gt; gt; gt; Dim iLastRow As Long
gt; gt; gt; Dim i As Long
gt; gt; gt; Dim sFormula As String
gt; gt; gt;
gt; gt; gt; iLastRow = Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
gt; gt; gt; sFormula = _
gt; gt; gt; quot;=IF(SUMPRODUCT(--($A$1:$A$quot; amp; iLastRow amp; quot;=A2),--($B$1:$B$quot; amp; _
gt; gt; gt; iLastRow amp; quot;=B2))gt;1,quot;quot;Dupquot;quot;,quot;quot;quot;quot;)quot;
gt; gt; gt;
gt; gt; gt; Columns(quot;R:Rquot;).Insert Shift:=xlToRight
gt; gt; gt; With Range(quot;R1quot;)
gt; gt; gt; .Formula = quot;=ROW()quot;
gt; gt; gt; .AutoFill Destination:=.Resize(iLastRow)
gt; gt; gt; .Resize(iLastRow).Value = .Resize(iLastRow).Value
gt; gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;A2quot;), _
gt; gt; gt; Order1:=xlAscending, _
gt; gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; gt; Order2:=xlAscending, _
gt; gt; gt; Header:=xlYes, _
gt; gt; gt; OrderCustom:=1, _
gt; gt; gt; MatchCase:=False, _
gt; gt; gt; Orientation:=xlTopToBottom, _
gt; gt; gt; DataOption1:=xlSortNormal, _
gt; gt; gt; DataOption2:=xlSortNormal
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; With Range(quot;S2quot;)
gt; gt; gt; .Formula = sFormula
gt; gt; gt; .AutoFill .Resize(iLastRow)
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; For i = iLastRow To 2 Step -1
gt; gt; gt; If Cells(i, quot;Squot;).Value = quot;Dupquot; Then
gt; gt; gt; If Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i, quot;qquot;).Value = quot;Xquot; And _
gt; gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value =
gt; gt; gt; quot;Xquot; Then
gt; gt; gt; Rows(i).Delete
gt; gt; gt; i = i - 1
gt; gt; gt; ElseIf Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value =
gt; gt; gt; quot;Xquot; Then
gt; gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot;
gt; gt; gt; Rows(i).Delete
gt; gt; gt; i = i - 1
gt; gt; gt; Else
gt; gt; gt; Rows(i - 1).Delete
gt; gt; gt; i = i - 1
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt; Next i
gt; gt; gt;
gt; gt; gt; With Range(quot;R1quot;)
gt; gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;R2quot;), _
gt; gt; gt; Order1:=xlAscending, _
gt; gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; gt; Order2:=xlAscending, _
gt; gt; gt; Header:=xlYes, _
gt; gt; gt; DataOption1:=xlSortNormal
gt; gt; gt; .Resize(, 2).EntireColumn.Delete
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; If it works, buy me a drink lt;vbggt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hello. I use Excell 2003 on my work comp. On this particular worksheet
gt; gt; gt; there
gt; gt; gt; gt; is a Column A for Last Names, a Column B for first names, a Column P for
gt; gt; gt; one
gt; gt; gt; gt; of the data resources, and a Column Q for the other data resource. There
gt; gt; gt; are
gt; gt; gt; gt; about 16,000 names here and I would be very grateful for anyone that might
gt; gt; gt; be
gt; gt; gt; gt; able to suggest a formula for looking up duplicate names and, if both
gt; gt; gt; entries
gt; gt; gt; gt; have an X in Column P then it could delete one of the 2 entries, and if
gt; gt; gt; one
gt; gt; gt; gt; entry has an X in both Column P and Column Q then it could delete the
gt; gt; gt; entry
gt; gt; gt; gt; with the X in Column P and add an X to column P of the second entry. I am
gt; gt; gt; gt; even willing to buy a seperate program if need be. I would be so grateful
gt; gt; gt; of
gt; gt; gt; gt; anyone with a solution. Thank You.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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
(Insertgt;Module), and paste the code there.

Then in Excel, goto menu Toolsgt;Macrogt;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)

quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
news
gt; I'll buy You dinner and drinks all night long if it does work! lol One
gt; question though... Where should I insert that formula?
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Don't buy new software, try this VBA
gt; gt;
gt; gt; Sub Reformat()
gt; gt; Dim iLastRow As Long
gt; gt; Dim i As Long
gt; gt; Dim sFormula As String
gt; gt;
gt; gt; iLastRow = Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
gt; gt; sFormula = _
gt; gt; quot;=IF(SUMPRODUCT(--($A$1:$A$quot; amp; iLastRow amp; quot;=A2),--($B$1:$B$quot; amp; _
gt; gt; iLastRow amp; quot;=B2))gt;1,quot;quot;Dupquot;quot;,quot;quot;quot;quot;)quot;
gt; gt;
gt; gt; Columns(quot;R:Rquot;).Insert Shift:=xlToRight
gt; gt; With Range(quot;R1quot;)
gt; gt; .Formula = quot;=ROW()quot;
gt; gt; .AutoFill Destination:=.Resize(iLastRow)
gt; gt; .Resize(iLastRow).Value = .Resize(iLastRow).Value
gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;A2quot;), _
gt; gt; Order1:=xlAscending, _
gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; Order2:=xlAscending, _
gt; gt; Header:=xlYes, _
gt; gt; OrderCustom:=1, _
gt; gt; MatchCase:=False, _
gt; gt; Orientation:=xlTopToBottom, _
gt; gt; DataOption1:=xlSortNormal, _
gt; gt; DataOption2:=xlSortNormal
gt; gt; End With
gt; gt;
gt; gt; With Range(quot;S2quot;)
gt; gt; .Formula = sFormula
gt; gt; .AutoFill .Resize(iLastRow)
gt; gt; End With
gt; gt;
gt; gt; For i = iLastRow To 2 Step -1
gt; gt; If Cells(i, quot;Squot;).Value = quot;Dupquot; Then
gt; gt; If Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i, quot;qquot;).Value = quot;Xquot;
And _
gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1,
quot;Qquot;).Value =
gt; gt; quot;Xquot; Then
gt; gt; Rows(i).Delete
gt; gt; i = i - 1
gt; gt; ElseIf Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value
=
gt; gt; quot;Xquot; Then
gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot;
gt; gt; Rows(i).Delete
gt; gt; i = i - 1
gt; gt; Else
gt; gt; Rows(i - 1).Delete
gt; gt; i = i - 1
gt; gt; End If
gt; gt; End If
gt; gt; Next i
gt; gt;
gt; gt; With Range(quot;R1quot;)
gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;R2quot;), _
gt; gt; Order1:=xlAscending, _
gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; Order2:=xlAscending, _
gt; gt; Header:=xlYes, _
gt; gt; DataOption1:=xlSortNormal
gt; gt; .Resize(, 2).EntireColumn.Delete
gt; gt; End With
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; If it works, buy me a drink lt;vbggt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
gt; gt; ...
gt; gt; gt; Hello. I use Excell 2003 on my work comp. On this particular worksheet
gt; gt; there
gt; gt; gt; is a Column A for Last Names, a Column B for first names, a Column P
for
gt; gt; one
gt; gt; gt; of the data resources, and a Column Q for the other data resource.
There
gt; gt; are
gt; gt; gt; about 16,000 names here and I would be very grateful for anyone that
might
gt; gt; be
gt; gt; gt; able to suggest a formula for looking up duplicate names and, if both
gt; gt; entries
gt; gt; gt; have an X in Column P then it could delete one of the 2 entries, and
if
gt; gt; one
gt; gt; gt; entry has an X in both Column P and Column Q then it could delete the
gt; gt; entry
gt; gt; gt; with the X in Column P and add an X to column P of the second entry. I
am
gt; gt; gt; even willing to buy a seperate program if need be. I would be so
grateful
gt; gt; of
gt; gt; gt; anyone with a solution. Thank You.
gt; gt;
gt; gt;
gt; gt;
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(quot;A2quot;), _
Order1:=xlAscending, _
Key2:=Range(quot;B2quot;), _
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...

quot;Bob Phillipsquot; wrote:

gt; It is not a formula it is VBA, so you need to store it in a standard code
gt; module. To do this, go to the VBIDE (Alt-F11), insert a new code module
gt; (Insertgt;Module), and paste the code there.
gt;
gt; Then in Excel, goto menu Toolsgt;Macrogt;Macros... and select Reformat from the
gt; list, and click Run.
gt;
gt; You will need to test it carefully, so take a backup, as I wasn't absolutely
gt; sure about the P/X, Q/X rules, but they can be easily adjusted.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
gt; news
gt; gt; I'll buy You dinner and drinks all night long if it does work! lol One
gt; gt; question though... Where should I insert that formula?
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Don't buy new software, try this VBA
gt; gt; gt;
gt; gt; gt; Sub Reformat()
gt; gt; gt; Dim iLastRow As Long
gt; gt; gt; Dim i As Long
gt; gt; gt; Dim sFormula As String
gt; gt; gt;
gt; gt; gt; iLastRow = Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
gt; gt; gt; sFormula = _
gt; gt; gt; quot;=IF(SUMPRODUCT(--($A$1:$A$quot; amp; iLastRow amp; quot;=A2),--($B$1:$B$quot; amp; _
gt; gt; gt; iLastRow amp; quot;=B2))gt;1,quot;quot;Dupquot;quot;,quot;quot;quot;quot;)quot;
gt; gt; gt;
gt; gt; gt; Columns(quot;R:Rquot;).Insert Shift:=xlToRight
gt; gt; gt; With Range(quot;R1quot;)
gt; gt; gt; .Formula = quot;=ROW()quot;
gt; gt; gt; .AutoFill Destination:=.Resize(iLastRow)
gt; gt; gt; .Resize(iLastRow).Value = .Resize(iLastRow).Value
gt; gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;A2quot;), _
gt; gt; gt; Order1:=xlAscending, _
gt; gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; gt; Order2:=xlAscending, _
gt; gt; gt; Header:=xlYes, _
gt; gt; gt; OrderCustom:=1, _
gt; gt; gt; MatchCase:=False, _
gt; gt; gt; Orientation:=xlTopToBottom, _
gt; gt; gt; DataOption1:=xlSortNormal, _
gt; gt; gt; DataOption2:=xlSortNormal
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; With Range(quot;S2quot;)
gt; gt; gt; .Formula = sFormula
gt; gt; gt; .AutoFill .Resize(iLastRow)
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; For i = iLastRow To 2 Step -1
gt; gt; gt; If Cells(i, quot;Squot;).Value = quot;Dupquot; Then
gt; gt; gt; If Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i, quot;qquot;).Value = quot;Xquot;
gt; And _
gt; gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1,
gt; quot;Qquot;).Value =
gt; gt; gt; quot;Xquot; Then
gt; gt; gt; Rows(i).Delete
gt; gt; gt; i = i - 1
gt; gt; gt; ElseIf Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1, quot;Qquot;).Value
gt; =
gt; gt; gt; quot;Xquot; Then
gt; gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot;
gt; gt; gt; Rows(i).Delete
gt; gt; gt; i = i - 1
gt; gt; gt; Else
gt; gt; gt; Rows(i - 1).Delete
gt; gt; gt; i = i - 1
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt; Next i
gt; gt; gt;
gt; gt; gt; With Range(quot;R1quot;)
gt; gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;R2quot;), _
gt; gt; gt; Order1:=xlAscending, _
gt; gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; gt; Order2:=xlAscending, _
gt; gt; gt; Header:=xlYes, _
gt; gt; gt; DataOption1:=xlSortNormal
gt; gt; gt; .Resize(, 2).EntireColumn.Delete
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; If it works, buy me a drink lt;vbggt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hello. I use Excell 2003 on my work comp. On this particular worksheet
gt; gt; gt; there
gt; gt; gt; gt; is a Column A for Last Names, a Column B for first names, a Column P
gt; for
gt; gt; gt; one
gt; gt; gt; gt; of the data resources, and a Column Q for the other data resource.
gt; There
gt; gt; gt; are
gt; gt; gt; gt; about 16,000 names here and I would be very grateful for anyone that
gt; might
gt; gt; gt; be
gt; gt; gt; gt; able to suggest a formula for looking up duplicate names and, if both
gt; gt; gt; entries
gt; gt; gt; gt; have an X in Column P then it could delete one of the 2 entries, and
gt; if
gt; gt; gt; one
gt; gt; gt; gt; entry has an X in both Column P and Column Q then it could delete the
gt; gt; gt; entry
gt; gt; gt; gt; with the X in Column P and add an X to column P of the second entry. I
gt; am
gt; gt; gt; gt; even willing to buy a seperate program if need be. I would be so
gt; grateful
gt; gt; gt; of
gt; gt; gt; gt; anyone with a solution. Thank You.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

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)

quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
...
gt; Thank You so much again... *smiles* I did everything as You said but I'm
gt; getting this Runtime 1004 Error... The portion of the macro that it
gt; highlights is:
gt;
gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;A2quot;), _
gt; Order1:=xlAscending, _
gt; Key2:=Range(quot;B2quot;), _
gt; Order2:=xlAscending, _
gt; Header:=xlYes, _
gt; OrderCustom:=1, _
gt; MatchCase:=False, _
gt; Orientation:=xlTopToBottom, _
gt; DataOption1:=xlSortNormal, _
gt; DataOption2:=xlSortNormal
gt;
gt; What am I doing wrong? I've tried unmerging the cells and resizing them
but
gt; that's not seeming to work...
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; It is not a formula it is VBA, so you need to store it in a standard
code
gt; gt; module. To do this, go to the VBIDE (Alt-F11), insert a new code module
gt; gt; (Insertgt;Module), and paste the code there.
gt; gt;
gt; gt; Then in Excel, goto menu Toolsgt;Macrogt;Macros... and select Reformat from
the
gt; gt; list, and click Run.
gt; gt;
gt; gt; You will need to test it carefully, so take a backup, as I wasn't
absolutely
gt; gt; sure about the P/X, Q/X rules, but they can be easily adjusted.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
gt; gt; news
gt; gt; gt; I'll buy You dinner and drinks all night long if it does work! lol One
gt; gt; gt; question though... Where should I insert that formula?
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Don't buy new software, try this VBA
gt; gt; gt; gt;
gt; gt; gt; gt; Sub Reformat()
gt; gt; gt; gt; Dim iLastRow As Long
gt; gt; gt; gt; Dim i As Long
gt; gt; gt; gt; Dim sFormula As String
gt; gt; gt; gt;
gt; gt; gt; gt; iLastRow = Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
gt; gt; gt; gt; sFormula = _
gt; gt; gt; gt; quot;=IF(SUMPRODUCT(--($A$1:$A$quot; amp; iLastRow amp; quot;=A2),--($B$1:$B$quot;
amp; _
gt; gt; gt; gt; iLastRow amp; quot;=B2))gt;1,quot;quot;Dupquot;quot;,quot;quot;quot;quot;)quot;
gt; gt; gt; gt;
gt; gt; gt; gt; Columns(quot;R:Rquot;).Insert Shift:=xlToRight
gt; gt; gt; gt; With Range(quot;R1quot;)
gt; gt; gt; gt; .Formula = quot;=ROW()quot;
gt; gt; gt; gt; .AutoFill Destination:=.Resize(iLastRow)
gt; gt; gt; gt; .Resize(iLastRow).Value = .Resize(iLastRow).Value
gt; gt; gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;A2quot;), _
gt; gt; gt; gt; Order1:=xlAscending, _
gt; gt; gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; gt; gt; Order2:=xlAscending, _
gt; gt; gt; gt; Header:=xlYes, _
gt; gt; gt; gt; OrderCustom:=1, _
gt; gt; gt; gt; MatchCase:=False, _
gt; gt; gt; gt; Orientation:=xlTopToBottom,
_
gt; gt; gt; gt; DataOption1:=xlSortNormal,
_
gt; gt; gt; gt; DataOption2:=xlSortNormal
gt; gt; gt; gt; End With
gt; gt; gt; gt;
gt; gt; gt; gt; With Range(quot;S2quot;)
gt; gt; gt; gt; .Formula = sFormula
gt; gt; gt; gt; .AutoFill .Resize(iLastRow)
gt; gt; gt; gt; End With
gt; gt; gt; gt;
gt; gt; gt; gt; For i = iLastRow To 2 Step -1
gt; gt; gt; gt; If Cells(i, quot;Squot;).Value = quot;Dupquot; Then
gt; gt; gt; gt; If Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i, quot;qquot;).Value =
quot;Xquot;
gt; gt; And _
gt; gt; gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1,
gt; gt; quot;Qquot;).Value =
gt; gt; gt; gt; quot;Xquot; Then
gt; gt; gt; gt; Rows(i).Delete
gt; gt; gt; gt; i = i - 1
gt; gt; gt; gt; ElseIf Cells(i, quot;Pquot;).Value = quot;Xquot; And Cells(i - 1,
quot;Qquot;).Value
gt; gt; =
gt; gt; gt; gt; quot;Xquot; Then
gt; gt; gt; gt; Cells(i - 1, quot;Pquot;).Value = quot;Xquot;
gt; gt; gt; gt; Rows(i).Delete
gt; gt; gt; gt; i = i - 1
gt; gt; gt; gt; Else
gt; gt; gt; gt; Rows(i - 1).Delete
gt; gt; gt; gt; i = i - 1
gt; gt; gt; gt; End If
gt; gt; gt; gt; End If
gt; gt; gt; gt; Next i
gt; gt; gt; gt;
gt; gt; gt; gt; With Range(quot;R1quot;)
gt; gt; gt; gt; .Resize(iLastRow).EntireRow.Sort Key1:=Range(quot;R2quot;), _
gt; gt; gt; gt; Order1:=xlAscending, _
gt; gt; gt; gt; Key2:=Range(quot;B2quot;), _
gt; gt; gt; gt; Order2:=xlAscending, _
gt; gt; gt; gt; Header:=xlYes, _
gt; gt; gt; gt; DataOption1:=xlSortNormal
gt; gt; gt; gt; .Resize(, 2).EntireColumn.Delete
gt; gt; gt; gt; End With
gt; gt; gt; gt;
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; If it works, buy me a drink lt;vbggt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Nariannaquot; .(DO_NOT_SPAM).gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Hello. I use Excell 2003 on my work comp. On this particular
worksheet
gt; gt; gt; gt; there
gt; gt; gt; gt; gt; is a Column A for Last Names, a Column B for first names, a Column
P
gt; gt; for
gt; gt; gt; gt; one
gt; gt; gt; gt; gt; of the data resources, and a Column Q for the other data resource.
gt; gt; There
gt; gt; gt; gt; are
gt; gt; gt; gt; gt; about 16,000 names here and I would be very grateful for anyone
that
gt; gt; might
gt; gt; gt; gt; be
gt; gt; gt; gt; gt; able to suggest a formula for looking up duplicate names and, if
both
gt; gt; gt; gt; entries
gt; gt; gt; gt; gt; have an X in Column P then it could delete one of the 2 entries,
and
gt; gt; if
gt; gt; gt; gt; one
gt; gt; gt; gt; gt; entry has an X in both Column P and Column Q then it could delete
the
gt; gt; gt; gt; entry
gt; gt; gt; gt; gt; with the X in Column P and add an X to column P of the second
entry. I
gt; gt; am
gt; gt; gt; gt; gt; even willing to buy a seperate program if need be. I would be so
gt; gt; grateful
gt; gt; gt; gt; of
gt; gt; gt; gt; gt; anyone with a solution. Thank You.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;