Create array based on cell reference

[mbarbera83]

Hi all.

I have a list of data that I need an array created

	A	B	C
1	X	2019	4
2	Y	2013	10
3	Z	2015	8
4

I have Tried MakeArray with Lambda, but i cant seem to get it right I need the array to populate the following, 1 column with multiple rows, number of rows determined by column C, and the value of each row to be the starting point in Column B, + 1 year for each row
Start with the year in B1, and then add 1 year on for each row - number of rows determined by column C - in this case 4 rows of X

B1

B1 + 1

B1 + 1 + 1

B1 + 1 + 1 + 1

B2

B2 + 1

B2 + 1 +1 - this to continue for 10 rows

I would really appreciate help in this regard.

 

[mbarbera83]

You're welcome & thanks for the feedback.

it seems to be creating an error from U36?

 

[mbarbera83]

You're welcome & thanks for the feedback.

Fluff. There seems to be an error from U36, as well as the starting point of U3 isn't starting on the correct date?

U1	2019
U1	2020
U1	2021
U1	2022
U3	2022
U3	2023
U3	2024

U35	2026
U36	2021
U36	#N/A
U36	#N/A
U36	#N/A

 

[Fluff]

That's because of the zeros, try

=LET(s,SCAN(0,C2:C51,LAMBDA(a,b,a+b)),x,SEQUENCE(MAX(s)),HSTACK(XLOOKUP(x,s,A2:A51,,1),XLOOKUP(x,s,B2:B51,,1)+DROP(REDUCE(0,FILTER(C2:C51,C2:C51<>0),LAMBDA(x,y,VSTACK(x,SEQUENCE(y,,0)))),1)))

 

[mbarbera83]

That's because of the zeros, try

=LET(s,SCAN(0,C2:C51,LAMBDA(a,b,a+b)),x,SEQUENCE(MAX(s)),HSTACK(XLOOKUP(x,s,A2:A51,,1),XLOOKUP(x,s,B2:B51,,1)+DROP(REDUCE(0,FILTER(C2:C51,C2:C51<>0),LAMBDA(x,y,VSTACK(x,SEQUENCE(y,,0)))),1)))

Fantastic! thank you

 

[Fluff]

You're welcome.