Mar 12 2023 07:45 AM
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
Mar 12 2023 08:21 AM
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.
Mar 12 2023 10:41 AM
SolutionIt'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))))
Mar 12 2023 12:46 PM
@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
Mar 12 2023 12:49 PM
Mar 12 2023 01:49 PM
Mar 12 2023 10:41 AM
SolutionIt'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))))