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" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"extrafrate" wrote:

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