Array with Let and Scan and V/Hstack

Holger

Board Regular
Joined
Nov 22, 2017
Messages
79
Office Version
  1. 365
Platform
  1. MacOS
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
BCDEFGHIJKLMNOPQRSTU
2Section 1Section 2Section 3
3Column1Column2Column3Column4Column5Column6Column7Column8Section 1Column11
4Column2
5Column3
6Section 2Column42
7Column5
8Column6
9Section 3Column73
10Column8
11
12
13
14
15Section1=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
18Section2
19
20
21Section3
22
23
24
25Challenge:
26S15 is my attempt to take O3 and add col L3:L10 but it is not working
27Please note that S15 starts with ' to keep current attempt so you need to remove to test, which gives an error
28correct formula S15 to resemble L33#
29delete emptry rows
30
31
32desired end result
33Section1
34Section2
35Section3
Sheet1
Cell Formulas
RangeFormula
L3:M10L3=TRANSPOSE(T(INDEX(2:3, {1;2}, COLUMN(Tbl))))
O3:O10O3=LET(arr, INDEX(L3#,,1)<>"", IF(arr, SCAN(0, arr, LAMBDA(a,v, a+v)), ""))
L15:M22L15=HSTACK(IF(ISERROR(LEFT(TAKE(L3#,,1),FIND(" ",TAKE(L3#,,1))-1)),"",LEFT(TAKE(L3#,,1),FIND(" ",TAKE(L3#,,1))-1)),O3#)
L33:M35L33=FILTER(L15:M21,L15:L21<>"")
Dynamic array formulas.
 
Try it.
Excel Formula:
=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
)
 
Upvote 0
That is awesome. Don't get it how you guys come up with this 😳😭.....it looks so easy and efficient.

Much appreciated.
 
Upvote 0
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.
 
Upvote 0
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 :
Excel Formula:
=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,
Excel Formula:
=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))
)
 
Upvote 0
@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
BCDEFGHIJKLM
1
2Section 1Section 2Section 3
3Column1Column2Column3Column4Column5Column6Column7Column8Section1
4Section2
5Section3
Sheet9
Cell Formulas
RangeFormula
L3:M5L3=DROP(REDUCE("",TOCOL(B2:I2,1),LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ")))),1)
Dynamic array formulas.
 
Upvote 0
@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
BCDEFGHIJKLMNO
2MondayTuesdayWednesday
3Column1Column2Column3Column4Column5Column6Column7Column8MondayColumn11
4Column2
5Column3
6TuesdayColumn42
7Column5
8Column6
9WednesdayColumn73
10Column8
11
12
13
14
15
16
17
18
19
20
21
22
23
24SectionSequence
25Monday1
26Tuesday2
27Wednesday3
Sheet1
Cell Formulas
RangeFormula
L3:M10L3=TRANSPOSE(T(INDEX(2:3, {1;2}, COLUMN(Tbl))))
O3:O10O3=LET(arr, INDEX(L3#,,1)<>"", IF(arr, SCAN(0, arr, LAMBDA(a,v, a+v)), ""))
L24:M27L24=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.
 
Upvote 0
If your goal is to use SCAN, then see L3#. Though there are many ways to accomplish without it e.g. L7#.
Book1
BCDEFGHIJKLM
1
2MondayTuesdayWednesday
3Column1Column2Column3Column4Column5Column6Column7Column8Monday1
4Tuesday2
5Wednesday3
6
7Monday1
8Tuesday2
9Wednesday3
Sheet9
Cell Formulas
RangeFormula
L3:M5L3=LET(t,TOCOL(B2:I2,1),HSTACK(t,SCAN(0,t,LAMBDA(a,b,a+(b<>"")))))
L7:M9L7=HSTACK(TOCOL(B2:I2,1),SEQUENCE(COUNTA(B2:I2)))
Dynamic array formulas.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top