Forum Discussion

micyano's avatar
micyano
Copper Contributor
Nov 28, 2023

How to generate a table of data built up off of smaller parts?

Hello Excel Gurus

 

I can't even figure out how to ask this question so I'm hoping if you can look at the data set I'm working with, and how it should look after combining the information, someone can hopefully point me in the right direction.

 

 

The first table (on the left) is my information. 

Column A is the name of range of data

Column B is the bottom of the range - the bottom of each section is equal to the top of the section below [AKA the bottom of "B" is the top of "A"], there will never be a gap and "A" will always start at 0 (Although the names will vary, sometimes being the same name as another section)

Column C is the top of the range

Column D is the length of each portion

Column E is how many portions make up each section.

 

The second table (on the right) is what I need generated

Column G is the name from where the portion is from

Column H is the bottom of the portion

Column I is the top of the portion

 

I don't know what the final size of the Table A, so Table B needs to be able to grow dynamically.  Is this doable, or will I need to do this by hand for every set of data?

 

Thanks in Advance

 

Michael

  • michyano If I've understood you correctly, I think the error can be solved by filtering the data first, to exclude any blank rows / rows that contain all zeros.

     

    =LET(data, A2:E40,
    arr, FILTER(data, NOT(BYROW(data, LAMBDA(r, AND(r=0))))),
    DROP(REDUCE("", SEQUENCE(ROWS(arr)), LAMBDA(v,n, LET(
       acc, INDEX(arr, n, 4),
       top, SCAN(INDEX(arr, n, 2), IF(SEQUENCE(INDEX(arr, n, 5)), acc), LAMBDA(a,r, a+r)),
       name, INDEX(arr, n, 1),
       VSTACK(v, IFNA(HSTACK(name, top-acc, top), name))))), 1))

     

    As requested, the headers were also excluded in this example (data range starts at A2 instead of A1). An empty string was used as the initial_value for the REDUCE function, and the whole thing was wrapped in the DROP function to eliminate the first row from the results. Hopefully that works!

  • djclements's avatar
    djclements
    Bronze Contributor

    micyano Do you have Excel for MS365? If so, one possible solution is to use the REDUCE / VSTACK method, which iterates through the rows of the data range, performs the necessary calculations, then stacks the results together vertically.

     

    =LET(data, A1:E17, arr, DROP(data, 1),
    REDUCE(TAKE(data, 1, 3), SEQUENCE(ROWS(arr)), LAMBDA(v,n, LET(
       acc, INDEX(arr, n, 4),
       top, SCAN(INDEX(arr, n, 2), IF(SEQUENCE(INDEX(arr, n, 5)), acc), LAMBDA(a,r, a+r)),
       name, INDEX(arr, n, 1),
       VSTACK(v, IFNA(HSTACK(name, top-acc, top), name))))))

     

    For each row in the data range, the INDEX function is used to identify the values from each applicable column. The SCAN function is used to perform the primary calculation, which is to return the list of "Top" values in an array. The "Bottom" values are calculated simply by subtracting the accumulator (portion size) from the "Top" values. The IFNA and HSTACK functions are then used to stack the results together horizontally, along with the "Name". The results should look something like this:

     

    MS365 Solution with Dynamic Array Functions

    • michyano's avatar
      michyano
      Copper Contributor

      djclements 

      This is exactly what I needed, I could not have figured this out on my own thank you.  You've saved me a lot of time I very much appreciate it!  Now I need to dissect this whole thing to understand it.  I haven't used any of these formulas before other than index.  This is much more complicated of a code than I expected.

    • michyano's avatar
      michyano
      Copper Contributor

      Ok I've run into an issue trying to implement this into my worksheet. The data that are the inputs for the table are read from a dynamic table (of sorts, it is 40 rows high, with data usually in the top 10-20 rows, but at time can need more than 40 so the rows in that section of the table are copied and inserted into that portion of the workbook to expand the input table as to incorporate the associated calculations further down each row.)

      If I try and select the entire 40 lines as the input table, I get an error. If doesn't seem to matter if the extra rows it reads are blanks, or 0's.  Is there a way to ignore it?  I'd like the user of the sheet to not have to worry about altering the ranges of the calculations after the size of the input is changed.

       

       

       


      Also, not as important, I can work around it, but how do I stop it from adding the headings to the dynamic table? I tried changing the range to just not include the headings, but it then seems to skip the top section, (I suspect this has something to do with the DROP function).  I'm starting to understand the individual functions, but the combinations of them is still beyond me.  I suspect the name array that is being created is the titles for the columns, but I can't figure out how to remove that from the formula without everything breaking

      Ok it's not that but I still can't figure it out what is "v", I'm having trouble understanding the LAMBDA function, it lets you define an equation as a function to use I think, but I don't understand how it works.

      Thanks for your time

      Michael


      • djclements's avatar
        djclements
        Bronze Contributor

        michyano If I've understood you correctly, I think the error can be solved by filtering the data first, to exclude any blank rows / rows that contain all zeros.

         

        =LET(data, A2:E40,
        arr, FILTER(data, NOT(BYROW(data, LAMBDA(r, AND(r=0))))),
        DROP(REDUCE("", SEQUENCE(ROWS(arr)), LAMBDA(v,n, LET(
           acc, INDEX(arr, n, 4),
           top, SCAN(INDEX(arr, n, 2), IF(SEQUENCE(INDEX(arr, n, 5)), acc), LAMBDA(a,r, a+r)),
           name, INDEX(arr, n, 1),
           VSTACK(v, IFNA(HSTACK(name, top-acc, top), name))))), 1))

         

        As requested, the headers were also excluded in this example (data range starts at A2 instead of A1). An empty string was used as the initial_value for the REDUCE function, and the whole thing was wrapped in the DROP function to eliminate the first row from the results. Hopefully that works!

  • micyano 

    Sub generatetable()
    
    Dim i, j, k, l, m As Long
    
    Range("G:I").Clear
    
    j = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To j
    For k = 1 To Cells(i, 5).Value
    Cells(k + m + 1, 7).Value = Cells(i, 1).Value
    Cells(k + m + 1, 8).Value = Cells(i, 2).Value + Cells(i, 4).Value * (k - 1)
    Cells(k + m + 1, 9).Value = Cells(i, 2).Value + Cells(i, 4).Value * k
    Next k
    m = m + k - 1
    Next i
    
    End Sub

    An alternative could be this macro. Below is an example after running the macro in my sheet.

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor

    micyano 

     if with sql,

     

     

    create temp table aa as
    select *,split_num('1-'||No_of_Partions) grp from split_to_discrete;
    cli_split_data~aa~,~grp;
    select name NAME, Bottom+Partion_Size*(grp-1) BTM,Bottom+Partion_Size*grp TOP from aasplit;

     

Resources