Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way to find matches across multiple cells?
I came up with the following worksheet formula, beacuse as I cycle through
each row on one sheet, I need to find the corresponding record on the other sheet to pull some additional data. There are two identifiers that must both be used to find the unique matching row. {=MATCH(D8&J8,('Raw'!A1:A65000)&('Raw'!C1:C65000), FALSE)} The problem, of course, is that due to the size of the array from the second sheet, each match takes way too long (in excess of 5 seconds per row, with an average of 8000-10000 rows that I need to process). The second sheet will often be full or close to full, which is why I'm going to 65K rows. Is a find loop (like below) where I reset the range after every false match the fastest option, or is there a better way to do this in VBA? This seems overly complicated, especially since I'm not sure how many false matches (non-matching values) there would be (it can vary) so I'm not sure how to determine how many loops I should use. (or maybe with the exit for, it doesn't matter?) I'm currently looking at something like the aircode below, but it just doesn't seem elegant. For example, if a match of the initial number isn't found at all (which is possible) then it would crash as soon as the match function returned an error (on the line that tries to increment the RawRangeStart to NA()+1). If there is a better way than my frantic looping, please let me know. Thanks, Keith XP/XL2003 For i = 1 to 8000 RawRangeStart = 1 for multiplevalues = 1 to 10 'or 20? I don't know how many false matches I need to prepare for set rangeA = Sheet(Raw).range("A" & Cstr(RawRangeStart) & ":A65000") 'resize the range x = Application.match(valuefromD, rangeA,False) 'find the match in the remainder of the range if valuefromJ=valuefromC then 'do my calculations exit for 'I think this drops just to the most recent for, the multiplevalues and not the i loop? else RawRangeStart = x + 1 end if Next multiplevalues Next i |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way to find matches across multiple cells?
Have you thought of using a couple of helper columns?
One on the Raw worksheet that concatenates column A and column C. And one on the other sheet concatenating D and J. Ps. I'd use some sort of separator to make sure fields like: XXXX YYYY XX XXYYYY don't get treated the same. =d8&"..."&j8 (for instance) Then you can use those helper columns and not have to use array formulas. And if you're doing this in code, you could insert the helper columns, use them, and then delete them. Keith R wrote: I came up with the following worksheet formula, beacuse as I cycle through each row on one sheet, I need to find the corresponding record on the other sheet to pull some additional data. There are two identifiers that must both be used to find the unique matching row. {=MATCH(D8&J8,('Raw'!A1:A65000)&('Raw'!C1:C65000), FALSE)} The problem, of course, is that due to the size of the array from the second sheet, each match takes way too long (in excess of 5 seconds per row, with an average of 8000-10000 rows that I need to process). The second sheet will often be full or close to full, which is why I'm going to 65K rows. Is a find loop (like below) where I reset the range after every false match the fastest option, or is there a better way to do this in VBA? This seems overly complicated, especially since I'm not sure how many false matches (non-matching values) there would be (it can vary) so I'm not sure how to determine how many loops I should use. (or maybe with the exit for, it doesn't matter?) I'm currently looking at something like the aircode below, but it just doesn't seem elegant. For example, if a match of the initial number isn't found at all (which is possible) then it would crash as soon as the match function returned an error (on the line that tries to increment the RawRangeStart to NA()+1). If there is a better way than my frantic looping, please let me know. Thanks, Keith XP/XL2003 For i = 1 to 8000 RawRangeStart = 1 for multiplevalues = 1 to 10 'or 20? I don't know how many false matches I need to prepare for set rangeA = Sheet(Raw).range("A" & Cstr(RawRangeStart) & ":A65000") 'resize the range x = Application.match(valuefromD, rangeA,False) 'find the match in the remainder of the range if valuefromJ=valuefromC then 'do my calculations exit for 'I think this drops just to the most recent for, the multiplevalues and not the i loop? else RawRangeStart = x + 1 end if Next multiplevalues Next i -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way to find matches across multiple cells?
Check your other post.
Dave Peterson wrote: Have you thought of using a couple of helper columns? One on the Raw worksheet that concatenates column A and column C. And one on the other sheet concatenating D and J. Ps. I'd use some sort of separator to make sure fields like: XXXX YYYY XX XXYYYY don't get treated the same. =d8&"..."&j8 (for instance) Then you can use those helper columns and not have to use array formulas. And if you're doing this in code, you could insert the helper columns, use them, and then delete them. Keith R wrote: I came up with the following worksheet formula, beacuse as I cycle through each row on one sheet, I need to find the corresponding record on the other sheet to pull some additional data. There are two identifiers that must both be used to find the unique matching row. {=MATCH(D8&J8,('Raw'!A1:A65000)&('Raw'!C1:C65000), FALSE)} The problem, of course, is that due to the size of the array from the second sheet, each match takes way too long (in excess of 5 seconds per row, with an average of 8000-10000 rows that I need to process). The second sheet will often be full or close to full, which is why I'm going to 65K rows. Is a find loop (like below) where I reset the range after every false match the fastest option, or is there a better way to do this in VBA? This seems overly complicated, especially since I'm not sure how many false matches (non-matching values) there would be (it can vary) so I'm not sure how to determine how many loops I should use. (or maybe with the exit for, it doesn't matter?) I'm currently looking at something like the aircode below, but it just doesn't seem elegant. For example, if a match of the initial number isn't found at all (which is possible) then it would crash as soon as the match function returned an error (on the line that tries to increment the RawRangeStart to NA()+1). If there is a better way than my frantic looping, please let me know. Thanks, Keith XP/XL2003 For i = 1 to 8000 RawRangeStart = 1 for multiplevalues = 1 to 10 'or 20? I don't know how many false matches I need to prepare for set rangeA = Sheet(Raw).range("A" & Cstr(RawRangeStart) & ":A65000") 'resize the range x = Application.match(valuefromD, rangeA,False) 'find the match in the remainder of the range if valuefromJ=valuefromC then 'do my calculations exit for 'I think this drops just to the most recent for, the multiplevalues and not the i loop? else RawRangeStart = x + 1 end if Next multiplevalues Next i -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way to find matches across multiple cells?
Dave-
Thank you for your reply. I had been trying to avoid using extra worksheet fields (I never know what my end users might do) but your post made me realize that I could/should just create the concatenated fields in two new arrays, then my code was just the simple application.match, and it is incredibly fast, and requires no loops other than my main loop of the first array. I used a period as the separator, since that is a character that will never be used in the raw data. Thanks again, Keith "Dave Peterson" wrote in message ... Have you thought of using a couple of helper columns? One on the Raw worksheet that concatenates column A and column C. And one on the other sheet concatenating D and J. Ps. I'd use some sort of separator to make sure fields like: XXXX YYYY XX XXYYYY don't get treated the same. =d8&"..."&j8 (for instance) Then you can use those helper columns and not have to use array formulas. And if you're doing this in code, you could insert the helper columns, use them, and then delete them. Keith R wrote: I came up with the following worksheet formula, beacuse as I cycle through each row on one sheet, I need to find the corresponding record on the other sheet to pull some additional data. There are two identifiers that must both be used to find the unique matching row. {=MATCH(D8&J8,('Raw'!A1:A65000)&('Raw'!C1:C65000), FALSE)} The problem, of course, is that due to the size of the array from the second sheet, each match takes way too long (in excess of 5 seconds per row, with an average of 8000-10000 rows that I need to process). The second sheet will often be full or close to full, which is why I'm going to 65K rows. Is a find loop (like below) where I reset the range after every false match the fastest option, or is there a better way to do this in VBA? This seems overly complicated, especially since I'm not sure how many false matches (non-matching values) there would be (it can vary) so I'm not sure how to determine how many loops I should use. (or maybe with the exit for, it doesn't matter?) I'm currently looking at something like the aircode below, but it just doesn't seem elegant. For example, if a match of the initial number isn't found at all (which is possible) then it would crash as soon as the match function returned an error (on the line that tries to increment the RawRangeStart to NA()+1). If there is a better way than my frantic looping, please let me know. Thanks, Keith XP/XL2003 For i = 1 to 8000 RawRangeStart = 1 for multiplevalues = 1 to 10 'or 20? I don't know how many false matches I need to prepare for set rangeA = Sheet(Raw).range("A" & Cstr(RawRangeStart) & ":A65000") 'resize the range x = Application.match(valuefromD, rangeA,False) 'find the match in the remainder of the range if valuefromJ=valuefromC then 'do my calculations exit for 'I think this drops just to the most recent for, the multiplevalues and not the i loop? else RawRangeStart = x + 1 end if Next multiplevalues Next i -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i find multiple matches of one data item in an excel range | Excel Discussion (Misc queries) | |||
Add up Values if Find Matches x 2 | Excel Worksheet Functions | |||
How do I count cells that matches multiple criteria in Excel? | Excel Worksheet Functions | |||
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions | |||
MATCH function - how to find multiple matches ?? | Excel Programming |