Apr 30 2022 01:23 PM
Thank you for reading this post!
I propose a formulas challenge (The shorter the better). Attached is a workbook in need of two formulas. I have solutions for each (Formula 1 is 66 characters, Formula 2 is 72) but would like to compare notes with others and maybe learn some new things.
Formula 1: Rollup data from 5 sheets. Must be sorted A-Z, no blank rows, and must include header.
Formula 2: Find the j+ max for each agent. This sounds simple but you'll have to see the workbook. The arrangement is based off a client's workbook that crossed my desk several years ago.
The rules are simple. For this challenge the following are restricted: vba, PowerQuery, fill handle, use of named items, and alteration of existing data. Functions available on the Beta channel are fair game.
Note:
You may find this exercise difficult if you're not on 365 with Insider. The goal of this exercise is to explore some of the newest functions Excel has to offer. Have at it!
Apr 30 2022 01:33 PM
Apr 30 2022 02:04 PM
Apr 30 2022 03:40 PM
Apr 30 2022 04:04 PM
Please check the directions again.
Apr 30 2022 10:34 PM
@Patrick2788 Formula 2 (57 characters) could be:
=IFERROR(BYROW(B2:J16*(B1:J15="j+"),LAMBDA(a,MAX(a))),"")
May 01 2022 12:20 AM
@Patrick2788 A short formula isn't always best :))
For number 1 I could come up with this (at 72 char):
=VSTACK('1'!A1:B1,TAKE(SORT(VSTACK('1:5'!A2:B30)),COUNTA('1:5'!A2:A30)))
I feel that there must be a better way to exclude the blanks asThe COUNTA at the end looks a bit clumsy. And if you hadn't insisted on a formula generated header, the count would have come down to 54 chars.
May 01 2022 02:29 AM
May 01 2022 02:33 AM
I have attempted part 1 of your challenge but not within the rules. I only use defined names to reference data (at one time I ran macros to delete the content of any cell that contained a direct reference1).
My approach was to create an array of Thunks, each element containing the data from one of the tables.
Since submitting, I have had a look at @Riny_van_Eekelen 's solution and may have some reconsidering to do. I had not realised that VSTACK works over a 3D range reference! It does mean that the tables have to be aligned but often they are.
May 01 2022 06:16 AM
I'm re-attaching #2. After some thought, I recall the client's data was much "worse". It was arranged as such:
May 01 2022 07:00 AM
May 01 2022 11:42 AM
"VSTACK ... support 3D references. Very cool."
Yes, that was quite a discovery. Stay in touch over the results of further exploration. Although this forum is largely devoted to Q&A, I would like to see more discussion and sharing of experience.
You can see from my response that I am not fully committed to the shortest formula concept, including 44 spaces and 6 line feeds in a formula of length 123 definitely rates a 'could do better'! I have also used thunks in the Max problem as a device for getting 2 row arrays through MAP. The more obvious BYROW does not allow multiple parameters.
The final version of you Max problem is more comfortable for me because I am used to picking out arrays from multidimensional layouts.
As for your comment regarding VBA, last week on another forum, I wrote a formula which turned out to be needed because an add-in had ceased to work. The places where I would still need VBA include writing event handlers or using the object model to interrogate relationships between shape objects. Most calculation would appear to be better achieved through the use of worksheet formulae.
May 03 2022 10:07 AM
May 03 2022 03:33 PM
@Patrick2788 I am not happy with the array of array limitations but Thunks can be used to overcome the problem in many instances. The attached workbook contains a Lambda function
= Expandλ(arrayϑ)
which turns a column of row-thunks back into a 2D array. I would far prefer such tricks were not necessary but it is something I have been bitching about for years (along with array breakup that is now solved by Lambda helper functions).
Expandλ = LAMBDA(ϑ,
LET(
n₁, ROWS(ϑ),
n₂, MAX(MAP(ϑ,Columnsλ)),
MAKEARRAY(n₁,n₂,
LAMBDA(i₁,i₂,
INDEX(INDEX(ϑ,i₁,1)(),i₂)
)
)
)
);
Columnsλ = LAMBDA(xϑ,COLUMNS(xϑ()));
Thunkλ = LAMBDA(x, LAMBDA(x));
Microsoft Word - Will modern Excel finally change the way Models are built v2.0.docx (eusprig.org)
If it is any use to you, I could also offer Lambda functions that read multidimensional data from an arbitrary cross-tab dataset (I have only gone to 4D but there is no reason why it should not be applied to higher dimensional data were that relevant). The second Lambda function uses the first and generates a normalised dataset or will generate any of the normal pivot table crosstabs. It is also possible to aggregate a column by addition.