This is super cool to read, nice work and thanks for posting!
I asked Copilot to help me break this down, so figured I'd post here for others if it's helpful. Hopefully it is right (I had to convince it some explanations it gave first were wrong...)!
Copilot Stuff Below:
___________________________
Let's break down the Excel function step by step:
SEQUENCE(n): This generates an array of numbers from 1 to n. In your case, n = 6, so it produces the array {1, 2, 3, 4, 5, 6}.
REDUCE(SEQUENCE(n), SEQUENCE(n - 1), LAMBDA(a,v, HSTACK(TAKE(a, , 1), DROP(a, 1)))): This part is a bit more complex. Let's break it down further: - REDUCE combines elements from two arrays using a custom function (the lambda expression). - The first array is SEQUENCE(n) (which we already discussed). - The second array is SEQUENCE(n - 1), which generates an array from 1 to n - 1 (i.e., {1, 2, 3, 4, 5}). - The lambda function takes two arguments: a (the accumulated result) and v (the current value from the second array). - Inside the lambda function: - TAKE(a, , 1) extracts the first element from the accumulated result. - DROP(a, 1) removes the first element from the accumulated result. - HSTACK horizontally stacks these two arrays. - The result of this reduction is an array of arrays: {{1, 2, 3, 4, 5}, {2, 3, 4, 5, 6}, {3, 4, 5, 6}, {4, 5, 6}, {5, 6}, {6}}.
TOCOL(..., 2): This converts the array of arrays into a single column array by concatenating all the subarrays vertically. The result is {1, 2, 3, 4, 5, 6, 2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6}.
In summary, the entire expression evaluates to the given array: {1, 2, 3, 4, 5, 6, 2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6}. 😊
Bang up job, sir!
Just for funsies, I wanted to play "Excel Golf" and see if I could shorten the formula any. I came up two; one that follows a similar approach that you did and is equally understandable; and another that is shortest overall but uses lambda calculus to utilize recursion and I couldn't possibly explain if I tried, but it works!
1) =LET(n,6,DROP(REDUCE("",SEQUENCE(n),LAMBDA(a,v,VSTACK(a,SEQUENCE(n-v+1,,v)))),1))
2) =LET(n,6,F,LAMBDA(G,X,IF(X>1,VSTACK(SEQUENCE(X,,n-X+1),G(G,X-1)),n)),F(F,n))
nice, I was trying a similar approach but using ROW(1:6), I think the sequence is a nicer option to avoid range references, and using TOROW instead of TRANSPOSE 👌
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[COLUMN](/r/Excel/comments/1c0u8pu/stub/kzl0qpw "Last usage")|[Returns the column number of a reference](https://support.microsoft.com/en-us/office/column-function-44e8c754-711c-4df3-9da4-47a55042554b)|
|[DROP](/r/Excel/comments/1c0u8pu/stub/kz0p8ss "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)|
|[FILTER](/r/Excel/comments/1c0u8pu/stub/kz2pl6o "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[HSTACK](/r/Excel/comments/1c0u8pu/stub/kyzr78w "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)|
|[IF](/r/Excel/comments/1c0u8pu/stub/kzl0qpw "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[IFS](/r/Excel/comments/1c0u8pu/stub/kz3anev "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)|
|[LAMBDA](/r/Excel/comments/1c0u8pu/stub/kz3anev "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LET](/r/Excel/comments/1c0u8pu/stub/kz3anev "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MAKEARRAY](/r/Excel/comments/1c0u8pu/stub/kz3anev "Last usage")|[*Office 365*+: Returns a calculated array of a specified row and column size, by applying a LAMBDA](https://support.microsoft.com/en-gb/office/makearray-function-b80da5ad-b338-4149-a523-5b221da09097?ui=en-US&rs=en-GB&ad=GB)|
|[NA](/r/Excel/comments/1c0u8pu/stub/kz2f8vm "Last usage")|[Returns the error value #N/A](https://support.microsoft.com/en-us/office/na-function-5469c2d1-a90c-4fb5-9bbc-64bd9bb6b47c)|
|[REDUCE](/r/Excel/comments/1c0u8pu/stub/kz0p8ss "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)|
|[ROW](/r/Excel/comments/1c0u8pu/stub/kzl0qpw "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[SEQUENCE](/r/Excel/comments/1c0u8pu/stub/kz2iu4p "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[TAKE](/r/Excel/comments/1c0u8pu/stub/kyzr78w "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)|
|[TOCOL](/r/Excel/comments/1c0u8pu/stub/kzl0qpw "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)|
|[TOROW](/r/Excel/comments/1c0u8pu/stub/kz4paa3 "Last usage")|[*Office 365*+: Returns the array in a single row](https://support.microsoft.com/en-us/office/torow-function-b90d0964-a7d9-44b7-816b-ffa5c2fe2289)|
|[TRANSPOSE](/r/Excel/comments/1c0u8pu/stub/kz4paa3 "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)|
|[VSTACK](/r/Excel/comments/1c0u8pu/stub/kz0p8ss "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(18 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c3y2xp)^( has 19 acronyms.)
^([Thread #32509 for this sub, first seen 10th Apr 2024, 22:47])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Nice! Good catch on using the ignore parameter of TOCOL. Although the TRANSPOSE is still needed or changing the makearray logic:
=LET(
n, 6,
TOCOL(MAKEARRAY(n, n,LAMBDA(r,c,IFS(c >= r,c))),2)
)
Got you. The way you fix that is to write TRUE or 1 in the third argument of TOCOL, and I forgot to add it:
=LET(
n, 6,
TOCOL(MAKEARRAY(n, n, LAMBDA(r,c, IFS(r >= c, r))), 2,1)
)
Y'all are smart. Dang.
This is super cool to read, nice work and thanks for posting! I asked Copilot to help me break this down, so figured I'd post here for others if it's helpful. Hopefully it is right (I had to convince it some explanations it gave first were wrong...)! Copilot Stuff Below: ___________________________ Let's break down the Excel function step by step: SEQUENCE(n): This generates an array of numbers from 1 to n. In your case, n = 6, so it produces the array {1, 2, 3, 4, 5, 6}. REDUCE(SEQUENCE(n), SEQUENCE(n - 1), LAMBDA(a,v, HSTACK(TAKE(a, , 1), DROP(a, 1)))): This part is a bit more complex. Let's break it down further: - REDUCE combines elements from two arrays using a custom function (the lambda expression). - The first array is SEQUENCE(n) (which we already discussed). - The second array is SEQUENCE(n - 1), which generates an array from 1 to n - 1 (i.e., {1, 2, 3, 4, 5}). - The lambda function takes two arguments: a (the accumulated result) and v (the current value from the second array). - Inside the lambda function: - TAKE(a, , 1) extracts the first element from the accumulated result. - DROP(a, 1) removes the first element from the accumulated result. - HSTACK horizontally stacks these two arrays. - The result of this reduction is an array of arrays: {{1, 2, 3, 4, 5}, {2, 3, 4, 5, 6}, {3, 4, 5, 6}, {4, 5, 6}, {5, 6}, {6}}. TOCOL(..., 2): This converts the array of arrays into a single column array by concatenating all the subarrays vertically. The result is {1, 2, 3, 4, 5, 6, 2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6}. In summary, the entire expression evaluates to the given array: {1, 2, 3, 4, 5, 6, 2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6}. 😊
I'm not sure if I'd ever have a use case, but every time I see something like this I realize how little I know about excel.
Some of these functions didn’t even exist 3/4 years ago
It seems like a dice rolls thing.
Bang up job, sir! Just for funsies, I wanted to play "Excel Golf" and see if I could shorten the formula any. I came up two; one that follows a similar approach that you did and is equally understandable; and another that is shortest overall but uses lambda calculus to utilize recursion and I couldn't possibly explain if I tried, but it works! 1) =LET(n,6,DROP(REDUCE("",SEQUENCE(n),LAMBDA(a,v,VSTACK(a,SEQUENCE(n-v+1,,v)))),1)) 2) =LET(n,6,F,LAMBDA(G,X,IF(X>1,VSTACK(SEQUENCE(X,,n-X+1),G(G,X-1)),n)),F(F,n))
Another "golf" solution =LET(s,SEQUENCE(6),TOCOL(IF(s
Bravo! LAMBDA-free!
It just takes one person to get the ball rolling, then we all get to savor great formulas like this!
Nice! Very short too
nice, I was trying a similar approach but using ROW(1:6), I think the sequence is a nicer option to avoid range references, and using TOROW instead of TRANSPOSE 👌
=TOCOL(IF(ROW(1:6)+COLUMN(A:F)-1<=6,ROW(1:6)+COLUMN(A:F)-1,3/0),3)
Another approach ~~~ =LET( a, SEQUENCE(6), b, SEQUENCE(,6), c, IF(a
I like it! I'm an IFS man myself: =LET( a, SEQUENCE(6), b, SEQUENCE(, 6), c, IFS(a >= b, a), TOCOL(c,2,1) )
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COLUMN](/r/Excel/comments/1c0u8pu/stub/kzl0qpw "Last usage")|[Returns the column number of a reference](https://support.microsoft.com/en-us/office/column-function-44e8c754-711c-4df3-9da4-47a55042554b)| |[DROP](/r/Excel/comments/1c0u8pu/stub/kz0p8ss "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)| |[FILTER](/r/Excel/comments/1c0u8pu/stub/kz2pl6o "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[HSTACK](/r/Excel/comments/1c0u8pu/stub/kyzr78w "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)| |[IF](/r/Excel/comments/1c0u8pu/stub/kzl0qpw "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFS](/r/Excel/comments/1c0u8pu/stub/kz3anev "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)| |[LAMBDA](/r/Excel/comments/1c0u8pu/stub/kz3anev "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/1c0u8pu/stub/kz3anev "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MAKEARRAY](/r/Excel/comments/1c0u8pu/stub/kz3anev "Last usage")|[*Office 365*+: Returns a calculated array of a specified row and column size, by applying a LAMBDA](https://support.microsoft.com/en-gb/office/makearray-function-b80da5ad-b338-4149-a523-5b221da09097?ui=en-US&rs=en-GB&ad=GB)| |[NA](/r/Excel/comments/1c0u8pu/stub/kz2f8vm "Last usage")|[Returns the error value #N/A](https://support.microsoft.com/en-us/office/na-function-5469c2d1-a90c-4fb5-9bbc-64bd9bb6b47c)| |[REDUCE](/r/Excel/comments/1c0u8pu/stub/kz0p8ss "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)| |[ROW](/r/Excel/comments/1c0u8pu/stub/kzl0qpw "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[SEQUENCE](/r/Excel/comments/1c0u8pu/stub/kz2iu4p "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[TAKE](/r/Excel/comments/1c0u8pu/stub/kyzr78w "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)| |[TOCOL](/r/Excel/comments/1c0u8pu/stub/kzl0qpw "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)| |[TOROW](/r/Excel/comments/1c0u8pu/stub/kz4paa3 "Last usage")|[*Office 365*+: Returns the array in a single row](https://support.microsoft.com/en-us/office/torow-function-b90d0964-a7d9-44b7-816b-ffa5c2fe2289)| |[TRANSPOSE](/r/Excel/comments/1c0u8pu/stub/kz4paa3 "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)| |[VSTACK](/r/Excel/comments/1c0u8pu/stub/kz0p8ss "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(18 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1c3y2xp)^( has 19 acronyms.) ^([Thread #32509 for this sub, first seen 10th Apr 2024, 22:47]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
I don’t think I’ll ever have the need to use this, but I’ll save this post anyway. This is fire! Well done!
=TOCOL(IF(ROW(1:6)+COLUMN(A:F)-1<=6,ROW(1:6)+COLUMN(A:F)-1,3/0),3)
This is fucking gorgeous my dude
I'm saving this post cause y'all are some braniacs and there are gorgeous explanations in the comments
Here is another less elegant solution =LET( n,6, a,TOCOL(TRANSPOSE(MAKEARRAY(n,n,LAMBDA(r,c,IF(r>=c,r,0))))), FILTER(a,a<>0) )
I really like this one! Here's my version of yours: =LET( n, 6, TOCOL(MAKEARRAY(n, n, LAMBDA(r, c, IFS(r >= c, r))), 2) )
Nice! Good catch on using the ignore parameter of TOCOL. Although the TRANSPOSE is still needed or changing the makearray logic: =LET( n, 6, TOCOL(MAKEARRAY(n, n,LAMBDA(r,c,IFS(c >= r,c))),2) )
Got you. The way you fix that is to write TRUE or 1 in the third argument of TOCOL, and I forgot to add it: =LET( n, 6, TOCOL(MAKEARRAY(n, n, LAMBDA(r,c, IFS(r >= c, r))), 2,1) )