Array with Let and Scan and V/Hstack

[Holger]

Hi Everyone,

the initial data is from a previous post to learn and train however, I'm stuck and would appreciate help.

Thank you so much for any guidance and assistance.

SCAN to count Sections.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	Section 1			Section 2			Section 3
3	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8			Section 1	Column1		1
4												Column2
5												Column3
6											Section 2	Column4		2
7												Column5
8												Column6
9											Section 3	Column7		3
10												Column8
11
12
13
14
15											Section	1						=LET( arr, INDEX(L3#,,1)<>"", IF(arr, SCAN(0, arr, LAMBDA(a,v, a+v)), ""), Vstack( Hstack("Section","Running"), Hstack(L3:L10,arr) ) )
16
17
18											Section	2
19
20
21											Section	3
22
23
24
25											Challenge:
26												S15 is my attempt to take O3 and add col L3:L10 but it is not working
27												Please note that S15 starts with ' to keep current attempt so you need to remove to test, which gives an error
28												correct formula S15 to resemble L33#
29												delete emptry rows
30
31
32											desired end result
33											Section	1
34											Section	2
35											Section	3
Sheet1

Cell Formulas
Range	Formula
L3:M10	L3	=TRANSPOSE(T(INDEX(2:3, {1;2}, COLUMN(Tbl))))
O3:O10	O3	=LET(arr, INDEX(L3#,,1)<>"", IF(arr, SCAN(0, arr, LAMBDA(a,v, a+v)), ""))
L15:M22	L15	=HSTACK(IF(ISERROR(LEFT(TAKE(L3#,,1),FIND(" ",TAKE(L3#,,1))-1)),"",LEFT(TAKE(L3#,,1),FIND(" ",TAKE(L3#,,1))-1)),O3#)
L33:M35	L33	=FILTER(L15:M21,L15:L21<>"")
Dynamic array formulas.

 

[HongRu]

Try it.

=LET(
    x_1, TRANSPOSE(INDEX(2:2, 1, COLUMN(Tbl))),
    x_2, FILTER(x_1, x_1 <> ""),
    x_3, COUNTA(x_2),
    x_4, SEQUENCE(x_3),
    Result, HSTACK(x_2, x_4),
    Result
)

 

[Holger]

That is awesome. Don't get it how you guys come up with this .....it looks so easy and efficient.

Much appreciated.

 

[Holger]

While @HongRu solution works, is there anyone who could explain to me, why my attempt with Scan function and adding a column does not work? Thanks very much.

 

[HongRu]

While @HongRu solution works, is there anyone who could explain to me, why my attempt with Scan function and adding a column does not work? Thanks very much.

Because English is not my native language, My Enlish ability might be not able to explain clearly.

Basically, your formula in S15 has syntax error in function "LET" .
I quote words from microsoft.com :
"To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126."

So, your formula in S15 :

=LET(
arr, INDEX(L3#,,1)<>"", IF(arr, SCAN(0, arr, LAMBDA(a,v, a+v)), ""),
Vstack(
Hstack("Section","Running"),
Hstack(L3:L10,arr)
)
)

At least, You must write an any NAME before IF(arr, SCAN(0, arr, LAMBDA(a,v, a+v)), "") .
For example,

=LET(
    arr, INDEX(L3#, , 1) <> "",
    ANY_NAME, IF(arr, SCAN(0, arr, LAMBDA(a, v, a + v)), ""),
    VSTACK(HSTACK("Section", "Running"), HSTACK(L3:L10, arr))
)

 

[Holger]

Thanks very much @HongRu, I'll work on this to better understand. Cheers very much.

 

[Cubist]

@HongRu answer doesn't give the expected result. It seems that you want to split "Section 1" into "Section" and "1". Not assigning a sequence. One option:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Section 1			Section 2			Section 3
3	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8			Section	1
4											Section	2
5											Section	3
Sheet9

Cell Formulas
Range	Formula
L3:M5	L3	=DROP(REDUCE("",TOCOL(B2:I2,1),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),1)
Dynamic array formulas.

 

[Holger]

@Cubist I think HongRu was correct and rather my data and wording was not correct. Here is a new attempt:

* I changed the wording section 1, section 2, section 3 to Monday, Tuesday, Wednesday to not confuse anyone
* What I wanted to learn is how to create a new column that starts with 1 and each time a value in the first column of array L3# changes increases by 1
* in an old post someone come up with O3# but as you can see, has empty rows
* HongRu helped and we got where I wanted in L24#
* BUT he does not use Scan function
* What I want to learn is, how can one get from O3# to L24# with the SCAN function, which should be possible, I just don't get it.


Thanks for any help on my learning curve


I

SCAN to count Sections.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Monday			Tuesday			Wednesday
3	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8			Monday	Column1		1
4												Column2
5												Column3
6											Tuesday	Column4		2
7												Column5
8												Column6
9											Wednesday	Column7		3
10												Column8
11
12
13
14
15
16
17
18
19
20
21
22
23
24											Section	Sequence
25											Monday	1
26											Tuesday	2
27											Wednesday	3
Sheet1

Cell Formulas
Range	Formula
L3:M10	L3	=TRANSPOSE(T(INDEX(2:3, {1;2}, COLUMN(Tbl))))
O3:O10	O3	=LET(arr, INDEX(L3#,,1)<>"", IF(arr, SCAN(0, arr, LAMBDA(a,v, a+v)), ""))
L24:M27	L24	=LET( input1, TRANSPOSE(INDEX(2:2, 1, COLUMN(Tbl))), input2, FILTER(input1, input1 <> ""), input3, COUNTA(input2), input4, SEQUENCE(input3), Result, VSTACK(HSTACK("Section","Sequence"),HSTACK(input2, input4)), Result )
Dynamic array formulas.

 

[Cubist]

If your goal is to use SCAN, then see L3#. Though there are many ways to accomplish without it e.g. L7#.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Monday			Tuesday			Wednesday
3	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8			Monday	1
4											Tuesday	2
5											Wednesday	3
6
7											Monday	1
8											Tuesday	2
9											Wednesday	3
Sheet9

Cell Formulas
Range	Formula
L3:M5	L3	=LET(t,TOCOL(B2:I2,1),HSTACK(t,SCAN(0,t,LAMBDA(a,b,a+(b<>"")))))
L7:M9	L7	=HSTACK(TOCOL(B2:I2,1),SEQUENCE(COUNTA(B2:I2)))
Dynamic array formulas.

 

[Holger]

L3# is what I was after. Brilliant mate @Cubist. You use the scan in the Hstack which is in the calc section of the LET function. I think this is where I was wrong.

Thanks very much for you time.

Cheers H