Home 
Search 
Today's Posts 
#1




Macro to Compare Two Columns of Numbers on Two Sheets and Flag Mat
I have a worksheet ("All SEG ISBNs") which is a long list of numbers in
Column E. I have another sheet "WH ISBNs") in the same workbook with a long list of numbers in Column A. Is it possible to write a macro that will take each one of the numbers on the "WH ISBNs" sheet, look for it on the "All SEG ISBNs" sheet, and, if it finds a match on the "All SEG ISBNs" sheet, add the text "WH Match Found" next in Column F on the "All SEG ISBNs" sheetthat is, next to each of the matching numbers? There may be duplicate numbers in each column on each sheet, and it's OK if the macro inserts "WH Match Found" next to each occurrence. (By the way, both sheets have header rows.) I've used the COUNTIF function to identifying matching numbers, but it seems to take a very long time to calculate. If was hoping that a macrobased version would be faster, and besdies, I'm hoping to do this same thing for other sheets in the same workbook, and the calculation time for a formulabased solution is just going to get worse and worse. I've poked about and found various macros that seem to come close to what I need, but frankly, I'm not knowledgeable enough to manipulate them well, and my various attempt to loop and whatnot keep ending in disaster. Any help would be much, much appreciated! 
#2




Macro to Compare Two Columns of Numbers on Two Sheets and Flag Mat
Ezra,
A formula based solution should be extremely fast  in fact, if I were to write the macro, I would use the code to write a column of formulas, calculate the formulas, and then copy and convert them to values. That is typically much faster than looping through with code. You may simply want to limit the number of formulas that you use, or the range that you compare, or you could copy the column of formulas, then paste special values, and that will eliminate the recalc issue. Or post your formula  there may be ways to make it better.... HTH, Bernie MS Excel MVP "Ezra" wrote in message ... I have a worksheet ("All SEG ISBNs") which is a long list of numbers in Column E. I have another sheet "WH ISBNs") in the same workbook with a long list of numbers in Column A. Is it possible to write a macro that will take each one of the numbers on the "WH ISBNs" sheet, look for it on the "All SEG ISBNs" sheet, and, if it finds a match on the "All SEG ISBNs" sheet, add the text "WH Match Found" next in Column F on the "All SEG ISBNs" sheetthat is, next to each of the matching numbers? There may be duplicate numbers in each column on each sheet, and it's OK if the macro inserts "WH Match Found" next to each occurrence. (By the way, both sheets have header rows.) I've used the COUNTIF function to identifying matching numbers, but it seems to take a very long time to calculate. If was hoping that a macrobased version would be faster, and besdies, I'm hoping to do this same thing for other sheets in the same workbook, and the calculation time for a formulabased solution is just going to get worse and worse. I've poked about and found various macros that seem to come close to what I need, but frankly, I'm not knowledgeable enough to manipulate them well, and my various attempt to loop and whatnot keep ending in disaster. Any help would be much, much appreciated! 
#3




Macro to Compare Two Columns of Numbers on Two Sheets and Flag Mat
Try this:
Sub matchemup() Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range Dim lr As Long Set ws1 = Worksheets("All SEG ISBNs") Set ws2 = Worksheets("WH ISBNs") lr = ws1.Cells(Rows.Count, 5).End(xlUp).Row lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ws1.Range("E2:E" & lr) For Each c In rng For i = 2 To lr2 If c.Value = ws2.Cells(i, 1).Value Then c.Offset(, 1) = "WH Match Found" End If Next Next End Sub "Ezra" wrote in message ... I have a worksheet ("All SEG ISBNs") which is a long list of numbers in Column E. I have another sheet "WH ISBNs") in the same workbook with a long list of numbers in Column A. Is it possible to write a macro that will take each one of the numbers on the "WH ISBNs" sheet, look for it on the "All SEG ISBNs" sheet, and, if it finds a match on the "All SEG ISBNs" sheet, add the text "WH Match Found" next in Column F on the "All SEG ISBNs" sheetthat is, next to each of the matching numbers? There may be duplicate numbers in each column on each sheet, and it's OK if the macro inserts "WH Match Found" next to each occurrence. (By the way, both sheets have header rows.) I've used the COUNTIF function to identifying matching numbers, but it seems to take a very long time to calculate. If was hoping that a macrobased version would be faster, and besdies, I'm hoping to do this same thing for other sheets in the same workbook, and the calculation time for a formulabased solution is just going to get worse and worse. I've poked about and found various macros that seem to come close to what I need, but frankly, I'm not knowledgeable enough to manipulate them well, and my various attempt to loop and whatnot keep ending in disaster. Any help would be much, much appreciated! 
#4




Macro to Compare Two Columns of Numbers on Two Sheets and Flag
Hi, Bernie:
I've pared down my formula as much as possiblein fact, out of desperation, it's almost nothing now. In Column F on the "All SEG ISBNs" sheet, I have this in each cell: =COUNTIF('WH ISBNs'!$A2:$A6000,=E2) Originally, I had this embedded in an IF function that would replace the number returned into with the text string "WH Match Found" if the number returned was greater than zero. Even going with this pareddown version, though, which just gives me the raw count of matching numbers, it was taking about ten minutes to calculate. I can set the calculation mode to manual and just calulcate when I'm ready to do so, but I always hate doing that unless I absolutely have to. The "All SEG ISBNs" sheet is about 40,000 rows deep, and the "WH ISBNs" sheet is about 5000 rows, and I know that the various IF functions always take slightly longer to calculate, but this is just nutty. The results it generates are fine, but this thing is updated in varying ways often, and the calc time issue is driving users crazy. (Users are on both PCs and Macs in our office, but the calc time is long on both platforms, and I don't have reason to believe that workstation capacity is impeding crunch time in a significant way.) If there's a much faster way to do this, I'd really appreciate any suggestions to can offer. Thanks! "Bernie Deitrick" wrote: Ezra, A formula based solution should be extremely fast  in fact, if I were to write the macro, I would use the code to write a column of formulas, calculate the formulas, and then copy and convert them to values. That is typically much faster than looping through with code. You may simply want to limit the number of formulas that you use, or the range that you compare, or you could copy the column of formulas, then paste special values, and that will eliminate the recalc issue. Or post your formula  there may be ways to make it better.... HTH, Bernie MS Excel MVP "Ezra" wrote in message ... I have a worksheet ("All SEG ISBNs") which is a long list of numbers in Column E. I have another sheet "WH ISBNs") in the same workbook with a long list of numbers in Column A. Is it possible to write a macro that will take each one of the numbers on the "WH ISBNs" sheet, look for it on the "All SEG ISBNs" sheet, and, if it finds a match on the "All SEG ISBNs" sheet, add the text "WH Match Found" next in Column F on the "All SEG ISBNs" sheetthat is, next to each of the matching numbers? There may be duplicate numbers in each column on each sheet, and it's OK if the macro inserts "WH Match Found" next to each occurrence. (By the way, both sheets have header rows.) I've used the COUNTIF function to identifying matching numbers, but it seems to take a very long time to calculate. If was hoping that a macrobased version would be faster, and besdies, I'm hoping to do this same thing for other sheets in the same workbook, and the calculation time for a formulabased solution is just going to get worse and worse. I've poked about and found various macros that seem to come close to what I need, but frankly, I'm not knowledgeable enough to manipulate them well, and my various attempt to loop and whatnot keep ending in disaster. Any help would be much, much appreciated! 
#5




Macro to Compare Two Columns of Numbers on Two Sheets and Flag
I appreciate the code, JLGWhiz! Unfortunately, it seems to keep getting stuck
in a loop somewhere. That is, it starts up and doesn't error out, but it never stops running. I've made sure there aren't any blank cells in either of the columns being compared, but that doesn't seem to make a differenceit just keeps on going, with the result that I have to (on a Mac) do a Force Quit to pull out. If you have any more ideas, I'd really appreciate itcertainly, I appreciate what both you and Bernie (above) have contributed so far! Ezra "JLGWhiz" wrote: Try this: Sub matchemup() Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range Dim lr As Long Set ws1 = Worksheets("All SEG ISBNs") Set ws2 = Worksheets("WH ISBNs") lr = ws1.Cells(Rows.Count, 5).End(xlUp).Row lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ws1.Range("E2:E" & lr) For Each c In rng For i = 2 To lr2 If c.Value = ws2.Cells(i, 1).Value Then c.Offset(, 1) = "WH Match Found" End If Next Next End Sub "Ezra" wrote in message ... I have a worksheet ("All SEG ISBNs") which is a long list of numbers in Column E. I have another sheet "WH ISBNs") in the same workbook with a long list of numbers in Column A. Is it possible to write a macro that will take each one of the numbers on the "WH ISBNs" sheet, look for it on the "All SEG ISBNs" sheet, and, if it finds a match on the "All SEG ISBNs" sheet, add the text "WH Match Found" next in Column F on the "All SEG ISBNs" sheetthat is, next to each of the matching numbers? There may be duplicate numbers in each column on each sheet, and it's OK if the macro inserts "WH Match Found" next to each occurrence. (By the way, both sheets have header rows.) I've used the COUNTIF function to identifying matching numbers, but it seems to take a very long time to calculate. If was hoping that a macrobased version would be faster, and besdies, I'm hoping to do this same thing for other sheets in the same workbook, and the calculation time for a formulabased solution is just going to get worse and worse. I've poked about and found various macros that seem to come close to what I need, but frankly, I'm not knowledgeable enough to manipulate them well, and my various attempt to loop and whatnot keep ending in disaster. Any help would be much, much appreciated! 
#6




Macro to Compare Two Columns of Numbers on Two Sheets and Flag Mat
I would use the worksheet formula =match() instead (I bet it's quicker than
=countif()). I could use a formula like: =isnumber(match(e2,'wh isbns'!a:a,0)) It would return TRUE if there's a match and False if there is no match. In fact, I'd do it manually instead of using a macro. But if I needed a macro, I wouldn't do the =match() in code, I'd populate the range with formulas that did the work. This puts the formula in, converts to values, removes the false's and changes the true's to your text. (I would have lived with the true/false. Option Explicit Sub Testme() Dim myRng As Range Dim myLookupRng As Range With Worksheets("all seg isbns") Set myRng = .Range("E2", .Cells(.Rows.Count, "E").End(xlUp)) End With With Worksheets("wh isbns") Set myLookupRng = .Range("a:a") End With With myRng.Offset(0, 1) .FormulaR1C1 _ = "=isnumber(match(rc[1]," _ & myLookupRng.Address(external:=True, _ ReferenceStyle:=xlR1C1) _ & ",0))" 'application.calculate 'are you still in manual calc mode??? .Value = .Value .Replace what:=False, _ replacement:="", _ lookat:=xlWhole, _ searchorder:=xlByRows, _ MatchCase:=False .Replace what:=True, _ replacement:="WH Match Found", _ lookat:=xlWhole, _ searchorder:=xlByRows, _ MatchCase:=False End With End Sub Ezra wrote: I have a worksheet ("All SEG ISBNs") which is a long list of numbers in Column E. I have another sheet "WH ISBNs") in the same workbook with a long list of numbers in Column A. Is it possible to write a macro that will take each one of the numbers on the "WH ISBNs" sheet, look for it on the "All SEG ISBNs" sheet, and, if it finds a match on the "All SEG ISBNs" sheet, add the text "WH Match Found" next in Column F on the "All SEG ISBNs" sheetthat is, next to each of the matching numbers? There may be duplicate numbers in each column on each sheet, and it's OK if the macro inserts "WH Match Found" next to each occurrence. (By the way, both sheets have header rows.) I've used the COUNTIF function to identifying matching numbers, but it seems to take a very long time to calculate. If was hoping that a macrobased version would be faster, and besdies, I'm hoping to do this same thing for other sheets in the same workbook, and the calculation time for a formulabased solution is just going to get worse and worse. I've poked about and found various macros that seem to come close to what I need, but frankly, I'm not knowledgeable enough to manipulate them well, and my various attempt to loop and whatnot keep ending in disaster. Any help would be much, much appreciated!  Dave Peterson 
#7




Macro to Compare Two Columns of Numbers on Two Sheets and Flag
Although it worked in a test set up, it looks as though you have enough
suggestions to play with for a while, so I will move on. "Ezra" wrote in message ... I appreciate the code, JLGWhiz! Unfortunately, it seems to keep getting stuck in a loop somewhere. That is, it starts up and doesn't error out, but it never stops running. I've made sure there aren't any blank cells in either of the columns being compared, but that doesn't seem to make a differenceit just keeps on going, with the result that I have to (on a Mac) do a Force Quit to pull out. If you have any more ideas, I'd really appreciate itcertainly, I appreciate what both you and Bernie (above) have contributed so far! Ezra "JLGWhiz" wrote: Try this: Sub matchemup() Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range Dim lr As Long Set ws1 = Worksheets("All SEG ISBNs") Set ws2 = Worksheets("WH ISBNs") lr = ws1.Cells(Rows.Count, 5).End(xlUp).Row lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ws1.Range("E2:E" & lr) For Each c In rng For i = 2 To lr2 If c.Value = ws2.Cells(i, 1).Value Then c.Offset(, 1) = "WH Match Found" End If Next Next End Sub "Ezra" wrote in message ... I have a worksheet ("All SEG ISBNs") which is a long list of numbers in Column E. I have another sheet "WH ISBNs") in the same workbook with a long list of numbers in Column A. Is it possible to write a macro that will take each one of the numbers on the "WH ISBNs" sheet, look for it on the "All SEG ISBNs" sheet, and, if it finds a match on the "All SEG ISBNs" sheet, add the text "WH Match Found" next in Column F on the "All SEG ISBNs" sheetthat is, next to each of the matching numbers? There may be duplicate numbers in each column on each sheet, and it's OK if the macro inserts "WH Match Found" next to each occurrence. (By the way, both sheets have header rows.) I've used the COUNTIF function to identifying matching numbers, but it seems to take a very long time to calculate. If was hoping that a macrobased version would be faster, and besdies, I'm hoping to do this same thing for other sheets in the same workbook, and the calculation time for a formulabased solution is just going to get worse and worse. I've poked about and found various macros that seem to come close to what I need, but frankly, I'm not knowledgeable enough to manipulate them well, and my various attempt to loop and whatnot keep ending in disaster. Any help would be much, much appreciated! 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
compare 2 columns of numbers and place the matched numbers in a 3r  Excel Discussion (Misc queries)  
If, Or function or macro to compare 3 columns of numbers  Excel Worksheet Functions  
Is there a way to compare all columns in a Row and flag difference  Excel Programming  
Compare 2 Columns in 2 Sheets that are Not Ordered the Same  Excel Programming  
compare and merge 2 sheets if columns same  Excel Programming 