SOLVED

Expanding data in a column or row quickly ex: 1,2,3 --> 1,1,1,2,2,2,3,3,3

Copper Contributor

As the title suggests I need to expand a list of values but not in its current order again, instead adding the same value after it.  Doesn't feel that complicated but I don't know how to do it for a large amount of data which would save me a lot of time. Sorry if the wording is confusing, don't know how to explain it well. Please see my example below. 

Example expand row A by 3 times. 

Row A
23
53

142

352

43

Into this:

Row A

23

23
23

53

53

53

142

142

142

352

352

352

43

43

43

 

Thanks for your help. 

5 Replies
=MAKEARRAY(3*ROWS(Inputs);1;LAMBDA(I;j;INDEX(Inputs;INT((I-1)/3)+1;1)))
best response confirmed by mwin1680 (Copper Contributor)
Solution
Easier still (same concept): =INDEX(Inputs;INT((SEQUENCE(3*ROWS(Inputs))-1)/3)+1;1)

@mwin1680 

If you're on 365 and Insider, you might use this:

=LET(r,ROWS(list)*3,CHOOSEROWS(list,INT(SEQUENCE(r,,1,1/3))))
INDEX should work just as well as CHOOSEROWS.
Thank you. This worked well.
1 best response

Accepted Solutions
best response confirmed by mwin1680 (Copper Contributor)
Solution
Easier still (same concept): =INDEX(Inputs;INT((SEQUENCE(3*ROWS(Inputs))-1)/3)+1;1)

View solution in original post