Finding a match- should i use a nested array, my source arrays, or

[ker_01]

I have several arrays of products, each with a different number of items
fruit= Array("apples", "pears", "peaches")
pasta= Array("lasagna", "elbow", "bowtie", "penne", "orzo")
IceCream= Array("chocolate","vanilla")
[etc]

Which I've also thrown into one nested array (I'm not sure I needed to- it
seemed like a good idea at the time)
AllProducts = Array(fruit, pasta, IceCream)

The product values are all unique; the same product will not show up more
than once in an array, nor will it show up in more than one array.

I'm now cycling through a large body of raw data, trying to identify records
that match any of the values in my array. So for example, I might come across
a value of "vanilla". I need to identify if that value is in any of my
arrays, and if so, which array it is in, and which position it is in that
array. Once I know the position, I'll take other data from that same source
data row for my calculations (last order date, order quantity, etc.) to
aggregate by product and product group.

My limited experience suggests that I use the orginal arrays directly, maybe
with application.match to identify if it is in any component array. Is there
any benefit (speed, simplicity, or eloquence) to using a nested array to find
matched values, or am I better off sticking with the individual arrays? Or is
there some better way of doing this in Excel that I haven't learned yet?

Thank you!
Keith

 

[Bob Phillips]

You wont get a match in an array of arrays like that, so you would have to
check each item

fruit = Array("apples", "pears", "peaches")
pasta = Array("lasagna", "elbow", "bowtie", "penne", "orzo")
IceCream = Array("chocolate", "vanilla")
AllProducts = Array(fruit, pasta, IceCream)

lookup = "vanilla"
On Error Resume Next
For i = LBound(AllProducts) To UBound(AllProducts)

idx = Application.Match(lookup, AllProducts(i), 0)
If idx > 0 Then

MsgBox "Found in item (" & i & ")(" & idx & ")"
Exit For
End If
Next i

 

[ker_01]

Thanks Bob- this works great!
Best,
Keith

You wont get a match in an array of arrays like that, so you would have to
check each item

fruit = Array("apples", "pears", "peaches")
pasta = Array("lasagna", "elbow", "bowtie", "penne", "orzo")
IceCream = Array("chocolate", "vanilla")
AllProducts = Array(fruit, pasta, IceCream)

lookup = "vanilla"
On Error Resume Next
For i = LBound(AllProducts) To UBound(AllProducts)

idx = Application.Match(lookup, AllProducts(i), 0)
If idx > 0 Then

MsgBox "Found in item (" & i & ")(" & idx & ")"
Exit For
End If
Next i

--
__________________________________
HTH

Bob

I have several arrays of products, each with a different number of items
fruit= Array("apples", "pears", "peaches")
pasta= Array("lasagna", "elbow", "bowtie", "penne", "orzo")
IceCream= Array("chocolate","vanilla")
[etc]

Which I've also thrown into one nested array (I'm not sure I needed to- it
seemed like a good idea at the time)
AllProducts = Array(fruit, pasta, IceCream)

The product values are all unique; the same product will not show up more
than once in an array, nor will it show up in more than one array.

I'm now cycling through a large body of raw data, trying to identify
records
that match any of the values in my array. So for example, I might come
across
a value of "vanilla". I need to identify if that value is in any of my
arrays, and if so, which array it is in, and which position it is in that
array. Once I know the position, I'll take other data from that same
source
data row for my calculations (last order date, order quantity, etc.) to
aggregate by product and product group.

My limited experience suggests that I use the orginal arrays directly,
maybe
with application.match to identify if it is in any component array. Is
there
any benefit (speed, simplicity, or eloquence) to using a nested array to
find
matched values, or am I better off sticking with the individual arrays? Or
is
there some better way of doing this in Excel that I haven't learned yet?

Thank you!
Keith