Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Single Dimension to Multi Dimension Array
I haven't seen this anywhere. I'd like to combine a group of single arrays to
a multi-dimension. The item array(s) have 5 characteristics (e.g. A,B,C,D,E). I'd like to make a new product family array that consists of all the item arrays (e.g. A,B,C,D,E F,G,H,I,E A,G,H,J,K) Then I could use the product family array as a look up table to extract info. I'd like to get away from creating a worksheet to store the info, since it would only be temporary. Also, since new items are introduced every 6 months I can just add a new single item array, and then rebuild the product array. Any ideas? TIA, SK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Single Dimension to Multi Dimension Array
I don't think I understand what you are ultimately trying to do. From what
you posted, I would ask why you don't just create the multi-dimensional array right at the beginning and just use it for all your array needs? -- Rick (MVP - Excel) "Stathy K" wrote in message ... I haven't seen this anywhere. I'd like to combine a group of single arrays to a multi-dimension. The item array(s) have 5 characteristics (e.g. A,B,C,D,E). I'd like to make a new product family array that consists of all the item arrays (e.g. A,B,C,D,E F,G,H,I,E A,G,H,J,K) Then I could use the product family array as a look up table to extract info. I'd like to get away from creating a worksheet to store the info, since it would only be temporary. Also, since new items are introduced every 6 months I can just add a new single item array, and then rebuild the product array. Any ideas? TIA, SK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Single Dimension to Multi Dimension Array
I could do that with the current product line up, and it would work. But I
would think it would be easier to maintain single arrays as products are added or retired. For example a new product would be (excuse punctuation in syntax), ProdA=Array(1/1/09,3456,85,widget,east) ProdB=Array(1/1/09,7689,90,bolts,east) And then 6 mos later, I'd like to add/delete single arrays as product lineup changes: (New) ProdC=Array(7/1/09,1245,85,west) And I would only have to delete a line/array when a product is retired. I would like it to remain as simple as possible, so that other users would only have to enter a single dim array without needing any fancy VBA experience. After original post, I thought about a db but we do so much work in Excel I'd rather stay native. Thanks, SK "Rick Rothstein" wrote: I don't think I understand what you are ultimately trying to do. From what you posted, I would ask why you don't just create the multi-dimensional array right at the beginning and just use it for all your array needs? -- Rick (MVP - Excel) "Stathy K" wrote in message ... I haven't seen this anywhere. I'd like to combine a group of single arrays to a multi-dimension. The item array(s) have 5 characteristics (e.g. A,B,C,D,E). I'd like to make a new product family array that consists of all the item arrays (e.g. A,B,C,D,E F,G,H,I,E A,G,H,J,K) Then I could use the product family array as a look up table to extract info. I'd like to get away from creating a worksheet to store the info, since it would only be temporary. Also, since new items are introduced every 6 months I can just add a new single item array, and then rebuild the product array. Any ideas? TIA, SK |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Single Dimension to Multi Dimension Array
I'm still not 100% sure how you plan to put this idea to use; however, if I
understand your request correctly, there is a way to combine those existing arrays into a multi-dimensional array... sort of.<g The following method of producing a combined array will function the way you want, but the syntax will look odd. Given these single arrays... ProdA = Array("1/1/9", 3456, 85, widget, east) ProdB = Array("1/1/9", 7689, 90, bolts, east) ProdC = Array("7/1/9", 1245, 85, west) I notice that the ProdC array has less elements than the other two arrays... that is okay, this method will allow for that. You can make a multi-dimensional array from them by doing this... ComboArray = Array(ProdA, ProdB, ProdC) Here is where the odd syntax comes in. You do NOT address an element like this... ComboArray(2, 1) but, rather, you do it like this instead... ComboArray(2)(1) The indexing mechanism is the same an with a normal multi-dimensional array; for example, in the above Combo(2)(1) reference, assuming the default Option Base of 0 (so that array elements default to an initial index of 0), the 2nd element (1245) of the 3rd array (ProdC) is being addressed. In other words, the first number is parentheses is the index of the product arrays and the second number is the index number of the element within that array. You can find out the upper bound of the product arrays (ProdA, ProdB, etc.) with this... UBound(ComboArray) so that the number of product arrays is UBound(ComboArray)-1, again, assuming an Option Base of 0. Since each of the arrays that were combined can possibly have different number of elements in them, you must check the UBound for each one individually. For example, if you wanted to know the upper bound of the ProdA array, you would find out using this... UBound(ComboArray(0)) If you wanted to find the upper bound of the ProdC array, you would do this... UBound(ComboArray(2)) That is pretty much it... I sure hope all of the above is clear (if not, let me know and I'll try to word the concept differently). I cannot think of any other way to do what you appear to want to do within VB. Well, that is not entirely true... you might be able to use a Collection to house your product arrays, but I think the above array method may be more "robust". -- Rick (MVP - Excel) "Stathy K" wrote in message ... I could do that with the current product line up, and it would work. But I would think it would be easier to maintain single arrays as products are added or retired. For example a new product would be (excuse punctuation in syntax), ProdA=Array(1/1/09,3456,85,widget,east) ProdB=Array(1/1/09,7689,90,bolts,east) And then 6 mos later, I'd like to add/delete single arrays as product lineup changes: (New) ProdC=Array(7/1/09,1245,85,west) And I would only have to delete a line/array when a product is retired. I would like it to remain as simple as possible, so that other users would only have to enter a single dim array without needing any fancy VBA experience. After original post, I thought about a db but we do so much work in Excel I'd rather stay native. Thanks, SK "Rick Rothstein" wrote: I don't think I understand what you are ultimately trying to do. From what you posted, I would ask why you don't just create the multi-dimensional array right at the beginning and just use it for all your array needs? -- Rick (MVP - Excel) "Stathy K" wrote in message ... I haven't seen this anywhere. I'd like to combine a group of single arrays to a multi-dimension. The item array(s) have 5 characteristics (e.g. A,B,C,D,E). I'd like to make a new product family array that consists of all the item arrays (e.g. A,B,C,D,E F,G,H,I,E A,G,H,J,K) Then I could use the product family array as a look up table to extract info. I'd like to get away from creating a worksheet to store the info, since it would only be temporary. Also, since new items are introduced every 6 months I can just add a new single item array, and then rebuild the product array. Any ideas? TIA, SK |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Single Dimension to Multi Dimension Array
Rick,
Thanks for your feedback. You've given me good guidance on how to move ahead. I'll apply your concepts and see if it works out. (Note: All the arrays have the same # of elements; I just typed ProdC incorrectly.) Thanks, Stathy "Rick Rothstein" wrote: I'm still not 100% sure how you plan to put this idea to use; however, if I understand your request correctly, there is a way to combine those existing arrays into a multi-dimensional array... sort of.<g The following method of producing a combined array will function the way you want, but the syntax will look odd. Given these single arrays... ProdA = Array("1/1/9", 3456, 85, widget, east) ProdB = Array("1/1/9", 7689, 90, bolts, east) ProdC = Array("7/1/9", 1245, 85, west) I notice that the ProdC array has less elements than the other two arrays... that is okay, this method will allow for that. You can make a multi-dimensional array from them by doing this... ComboArray = Array(ProdA, ProdB, ProdC) Here is where the odd syntax comes in. You do NOT address an element like this... ComboArray(2, 1) but, rather, you do it like this instead... ComboArray(2)(1) The indexing mechanism is the same an with a normal multi-dimensional array; for example, in the above Combo(2)(1) reference, assuming the default Option Base of 0 (so that array elements default to an initial index of 0), the 2nd element (1245) of the 3rd array (ProdC) is being addressed. In other words, the first number is parentheses is the index of the product arrays and the second number is the index number of the element within that array. You can find out the upper bound of the product arrays (ProdA, ProdB, etc.) with this... UBound(ComboArray) so that the number of product arrays is UBound(ComboArray)-1, again, assuming an Option Base of 0. Since each of the arrays that were combined can possibly have different number of elements in them, you must check the UBound for each one individually. For example, if you wanted to know the upper bound of the ProdA array, you would find out using this... UBound(ComboArray(0)) If you wanted to find the upper bound of the ProdC array, you would do this... UBound(ComboArray(2)) That is pretty much it... I sure hope all of the above is clear (if not, let me know and I'll try to word the concept differently). I cannot think of any other way to do what you appear to want to do within VB. Well, that is not entirely true... you might be able to use a Collection to house your product arrays, but I think the above array method may be more "robust". -- Rick (MVP - Excel) "Stathy K" wrote in message ... I could do that with the current product line up, and it would work. But I would think it would be easier to maintain single arrays as products are added or retired. For example a new product would be (excuse punctuation in syntax), ProdA=Array(1/1/09,3456,85,widget,east) ProdB=Array(1/1/09,7689,90,bolts,east) And then 6 mos later, I'd like to add/delete single arrays as product lineup changes: (New) ProdC=Array(7/1/09,1245,85,west) And I would only have to delete a line/array when a product is retired. I would like it to remain as simple as possible, so that other users would only have to enter a single dim array without needing any fancy VBA experience. After original post, I thought about a db but we do so much work in Excel I'd rather stay native. Thanks, SK "Rick Rothstein" wrote: I don't think I understand what you are ultimately trying to do. From what you posted, I would ask why you don't just create the multi-dimensional array right at the beginning and just use it for all your array needs? -- Rick (MVP - Excel) "Stathy K" wrote in message ... I haven't seen this anywhere. I'd like to combine a group of single arrays to a multi-dimension. The item array(s) have 5 characteristics (e.g. A,B,C,D,E). I'd like to make a new product family array that consists of all the item arrays (e.g. A,B,C,D,E F,G,H,I,E A,G,H,J,K) Then I could use the product family array as a look up table to extract info. I'd like to get away from creating a worksheet to store the info, since it would only be temporary. Also, since new items are introduced every 6 months I can just add a new single item array, and then rebuild the product array. Any ideas? TIA, SK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Single Dimension to Multi Dimension Array
If you have any trouble with the concepts I presented to you, feel free to
post back for clarification. I figured your ProdC entry was mistyped, but it doesn't change anything... the method I showed you works the same whether the individual array elements have the same number of elements or not. However, knowing that they do have the same number of elements, you can then check the upper bound of any one of the included arrays to get the upper bound for all of them. This means if you want to know the upper bound of the 2nd element, just executing this... UBound(ComboArray(0)) will return it. The convenience being that there is always going to be a first (product) element no matter how many product elements are present in the ComboArray, so testing against that first element will always work whether there is one or one hundred product arrays included in the ComboArray. -- Rick (MVP - Excel) "Stathy K" wrote in message ... Rick, Thanks for your feedback. You've given me good guidance on how to move ahead. I'll apply your concepts and see if it works out. (Note: All the arrays have the same # of elements; I just typed ProdC incorrectly.) Thanks, Stathy "Rick Rothstein" wrote: I'm still not 100% sure how you plan to put this idea to use; however, if I understand your request correctly, there is a way to combine those existing arrays into a multi-dimensional array... sort of.<g The following method of producing a combined array will function the way you want, but the syntax will look odd. Given these single arrays... ProdA = Array("1/1/9", 3456, 85, widget, east) ProdB = Array("1/1/9", 7689, 90, bolts, east) ProdC = Array("7/1/9", 1245, 85, west) I notice that the ProdC array has less elements than the other two arrays... that is okay, this method will allow for that. You can make a multi-dimensional array from them by doing this... ComboArray = Array(ProdA, ProdB, ProdC) Here is where the odd syntax comes in. You do NOT address an element like this... ComboArray(2, 1) but, rather, you do it like this instead... ComboArray(2)(1) The indexing mechanism is the same an with a normal multi-dimensional array; for example, in the above Combo(2)(1) reference, assuming the default Option Base of 0 (so that array elements default to an initial index of 0), the 2nd element (1245) of the 3rd array (ProdC) is being addressed. In other words, the first number is parentheses is the index of the product arrays and the second number is the index number of the element within that array. You can find out the upper bound of the product arrays (ProdA, ProdB, etc.) with this... UBound(ComboArray) so that the number of product arrays is UBound(ComboArray)-1, again, assuming an Option Base of 0. Since each of the arrays that were combined can possibly have different number of elements in them, you must check the UBound for each one individually. For example, if you wanted to know the upper bound of the ProdA array, you would find out using this... UBound(ComboArray(0)) If you wanted to find the upper bound of the ProdC array, you would do this... UBound(ComboArray(2)) That is pretty much it... I sure hope all of the above is clear (if not, let me know and I'll try to word the concept differently). I cannot think of any other way to do what you appear to want to do within VB. Well, that is not entirely true... you might be able to use a Collection to house your product arrays, but I think the above array method may be more "robust". -- Rick (MVP - Excel) "Stathy K" wrote in message ... I could do that with the current product line up, and it would work. But I would think it would be easier to maintain single arrays as products are added or retired. For example a new product would be (excuse punctuation in syntax), ProdA=Array(1/1/09,3456,85,widget,east) ProdB=Array(1/1/09,7689,90,bolts,east) And then 6 mos later, I'd like to add/delete single arrays as product lineup changes: (New) ProdC=Array(7/1/09,1245,85,west) And I would only have to delete a line/array when a product is retired. I would like it to remain as simple as possible, so that other users would only have to enter a single dim array without needing any fancy VBA experience. After original post, I thought about a db but we do so much work in Excel I'd rather stay native. Thanks, SK "Rick Rothstein" wrote: I don't think I understand what you are ultimately trying to do. From what you posted, I would ask why you don't just create the multi-dimensional array right at the beginning and just use it for all your array needs? -- Rick (MVP - Excel) "Stathy K" wrote in message ... I haven't seen this anywhere. I'd like to combine a group of single arrays to a multi-dimension. The item array(s) have 5 characteristics (e.g. A,B,C,D,E). I'd like to make a new product family array that consists of all the item arrays (e.g. A,B,C,D,E F,G,H,I,E A,G,H,J,K) Then I could use the product family array as a look up table to extract info. I'd like to get away from creating a worksheet to store the info, since it would only be temporary. Also, since new items are introduced every 6 months I can just add a new single item array, and then rebuild the product array. Any ideas? TIA, SK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multi dimension/axis single series bar chart | Charts and Charting in Excel | |||
Setting Range value using a single dimension array | Excel Programming | |||
Put values into excel range from single dimension array | Excel Programming | |||
Array transfer - 1 dimension v. 2 dimension | Excel Programming | |||
single dimension array | Excel Programming |