Forum Discussion
J-Des000
Dec 23, 2024Brass Contributor
Repeating Numbers
Hi,
Trying to repeat the same number (from an array) an X number of times. How do I go about this? I've seen some examples using the OFFSET function, but trying to do this by avoiding volatile functions.
EX:
Number Array:
3
7
6
Repeat: 3 times
Output:
3
3
3
7
7
7
6
6
6
Thanks in advance!
- NimootuneCopper Contributorundefined
- Harun24HRBronze Contributor
For older versions of excel.
=IFERROR(INDEX($A$2:$A$4,ROUNDUP(ROW(A1)/3,0)),"")
- OliverScheurichGold Contributor
=TOCOL(IFNA(EXPAND(A1:A3,,C1),A1:A3))
- Patrick2788Silver Contributor
Before regex, I had a recursive function for this task, but REGEXEXTRACT has made it obsolete!
This will handle numbers and texts:
=LET( rep, SEQUENCE(, 3, 1, 0), extract, REGEXEXTRACT(texts, ".+", rep), TOCOL(extract) )
I may get cited by those who wish to simplify by removing LET but I tend to favor making the notation as obvious as I can to make it more readable.
As variant
=DROP( REDUCE(0, numbers, LAMBDA( a ,v, VSTACK(a, SEQUENCE(v,,v,0) ) ) ), 1)
- m_tarlerBronze Contributor
with 365 you have a lot of options. Here are a few:
=TOCOL(SEQUENCE(,3,1,0)*{3;7;6})
=TOCOL(IF(SEQUENCE(,3),{3;7;6}))
=CHOOSE(INT(SEQUENCE(9,,0)/3)+1,3,7,6)