-
Apr 10th, 2017, 10:47 AM
#1
Thread Starter
Lively Member
How to use a 5 dimension dynamic array?
Hello everyone, I am trying to collect 5 different pieces of information on an excel sheet. I am looping through each used row and in each row there are 5 different cells I need to collect info from. I am trying to collect all pieces of data in one loop (5 pieces of data per pass in the loop). What I am trying to avoid doing is having to use 5 different arrays to collect 5 different piece of data per loop.
Also, I don't know how many used rows there will be hence the reason I want the array to be dynamic. What do you guys think is the best solution here?
-
Apr 10th, 2017, 12:23 PM
#2
Re: How to use a 5 dimension dynamic array?
Johnny
Are you using ..
.. VBA (ie, a macro within the Excel sheet)
.. VB 6
.. VB.Net
.. other
While a 5-dimension array is possible, I'm thinking you'd only need a 2-dimension array
to match the Excel sheet.
.. 1st dimension would be the row
.. 2nd dimension would be the col.
A brute force method would be to get the max row of the sheet, and set the 1st dimension to that.
A possible more elegant approach would be to set 1st dimension to, say, 100, and issue a ReDim Preserve statement each time you reach an increment of 100.
Hope that gets you started
Spoo
-
Apr 10th, 2017, 12:44 PM
#3
Re: How to use a 5 dimension dynamic array?
I actually find it hard to see how a 5-dimensional array would work.
The question is just showing a common misunderstanding of what array dimensions mean and how they work.
The two dimensional array is one solution, another would be a single dimensional array with a User Defined Type for the column data.
A third solution could be a single dimensional array of single dimensional arrays (much less common).
Depending on the language, there are also dynamic objects known as collections that can be used, which should alleviate the need for ReDim Preserve, but doing the ReDim Preserve in chunks as Spooman says is not a bad way to go if you are going to dynamically be allocating array entries.
Just be aware that you can only change the last dimension and preserve your data, so your column data would need to be the first dimension (the leftmost) and the row dimension would be the second (the right most), which is the dynamic dimension.
-
Apr 10th, 2017, 01:40 PM
#4
Thread Starter
Lively Member
Re: How to use a 5 dimension dynamic array?
Okay, well essentially what I need to do is, sum data that is tagged to a number in column A. For example if half the rows from 1 through 25 have the number 2 in column A, and the other half has number 1, but not in any chronological order, then the code will produce 2 summations because there are only 2 different criteria in column A. If there were more numbers, say numbers 1 through 4, then there would be 4 summations.
Here is a visual example:
Criteria Value1 Value2 Value3 Value4
1 45.2 1200 12 48
3 40 1300 12 15
1 17 1560 56 51
2 2 8900 48 85
3 5 1200 15 15
3 16 1588 89 18
2 8 1548 12 15
2 85.3 1233 51 15
Sum of Criteria 1 = Value1 Value2 Value3 Value4
Sum of Criteria 2 = Value1 Value2 Value3 Value4
etc..
I was thinking that an array might be the way to go. Because I was going to collect in an array the values for ArraySum(Criteria, Value1, Value2, Value3, Value4). I could then sort the elements for criteria and for each element that was the same, do a summation of each value for that number.
So in other words, the first dimension would provide a key for me to know how to sum up the numbers according to the spreadsheet. I hope this makes sense as to why I thought of using a 5 dimension array. Truth is I probably made it more complicated than it needs to be. I tend to do that.
Thanks!
Edit: Cant seem to get formatting right for table above, sorry, but try to imagine each of those columns having numbers under them and not all squished together. Every time I try to save the post the formatting goes bad.
-
Apr 10th, 2017, 02:46 PM
#5
Re: How to use a 5 dimension dynamic array?
Johnny
Re "squished" .. yes, the generic editor removes excess spaces.
However, if you wrap it with the CODE feature (even though it isn't code), it should preserve spaces.
With a little playing around, I think this is what you were trying to do.
Code:
Criteria Value1 Value2 Value3 Value4
1 45.2 1200 12 48
3 40 1300 12 15
1 17 1560 56 51
2 2 8900 48 85
3 5 1200 15 15
3 16 1588 89 18
2 8 1548 12 15
2 85.3 1233 51 15
BTW, use the # feature on the editor menu bar to insert the CODE wrapper.
Approach #1
OK, so if I read this properly
Sum of Criteria 1 = Value1 Value2 Value3 Value4
Sum of Criteria 2 = Value1 Value2 Value3 Value4
etc
and "sort" your example, the result for Criteria 1 would be 2989.2 ..
Code:
Criteria Value1 Value2 Value3 Value4
1 45.2 1200 12 48 >> 1305.2
1 17 1560 56 51 >> 1684
>> Sum of 1 = 2989.2
2 2 8900 48 85
2 8 1548 12 15
2 85.3 1233 51 15
3 40 1300 12 15
3 5 1200 15 15
3 16 1588 89 18
.. and the others would be in a similar fashion.
Is this the result you would expect?
Approach #2
Or, in the alternative
Code:
Criteria Value1 Value2 Value3 Value4
1 45.2 1200 12 48
1 17 1560 56 51
>> 62.2 2760 68 99 << Criteria 1 individual sums
2 2 8900 48 85
2 8 1548 12 15
2 85.3 1233 51 15
3 40 1300 12 15
3 5 1200 15 15
3 16 1588 89 18
Which matches your need?
Spoo
-
Apr 10th, 2017, 04:33 PM
#6
Re: How to use a 5 dimension dynamic array?
why not use union to create a non-contiguous range of all the cells
here is an example that gets 3 columns for all filled cells in column A
Code:
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("a1").Resize(lr)
Set r = Union(r, Range("f1").Resize(lr), Range("q1").Resize(lr))
MsgBox r.Address
you could also add the same cells row by row based on some criteria
another option might be to use ADO to return a recordset of the specific fields from the data
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 11th, 2017, 06:21 AM
#7
Thread Starter
Lively Member
Re: How to use a 5 dimension dynamic array?
Hey Spooman, thanks for the tips on posting. I didn't think of using the code formatting. The result I am looking for is alternative 1. Also, westconn1, I tested the code you provided and I can see where you are coming from. However, how do you parse the union range to make different summations?
Also, did you have any suggestions Spooman? Obviously I want to use the best method but I also want to learn as much as I can.
-
Apr 11th, 2017, 07:02 AM
#8
Re: How to use a 5 dimension dynamic array?
Might be missing something... but surely a pivot table would do all those summing for you?
And you can always code a pivot table.. just need a range selected or figure out the first and last row of data...
Depends on what you want to do with the data afterwards... or if you are testing yourself on vba coding, in which case loops and an an array.. plus a second array of totals?
If the goal is to output with the data sorted... cant you sort the original data and just loop it holding totals ?
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Apr 12th, 2017, 09:34 PM
#9
New Member
Re: How to use a 5 dimension dynamic array?
Originally Posted by Ecniv
Might be missing something...
Yeah, I was thinking the same thing as Ecniv...is there a reason you need to do this in code?
- If you don't need to sort and display/output the data and only need to summarize it in XL, in addition to pivot tables, SUMPRODUCT() function might work too.
- If you need to summarize on a separate sheet, named ranges might help
- If memory serves, you can use SUMPRODUCT() with named ranges on data on another sheet
- References to other files, if possible, might be tricky
- If the number of rows in the data changes, you can use dynamic named ranges.
There might be other/easier ways to accomplish what you need to do. You can always use code to automate some of the steps of the alternate ways.
HTH,
CTB
Last edited by CTBarbarin; Apr 12th, 2017 at 10:07 PM.
Reason: Formatting and clarification/added detail
-
Apr 27th, 2017, 07:30 AM
#10
Thread Starter
Lively Member
Re: How to use a 5 dimension dynamic array?
Originally Posted by CTBarbarin
Yeah, I was thinking the same thing as Ecniv...is there a reason you need to do this in code?
- If you don't need to sort and display/output the data and only need to summarize it in XL, in addition to pivot tables, SUMPRODUCT() function might work too.
- If you need to summarize on a separate sheet, named ranges might help
- If memory serves, you can use SUMPRODUCT() with named ranges on data on another sheet
- References to other files, if possible, might be tricky
- If the number of rows in the data changes, you can use dynamic named ranges.
There might be other/easier ways to accomplish what you need to do. You can always use code to automate some of the steps of the alternate ways.
HTH,
CTB
SO I coded a solution to my problem description above. And it works okay. However, I am interested in trying to use a pivot table as you suggested. How do you set that up to sum based on the criteria outlined above?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|