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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
|
Similar Threads
