Better way to find matches across multiple cells?

[Keith R]

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]

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.

 

[Dave Peterson]

Check your other post.

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]

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