Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a match- should i use a nested array, my source arrays, or
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a match- should i use a nested array, my source arrays, or
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 "ker_01" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a match- should i use a nested array, my source arrays
Thanks Bob- this works great!
Best, Keith "Bob Phillips" wrote: 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 "ker_01" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
nested MATCH within Array formula? | Excel Worksheet Functions | |||
Arrays - Nested IF with Vlookup? | Excel Discussion (Misc queries) | |||
finding nearest match from an array | Excel Discussion (Misc queries) | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Nested Arrays | Excel Programming |