Forum Discussion
[Excel 365] Positioning a list of values as a "staircase" with dynamic array
Good day dear Excel community
I'm scratching my head around a formula I need to write to create the range on the right with a dynamic array.
I need to do the following transformation using a dynamic array formula.
The values from the SourceRange column are listed twice (ideally, I would love the formula to handle x2, x3, etc. based on a parameter) with a new column each time, and with a new row only each time we move to the next value from the SourceRange.
The goal of this strange request is to dynamically create helper ranges that will be needed for creating some advanced charts such as variable column width charts. The solution needs to be with a dynamic array (no VBA).
Appreciate a lot any help I can get!
Olivier
It's a rare occasion that MUNIT gets some work.
=LET(r,ROWS(Input),matrix,MUNIT(r)*Input,CHOOSECOLS(matrix,INT(SEQUENCE(r*rep,,MIN(Input),1/rep))))
5 Replies
It's a rare occasion that MUNIT gets some work.
=LET(r,ROWS(Input),matrix,MUNIT(r)*Input,CHOOSECOLS(matrix,INT(SEQUENCE(r*rep,,MIN(Input),1/rep))))Patrick2788 : amazing. I didn't even know there was a MUNIT function in Excel. It takes me back years ago to math class!
Thanks for the fantastic help
- You're welcome! Glad to help.
Assuming that you have Microsoft 365 or use Excel Online:
=LET(n, COUNTA(SourceTable[SourceRange]), MAKEARRAY(n, Repeats*n, LAMBDA(r, c, IF((c>=Repeats*(r-1)+1)*(c<=Repeats*r), INDEX(SourceRange, r),""))))
SourceTable is the table in A2:A6
The data range of this table has been named SourceRange.
Repeats is a named cell that contains the number of times each value should be repeated.
Sample workbook attached.
- Thanks Hans, this works quite well!
Really appreciate the help received in such a timely manner.
