infloent
infloent

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

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

Answers (2)

infloent
infloent

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

Tom Sharpe
Tom Sharpe

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))

enter image description here

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),""))

enter image description here

Upvotes: 2

Related Questions