REPT & SEQUENCE to stack results in column

[Georgiboy]

Hi all,

Seem to be having some brain fog this morning and have been pondering the below overnight. If i use REPT with SEQUENCE on one row, i.e. one text value and one number for REPT then i get my expected result. If i extend this range to two text cells and two number cells then i only get one of each value. I suppose i am not simply looking for the solution but interested to know why it only gives me one result per row - what is Excels logic:

Lambda-map examples.xlsx
	A	B	C	D	E	F	G
1	Two versions of a formula working in one row
2	Scania	5			Scania		Scania
3	Volvo	3			Scania		Scania
4					Scania		Scania
5					Scania		Scania
6					Scania		Scania
7
8	Same Formula with both rows
9	Scania	5			Scania		Scania
10	Volvo	3			Volvo		Volvo
11
12
13
14
15	Expected result
16	Scania	5			Scania		Scania
17	Volvo	3			Scania		Scania
18					Scania		Scania
19					Scania		Scania
20					Scania		Scania
21					Volvo		Volvo
22					Volvo		Volvo
23					Volvo		Volvo
Sheet5

Cell Formulas
Range	Formula
E2:E6	E2	=REPT(A2,SEQUENCE(B2,,1,0))
G2:G6	G2	=A2&T(SEQUENCE(B2))
E9:E10	E9	=REPT(A9:A10,SEQUENCE(B9:B10,,1,0))
G9:G10	G9	=A9:A10&T(SEQUENCE(B9:B10))
Dynamic array formulas.

 

[ISY]

Hi

=TOCOL(IF(SEQUENCE(,MAX(B2:B10))<=B2:B10,A2:A10,#N/A),3)

 

[JvdV]

SEQUENCE() is not going to work and give different sequences when applied to multiple cells. Instead, it will just output the 1st element in the sequence, hence you'd get the spilled {1;1}. Applied to your formulas this would then just give you one result each.

Is this what you are tyring perhaps:

Question.xlsx
	A	B	C	D
1	Scania	5		Scania
2	Volvo	3		Scania
3				Scania
4				Scania
5				Scania
6				Volvo
7				Volvo
8				Volvo
Sheet3

Cell Formulas
Range	Formula
D1:D8	D1	=XLOOKUP(SEQUENCE(SUM(B1:B2)),SCAN(0,B1:B2,LAMBDA(a,b,a+b)),A1:A2,,1)
Dynamic array formulas.

 

[Georgiboy]

Thank you both for the solutions, i am actually after a description of why the displayed examples give one result per REPT when looking at more than one row.

I want to get better understanding of the logic behind why Excel does this (not that it is wrong)

Just seems strange that for one row it spills the result but for two rows it contracts to one result per row.

 

[JvdV]

Yeah, like I said, the SEQUENCE() function is not going to be able to handle multiple reference and output multiple sequences. Imagine =SEQUENCE({5,3}), the function should then output what? {1,2,3,4,5,1,2,3}? It's not going to work like that and instead it will output the 1st element in each sequence as a result > {1,1}.

 

[Georgiboy]

Thanks - that helps my understanding, does that mean there could be a combination of something like BYROW with SEQUENCE that would run the SEQUENCE once per row and then stack the results calculated with REPT?

Sorr if the questions seem strange - i have spent the last 15 years writing code and now want to learn Excel 365 formula

 

[ISY]

there could be a combination of something like BYROW

you don't get multiple columns with BYROW

 

[JvdV]

Well, yeah, one option is what I've shown as a solution using SCAN(). This is a LAMBDA helper (just like BYROW() is) and would create a running summation of the values in your column B:B:

Question.xlsx
	A	B	C
1	Scania	5	5
2	Volvo	3	8
Sheet3

Cell Formulas
Range	Formula
C1:C2	C1	=SCAN(0,B1:B2,LAMBDA(a,b,a+b))
Dynamic array formulas.

Now, you would just need a single SEQUENCE() which is the length of summation of column B:B, which when applied to XLOOKUP we can tell this function to lookup the exact are next larger value and return the values from column A:A. For example, the value 7 is not found as an exact value, but the next larger value is 8. We then return 'Volvo'.

I hope that made sense :S

 

[JvdV]

If you really want to use SEQUENCE() more than once, you would need to stack the different results in an iterative way. One option is REDUCE(), another LAMBDA helper function:

Question.xlsx
	A	B	C	D
1	Scania	5		Scania
2	Volvo	3		Scania
3				Scania
4				Scania
5				Scania
6				Volvo
7				Volvo
8				Volvo
Sheet3

Cell Formulas
Range	Formula
D1:D8	D1	=DROP(REDUCE(0,ROW(A1:A2),LAMBDA(x,y,VSTACK(x,INDEX(A:A,y)&T(SEQUENCE(INDEX(B:B,y)))))),1)
Dynamic array formulas.

 

[Georgiboy]

Many thanks to both of you, you have helped me to understand why Excel is not doing what i tell it to