• 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.

Which months?

ufuk_anzelha

New Member
I found which months are full with the formula below. I want to do this topic as a different table but the order is different. How can I do it?

=TEXTJOIN(" - ";TRUE;FILTER((B3:E3>0)*(SEQUENCE(;COUNTA($B$2:$E$2)));(B3:E3>0)*(SEQUENCE(;COUNTA($B$2:$E$2)))>0))

1722695141777.png
 

Attachments

  • example.xlsx
    10.1 KB · Views: 5
In cell B10, an adaptation of your formula in cell G3. Copy this down.
Also, a slightly shorter single-cell formula in cell C10 which spills down:
Code:
=BYROW(A10:A12,LAMBDA(newsource,LET(source,A3:E5,TEXTJOIN(" - ",TRUE,IF(CHOOSEROWS(DROP(source,,1),XMATCH(newsource,TAKE(source,,1)))>0,SEQUENCE(,COLUMNS(source)-1),"")))))
Finally, in cell D10, the formula in cell C10 converted to a named lambda formula/function called FullMonths (see it in Name Manager).

1722722332451.png
 

Attachments

  • Chandoo57487example.xlsx
    10.8 KB · Views: 3
Last edited:
Hi again,
The short formula works in this file. But I couldn't understand what source and newsource are. Did you define them anywhere?

1723126544023.png
 
For the named lambda formula FullMonths, if you look at my pic in msg#2 here, you'll see that Source is the blue highlighted range A3:E5 which includes the leftmost column A being the a,b,c values. It's assumed that the leftmost column of Source will be what you're going to be matching in NewSource, being A10:A12 (b,c,a) in you original file.

In the long formula, it's very similar,
the BYROW function passes the range A10:A12 to the variable newsource. BYROW passes A10:A12 one row at a time to the lambda function immediately following it.
source is defined in the first part of the LET function: LET(source;A3:E5… which is saying "LET source = A3:A5"

Nothing is defined elsewhere.

If you want to copy the named lambda function FullMonths to your own workbook, an easy way is to copy my sheet to your workbook then immediately remove it again. The function should remain in your workbook.
 
I found which months are full with the formula below. I want to do this topic as a different table but the order is different. How can I do it?

=TEXTJOIN(" - ";TRUE;FILTER((B3:E3>0)*(SEQUENCE(;COUNTA($B$2:$E$2)));(B3:E3>0)*(SEQUENCE(;COUNTA($B$2:$E$2)))>0))

View attachment 87764
Code:
=LET(
    monthNumber, SEQUENCE(, 4),
    months, BYROW(values, LAMBDA(x, TEXTJOIN(" - ", , FILTER(monthNumber, x)))),
    XLOOKUP({"b"; "c"; "a"}, header, months)
)
You won't like the defined names but then, I haven't used a direct cell reference for 10 years now and have happily forgotten they exist :)
1723328978889.png
 
Back
Top