Application.Match on 2 Day array (multiple criteria)

[ericlch16]

Hi Experts,

i have a 2D array that is simplified as

array= (10,"","")
array= (10,1,"")
array=(10,1,"")
array=(10,2,"")
array=(10,1,23)
array=(10,1,24)
array=(10,1,25)
array=(10,1,26)
array=(10,2,40)
array=(10,2,42)

as you can see above, the array is already sorted in ascending order by column 1, column 2 then column 3. I am trying to user application match on the 3 columns to find the first occurrence and last occurrence of a concatenated value of column 1,2.

for example
10-"" : first occurrence will be 1 and last occurrence will be 1
10-1 : first occurrence will be 2 and last occurrence will be 3
10-2 : first occurrence will be 4 and last occurrence will be 4
10-1 : first occurrence will be 5 and last occurrence will be 8
10-2 : first occurrence will be 9 and last occurrence will be 10

I can get the concatenated string (e.g 10-"", 10-1,10-2) and first occurrence as I am already looping into it. But how can i get the last occurrence index using application.match or by using any other function? I would like to avoid another loop

can you do application.match on 2 columns in a 2D array? any other alternate function i can use?

thanks

 

[mart37]

the array is already sorted in ascending order by column 1, column 2 then column 3.
Sorry but in the example it is: 1 - 3 - 2

 

[ericlch16]

yes. typo error for sorting column. thanks.

the array is already sorted in ascending order by column 1, column 3 then column 2.

any solution for application.match?

 

[StephenCrump]

How are you populating and sorting your array?

Is it coming from Excel?

 

[ericlch16]

Yes. I filter excel first, copy filtered data to myworkbook, then copy from myworkbook to array.

from array, then i do the for loop.

 

[StephenCrump]

Yes. I filter excel first, copy filtered data to myworkbook, then copy from myworkbook to array.

from array, then i do the for loop.

This sounds convoluted?

Why not just use a formula in Excel: =MAX(IF("10-2"=INDEX(MyArray,,1)&"-"&INDEX(MyArray,,2),ROW(MyArray))) (which I think you'll need to array-enter).

Or from VBA: =EVALUATE("MAX(IF(""10-2""=INDEX(MyArray,,1)&""-""&INDEX(MyArray,,2),ROW(MyArray)))")

 

[ericlch16]

This sounds convoluted?

Why not just use a formula in Excel: =MAX(IF("10-2"=INDEX(MyArray,,1)&"-"&INDEX(MyArray,,2),ROW(MyArray))) (which I think you'll need to array-enter).

Or from VBA: =EVALUATE("MAX(IF(""10-2""=INDEX(MyArray,,1)&""-""&INDEX(MyArray,,2),ROW(MyArray)))")

i tried the VBA one and i am getting error 2029. I cannot figure it out what is the syntax error. any help?

TEST = Evaluate("MAX(IF(""" & mysectionnumber & " - " & myunitnumber & """=INDEX(TempArray,," & csv_sectioncolumn & ")&""-""&INDEX(TempArray,," & csv_unitcolumn & "),ROW(TempArray)))")

 

[StephenCrump]

Assuming mysectionnumber etc are VBA variables, and not Excel range names, try:

TEST = Evaluate("MAX(IF(""" & mysectionnumber & "-" & myunitnumber & """=INDEX(TempArray,," & csv_sectioncolumn & ") & ""-"" & INDEX(TempArray,," & csv_unitcolumn & "),ROW(TempArray)))")

You were very close. You just needed to give space to your ampersands. Because of the legacy VBA variables types (where a variable SomeLong& is a Long because of the & suffix) VBA will give you a syntax error on

C=A&B or C=A& B, but not with, say C=A+B or C=A+ B.