Given a list of values and a number n for each value, is there a way to create a 1-dimensional list with each value repeating n times?
| A | B | C | |
|---|---|---|---|
| 1 | animal | # repeats | result |
| 2 | aardvark | 3 | aardvark |
| 3 | badger | 0 | aardvark |
| 4 | capybara | 1 | aardvark |
| 5 | duck | 2 | capybara |
| 6 | duck | ||
| 7 | duck |
Ideally, this would be a single formula in C2.
I'm working in Google Sheets, but I'd be interested in Excel answers, too.
Note that this is different from questions like these, which have the same n for all values.
You can try either
=TOCOL(MAP(A2:A,B2:B,LAMBDA(a,b,WRAPROWS(a,b,a))),3)
OR
=LAMBDA(z,FILTER(z,z<>""))(FLATTEN(MAP(A2:A,B2:B,LAMBDA(a,b,IF(a="",,IFERROR(SPLIT(REPT(a&"|",b),"|")))))))
XLOOKUP or XMATCH functions (23s on 10k rows), the superior MATCH function is used with the INDEX function (3s on 10k rows).LET
=LET(Data,A2:A5,Repeats,B2:B5,
Both,HSTACK(Data,Repeats),Filtered,FILTER(Both,Repeats>0),
dData,TAKE(Filtered,,1),dStacked,VSTACK(dData,""),
rData,TAKE(Filtered,,-1),rSequence,SEQUENCE(SUM(rData)),
rStacked,VSTACK(0,rData),rScanned,SCAN(1,rStacked,LAMBDA(a,b,a+b)),
rIndexes,MATCH(rSequence,rScanned),
Result,INDEX(dStacked,rIndexes),Result)
If you prefer fewer variables:
=LET(Data,A2:A5,Repeats,B2:B5,
Filtered,FILTER(HSTACK(Data,Repeats),Repeats>0),
dStacked,VSTACK(TAKE(Filtered,,1),""),
rData,TAKE(Filtered,,-1),rSequence,SEQUENCE(SUM(rData)),
rScanned,SCAN(1,VSTACK(0,rData),LAMBDA(a,b,a+b)),
Result,INDEX(dStacked,MATCH(rSequence,rScanned)),Result)
Screenshot Formulas
F2 =HSTACK(A2:A5,B2:B5)
H2 =FILTER(F3#,B2:B5>0)
J2 =TAKE(H3#,,1)
K2 =VSTACK(J3#,"")
L2 =TAKE(H3#,,-1)
M2 =SEQUENCE(SUM(L3#))
N2 =VSTACK(0,L3:L5)
O2 =SCAN(1,N3#,LAMBDA(a,b,a+b))
P2 =MATCH(M3#,O3#)
Q2 =INDEX(K3#,P3#)
R2 =LAMBDA(Data,Repeats,LET(
Both,HSTACK(Data,Repeats),Filtered,FILTER(Both,Repeats>0),
dData,TAKE(Filtered,,1),dStacked,VSTACK(dData,""),
rData,TAKE(Filtered,,-1),rSequence,SEQUENCE(SUM(rData)),
rStacked,VSTACK(0,rData),rScanned,SCAN(1,rStacked,LAMBDA(a,b,a+b)),
rIndexes,MATCH(rSequence,rScanned),
Result,INDEX(dStacked,rIndexes),Result))(A2:A5,B2:B5)
S2 =LAMBDA(Data,Repeats,LET(
Both,HSTACK(Data,Repeats),Filtered,FILTER(Both,Repeats>0),
dData,TAKE(Filtered,,1),dStacked,VSTACK(dData,""),
rData,TAKE(Filtered,,-1),rSequence,SEQUENCE(SUM(rData)),
rStacked,VSTACK(0,rData),rScanned,SCAN(1,rStacked,LAMBDA(a,b,a+b)),
rIndexes,MATCH(rSequence,rScanned),
Result,INDEX(dStacked,rIndexes),Result))
Using the last formula, define a name e.g. RepeatSeq, and use the name instead:
=RepeatSeq(A2:A5,B2:B5)
pretty much the same:
=INDEX(TRIM(TRANSPOSE(SPLIT(QUERY(
REPT(A2:A10&"♠", B2:B10), ,9^9), "♠"))))
