SOLVED

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

Copper Contributor

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.

micyano_2-1701130050059.png

 

 

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

9 Replies

@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 FunctionsMS365 Solution with Dynamic Array Functions

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

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.

michyano_0-1701162041681.png

 

 

 


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


best response confirmed by Hans Vogelaar (MVP)
Solution

@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!

@michyano Sorry, I missed your edit regarding a better understanding of the REDUCE / VSTACK method. It can be a tough one to get the hang of, for sure...

 

With the REDUCE function, SEQUENCE(ROWS(arr)) is used in the array parameter to generate a list of numbers from 1 to the total number of rows in arr. The n parameter defined in the LAMBDA function represents the current iteration number, as it loops through the sequence. If you're familiar with VBA For Next loops, it's similar to using:

 

For n = 1 to arr.Rows.Count
   'calculate something
Next n

 

The v parameter defined in the LAMBDA function represents the value (results) of the previous iteration. In the case of the first iteration, it refers to value specified in the intial_value parameter. The results of the calculations performed on each iteration are then stacked together with the previous results (v) using the VSTACK function. If you don't want the initial_value included in the final results, the DROP function is needed to remove it.

 

I hope that helps to better understand the logic. Cheers!

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

rearrange table.png

@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;

 

Screenshot_2023-11-28-21-52-24-477_cn.uujian.browser.jpg

Hi @micyano 

 

With Power Query (pic. cropped)?

 

Sample.png

With data in Input Table (even with blanks rows), right-click on the green table > Refresh

@djclements 

Thanks so much!  That works exactly!

 

Thank you everyone else who replied as well, you guys are awesome!

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@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!

View solution in original post