Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Text between Columns
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Text between Columns
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 "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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Text between Columns
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Text between Columns
(minor edit)
Need add a closing quotation mark in beginning definition Set CompareRange = Worksheets("Sheet2").Range("C1:C5" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Joel" wrote: 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 "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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Text between Columns
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Text between Columns
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "extrafrate" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Text between Columns
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 on VBA) after the experience I've enjoyed on this, my first day, of posting. Thanks again! "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "extrafrate" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two columns of text values | Excel Discussion (Misc queries) | |||
comparing two columns of text | Excel Discussion (Misc queries) | |||
comparing text values in two columns | Excel Discussion (Misc queries) | |||
Comparing Two Columns of Text | Excel Discussion (Misc queries) | |||
Comparing text in columns | Excel Discussion (Misc queries) |