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.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1		2019	4		2019
2		2013	10		2020
3		2015	8		2021
4					2022
5					2013
6					2014
7					2015
8					2016
9					2017
10					2018
11					2019
12					2020
13					2021
14					2022
15					2015
16					2016
17					2017
18					2018
19					2019
20					2020
21					2021
22					2022
Main

Cell Formulas
Range	Formula
E1:E22	E1	=LET(s,SCAN(0,C1:C3,LAMBDA(a,b,a+b)),x,SEQUENCE(MAX(s)),XLOOKUP(x,s,B1:B3,,1)+DROP(REDUCE(0,C1:C3,LAMBDA(x,y,VSTACK(x,SEQUENCE(y,,0)))),1))
Dynamic array formulas.

 

[mbarbera83]

This is amazing thanks.
If I had a name in A1:A3 example Name1, Name2, Name3,
How would you have this be populated in Column D next to the years?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	A	2019	4		A	2019
2	B	2013	10		A	2020
3	C	2015	8		A	2021
4					A	2022
5					B	2013
6					B	2014
7					B	2015
8					B	2016
9					B	2017
10					B	2018
11					B	2019
12					B	2020
13					B	2021
14					B	2022
15					C	2015
16					C	2016
17					C	2017
18					C	2018
19					C	2019
20					C	2020
21					C	2021
22					C	2022
Main

Cell Formulas
Range	Formula
E1:F22	E1	=LET(s,SCAN(0,C1:C3,LAMBDA(a,b,a+b)),x,SEQUENCE(MAX(s)),HSTACK(XLOOKUP(x,s,A1:A3,,1),XLOOKUP(x,s,B1:B3,,1)+DROP(REDUCE(0,C1:C3,LAMBDA(x,y,VSTACK(x,SEQUENCE(y,,0)))),1)))
Dynamic array formulas.

 

[mbarbera83]

Or to make it even easier - how do I make the array create the example below by repeating a row x many times, then moving onto the next row.

I can always do the year based on a formula

Name1

Name1

Name1

Name1

Name2

 

[mbarbera83]

Thank yo

How about

Fluff.xlsm
	A	B	C	D	E	F
1	A	2019	4		A	2019
2	B	2013	10		A	2020
3	C	2015	8		A	2021
4					A	2022
5					B	2013
6					B	2014
7					B	2015
8					B	2016
9					B	2017
10					B	2018
11					B	2019
12					B	2020
13					B	2021
14					B	2022
15					C	2015
16					C	2016
17					C	2017
18					C	2018
19					C	2019
20					C	2020
21					C	2021
22					C	2022
Main

Cell Formulas
Range	Formula
E1:F22	E1	=LET(s,SCAN(0,C1:C3,LAMBDA(a,b,a+b)),x,SEQUENCE(MAX(s)),HSTACK(XLOOKUP(x,s,A1:A3,,1),XLOOKUP(x,s,B1:B3,,1)+DROP(REDUCE(0,C1:C3,LAMBDA(x,y,VSTACK(x,SEQUENCE(y,,0)))),1)))
Dynamic array formulas.

Thank you. this is amazing! it worked for a small range of data, but i am getting a calc error for the bigger range, is there a reason why?

Block Name	Date prepped	age at current
U1	2018​	4​
U2	2000​	22​
U3	2017​	5​
U4	2008​	14​
U5	2009​	13​
U6	2028​	0​
U7	2011​	11​
U8	2014​	8​
U9	2020​	2​
U10	2016​	6​
U11	2009​	13​
U12	2013​	9​
U13	2014​	8​
U14	2030​	0​
U15	2015​	7​
U16	2008​	14​
U17	2015​	7​
U18	2013​	9​
U19	2005​	17​
U20	2004​	18​
U21	2028​	0​
U22	2014​	8​
U23	2018​	4​
U24	2004​	18​
U25	2004​	18​
U26	2012​	10​
U27	2013​	9​
U28	2018​	4​
U29	2018​	4​
U30	2016​	6​
U31	2008​	14​
U32	2005​	17​
U33	2005​	17​
U34	2014​	8​
U35	2010​	12​
U36	2004​	18​
U37	2016​	6​
U38	2006​	16​
U39	2006​	16​
U40	2015​	7​
U41	2016​	6​
U42	2014​	8​
U43	2009​	13​
U44	2011​	11​
U45	2015​	7​
U46	2015​	7​
U47	2005​	17​
U48	2008​	14​
U49	2019​	3​
U50	2005​	17​

 

[mbarbera83]

I notice the Zero's are creating issues - is there a way to skip the Zero's?

 

[Fluff]

is there a reason why?

Yes you have 0 values in there 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,C2:C51,LAMBDA(x,y,IF(y=0,0,VSTACK(x,SEQUENCE(y,,0))))),1)))

 

[mbarbera83]

Yes you have 0 values in there 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,C2:C51,LAMBDA(x,y,IF(y=0,0,VSTACK(x,SEQUENCE(y,,0))))),1)))

You are a Legend!!!!!! Thank you thank you thank you!

 

[Fluff]

You're welcome & thanks for the feedback.