Hi - I am new to this forum. I have a MS Excel macro question. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o></o>
I need to compare two columns of names (on different sheets, within the same workbook) and copy the entire row of data for the matches to a new sheet. The names in both of these columns are the same individuals. However, some of the names in these columns are in a slightly different format. Example:
></o>
Is there any way that I can either do a wildcard type search (i.e.: "like" or "contains")? <o></o>
<o></o>
Or how can I search on lastname, and then firstname only (so that initials are not factored into the search)? I know I can do this via a vLookup instead of a macro, but I have a lot of names and also would like to add this as part of other steps in a macro. <o></o>
<o>Currently, the code I have works well for exact matches. But, it will not return the names where the formatting does not match exactly in both columns.</o>
<o></o>
<o>------------------------ Current code ---------------------------</o>
<o>Sub Compare()</o>
<o>Dim rng As Range
Dim c As Range
Dim cfind As Range</o>
<o>On Error Resume Next</o>
<o>Set rng = Worksheet("Sheet1").Range("A2", Worksheet("Sheet1").Range("A65536").End(xlUp))
For Each c In rng
Set cfind = Worksheet("Sheet2").Range("A2", Worksheet("Sheet2").Range("A65536").End(xlUp)).Find _
(what:=c.Value, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If cfind Is Nothing Then
c.EntireRow.Copy Worksheets("Final").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Set cfind = Nothing
End If
Next c
Set rng = Nothing</o>
<o>Application.CutCopyMode = False</o>
<o>End Sub</o>
<o>--------------------------------------------------------------------</o>
Thank you for any assistance!<o></o>!
fficeffice" /><o></o><o
></o>I need to compare two columns of names (on different sheets, within the same workbook) and copy the entire row of data for the matches to a new sheet. The names in both of these columns are the same individuals. However, some of the names in these columns are in a slightly different format. Example:
Column1/Sheet1 = Smith, James
Column1/Sheet2 = Smith, James W.
<oColumn1/Sheet2 = Smith, James W.
></o>Is there any way that I can either do a wildcard type search (i.e.: "like" or "contains")? <o
></o><o
></o>Or how can I search on lastname, and then firstname only (so that initials are not factored into the search)? I know I can do this via a vLookup instead of a macro, but I have a lot of names and also would like to add this as part of other steps in a macro. <o
></o><o
>Currently, the code I have works well for exact matches. But, it will not return the names where the formatting does not match exactly in both columns.</o><o
></o> <o
>------------------------ Current code ---------------------------</o><o
>Sub Compare()</o><o
>Dim rng As RangeDim c As Range
Dim cfind As Range</o
><o
>On Error Resume Next</o><o
>Set rng = Worksheet("Sheet1").Range("A2", Worksheet("Sheet1").Range("A65536").End(xlUp))For Each c In rng
Set cfind = Worksheet("Sheet2").Range("A2", Worksheet("Sheet2").Range("A65536").End(xlUp)).Find _
(what:=c.Value, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If cfind Is Nothing Then
c.EntireRow.Copy Worksheets("Final").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Set cfind = Nothing
End If
Next c
Set rng = Nothing</o
><o
>Application.CutCopyMode = False</o><o
>End Sub</o><o
>--------------------------------------------------------------------</o>Thank you for any assistance!<o
></o>!
