Results 1 to 10 of 10

Thread: How to use a 5 dimension dynamic array?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    100

    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?

  2. #2
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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

  3. #3
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    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.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    100

    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.

  5. #5
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    868

    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

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    100

    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.

  8. #8
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    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 ?

    BOFH Now, BOFH Past, Information on duplicates

    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...

  9. #9
    New Member
    Join Date
    Apr 2017
    Posts
    3

    Re: How to use a 5 dimension dynamic array?

    Quote Originally Posted by Ecniv View Post
    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

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Dec 2016
    Posts
    100

    Re: How to use a 5 dimension dynamic array?

    Quote Originally Posted by CTBarbarin View Post
    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
  •  



Click Here to Expand Forum to Full Width