Nov 27 2023 04:27 PM
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
Nov 27 2023 09:08 PM
@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:
Nov 27 2023 11:33 PM
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.
Nov 28 2023 12:55 AM - edited Nov 28 2023 01:08 AM
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
Nov 28 2023 01:37 AM
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!
Nov 28 2023 02:21 AM
@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!
Nov 28 2023 05:32 AM
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.
Nov 28 2023 05:56 AM
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;
Nov 28 2023 07:12 AM
Hi @micyano
With Power Query (pic. cropped)?
With data in Input Table (even with blanks rows), right-click on the green table > Refresh
Nov 28 2023 09:38 AM
Thanks so much! That works exactly!
Thank you everyone else who replied as well, you guys are awesome!
Nov 28 2023 01:37 AM
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!