Reputation: 41
How to build a sequence 2 cols by n rows based on a col range of values paired with a col of times to repeat the value.
Also the options to give and starting index would be good.
The values will be =IMAGE("url") so the images need to work when the sequence is built.
Definition
Index | 1
----------
Value | NR
----------
img1 | 5
----------
img2 | 4
----------
img3 | 3
Desired Result (With starting index 1)
img1 | img1
-----------
img1 | img1
-----------
img1 | img2
-----------
img2 | img2
-----------
img2 | img3
-----------
img3 | img3
Desired Result (With starting index 3)
|
-----------
| img1
-----------
img1 | img1
-----------
img1 | img1
-----------
img2 | img2
-----------
img2 | img2
-----------
img3 | img3
-----------
img3 |
Upvotes: 0
Views: 55
Reputation: 41
I've added a + 1
to all C2
occurrences in Tom's formula just to make the empty cells added match the index
number.
=ArrayFormula(if(sequence((sum(B2:B)+(C2+1))/2,2,1)>=(C2+1),vlookup(sequence((sum(B2:B)+(C2+1))/2,2,1)-(C2+1),{sumif(row(B2:B),"<="&row(B2:B),B2:B)-B2:B,A2:A},2),""))
Works perfectly
Many Thanks Tom!
Upvotes: 0
Reputation: 34370
Here is the formula for the case where starting index is 1:
=ArrayFormula(vlookup(sequence((sum(B2:B)+1)/2,2,0),{sumif(row(B2:B),"<="&row(B2:B),B2:B)-B2:B,A2:A},2))
The idea is to look up the value generated by Sequence (0 to 11) in the cumulative sum of the NR's up to the previous row to the corresponding image.
EDIT
Here is the updated formula to allow for a start index>1:
=ArrayFormula(if(sequence((sum(B2:B)+C2)/2,2,1)>=C2,vlookup(sequence((sum(B2:B)+C2)/2,2,1)-C2,{sumif(row(B2:B),"<="&row(B2:B),B2:B)-B2:B,A2:A},2),""))
Upvotes: 2