• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concat formula using LAMBDA and LET function

Hi All,
i trying to concat but facing error i have enclosed a excel file for your reference and in G Column output i required and in H Column in added the remarks, what iam trying to do, iam using this formula but facing error, like this i have 10000 rows pls support.

Code:
=LET(
    a, A2:A24,
    f, F2:F24,
    numPos, FILTER(SEQUENCE(ROWS(a)),ISNUMBER(--LEFT(a,1))),
    startPos, SEQUENCE(ROWS(numPos)+1),
    blocks, MAP(startPos,
        LAMBDA(n,
            LET(
                first, IF(n=1,2,INDEX(numPos,n-1)+1),
                last, IF(n>ROWS(numPos),ROWS(a),INDEX(numPos,n)-1),
                prefix, INDEX(f,first-1),
                texts, TEXTJOIN(" ",TRUE,INDEX(f,SEQUENCE(last-first+1,,first))),
                IF(prefix<>"",prefix & " " & texts,"")
            )
        )
    ),
    FILTER(blocks,blocks<>"")
)

Thanks
Jawahar Prem
 

Attachments

Back
Top