Comparing Text between Columns

[extrafrate]

Hi, I would like to modify this macro such that it will allow my to search
one column of text against another and reveal where instances of column A
text occurs within column C. Essentially, I would like to have a VBA-version
of this formula "=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)" (for reference to
what I'm trying to do, please see http://support.microsoft.com/kb/213367). I
tried to simply modify the search range but the macro ran with no results
(which I know is incorrect). Thank you for any help you can give. Note: the
two columns of data I wish to compare are; Column A --> ~10,000 server names,
Column C --> ~35,000 system names, and I want to find out which of those
column A servers are found in the column C list. Make sense?

Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
' Set CompareRange equal to the range to which you will
' compare the selection.
Set CompareRange = Range("C1:C5")
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.
' Set CompareRange = Workbooks("Book2"). _
' Worksheets("Sheet2").Range("C1:C5")
'
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 1) = x
Next y
Next x
End Sub

 

[Joel]

Set CompareRange = Worksheets("Sheet2").Range("C1:C5)
For Each x In Selection
set c = ComPareRange.find(what:=x,lookin:=xlvalues,lookat:=xlwhole)
if not c is nothing then
x.offset(0,1) = x
end if
Next x

 

[Luke M]

If you want to use that macro, note that you'll need to change the
CompareRange to a range equal to your data (C1:C35000). I also added an
escape clause so that after finding a matching value, the macro will not
continue to search column C (should increase calc time slightly). Due note
that this macro will still prb take awhile to run.

Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
' Set CompareRange equal to the range to which you will
' compare the selection.
Set CompareRange = Range("C1:C35000")
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.
' Set CompareRange = Workbooks("Book2"). _
' Worksheets("Sheet2").Range("C1:C5")
'
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.

'Set Range equal to area you want checked
For Each x In Range("A1:A10000")
For Each y In CompareRange
If x = y Then
x.Offset(0, 1) = x
Exit For
End If
Next y
Next x
End Sub

 

[Luke M]

(minor edit)
Need add a closing quotation mark in beginning definition
Set CompareRange = Worksheets("Sheet2").Range("C1:C5"

 

[extrafrate]

Hi Luke, your script worked just fine and took about 4 minutes to run. One
thing please, if I wanted to clean up the results, how could I have the
results listed in a separate column and without spaces between the rows? I
found 1192 systems from column A listed in column C and I'd like to just have
a single (easy to cut and paste) list of those systems. Is that easily
accomplished?

 

[Luke M]

Modified sub. Note that you can change column reference if desired. Also of
note, you could apply an autofilter and filter for non blank cells, and then
select and copy (if you don't want to have to wait 4 minutes again)

Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
' Set CompareRange equal to the range to which you will
' compare the selection.
Set CompareRange = Range("C1:C35000")
xCount = 1
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.
' Set CompareRange = Workbooks("Book2"). _
' Worksheets("Sheet2").Range("C1:C5")
'
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.

'Set Range equal to area you want checked
For Each x In Range("A1:A10000")
For Each y In CompareRange
If x = y Then

'Change letter to column of choice
Range("B" & xCount).Value = x
xCount = xCount + 1
Exit For
End If
Next y
Next x
End Sub

 

[extrafrate]

Luke, thank you very much! Works like a champ. Regarding your note however, I
could not apply the filters you described since I lack the requisite
knowledge. I have a more complex task in which I'm comparing data from two
databases (each a column of text from separate workbooks) with the intention
of identifying those data which are unique to each (and where they are
duplicated). I may just post my problem here (in combination with reading up