Apr 23 2023 10:41 AM
Hi everybody,
I would appreciate your help in order to build an Iterative and generic Lambda with indirect to replace basic Nested If formula
Inputs :
One CLPC_Status Excel table with Tab_Name and Tab_Value headers. Ex :
Tab_Name Tab_Value
Tab_CLPC_415 4.1.5
Tab_CLPC_413 4.1.3
Many Tab_CLPC_ijk Excel tables (whose names are contained in CLPC_Status[Tab_Name]). Each of these tables have one [Country] header and Product names such as [ProdA], [ProdB], headers. On each cell of those tables (for every given country and given product name, there is either:
2
0
or NA
Goal :
I need to build a table with the same structure as my excel tables above, but which calculates each cell as follow: It scans all Tab_CLPC_ijk looking for a 2 value in a given order. For example, if 2 was found on the 2nd table Tab_CLPC_413 (and not on the first one Tab_CLPC_415) it should returns a string (e.g 4.1.3) and stops. If no 2 value was found it should return an error value NA()
Here below is the way I did realize it until now
=IF(XLOOKUP([@COUNTRY],Tab_CLPC_415[COUNTRY],Tab_CLPC_415[ProdA],,0,1)=2,"4.1.5",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_413[COUNTRY],Tab_CLPC_413[ProdA],,0,1)=2,"4.1.3",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_405[COUNTRY],Tab_CLPC_405[ProdA],,0,1)=2,"4.0.5",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_403[COUNTRY],Tab_CLPC_403[ProdA],,0,1)=2,"4.0.3",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_326[COUNTRY],Tab_CLPC_326[ProdA],,0,1)=2,"3.2.6",
IF(XLOOKUP([@COUNTRY],Tab_CLPC_325[COUNTRY],Tab_CLPC_325[ProdA],,0,1)=2,"3.2.5",NA()))))
This works, BUT if a new Tab_CLPC_ijk comes, this needs to edit again these formulas which are not so easy to read and maintain
Thus the idea to use the CLPC_Status Excel table to get the table names from column CLPC_Status[Tab_Name]. And the return value from CLPC_Status[Tab_Value]. It looks a good candidate for an (iterative ?) LAMBDA function, using INDIRECT to address my Tab_CLPC_ijk tables but I did fail until now to make it work
Of course a VBA module could achieve this as well, but I dont like the fact to change the standard Workbook into a Workbook with macro
Thank you for your advises
Apr 23 2023 10:52 AM
It would help us all help you if you could post a copy of your workbook on OneDrive or GoogleDrive, with a link pasted here that grants access.
In the absence of that, you're asking (in effect) that we create a formula in a vacuum OR create what we think your workbook looks like and then create that formula, in every case running the risk of not at all replicating your situation.
Apr 23 2023 02:41 PM - edited Apr 23 2023 11:27 PM
The main challenge is that of combining your tables into one (it could be by replicating the Tables using PQ or building the table in memory using Lambda functions). To combine the tables, I have converted them to an array of Thunks, which I then expand using REDUCE. It was not obvious to me whether your tables have to same headers as one another or, even better, could be regarded as a 3D range.
For the conversion, I defined a Lambda function Thunkλ defined by
Thunkλ
= LAMBDA(x, LAMBDA(x))
I then defined named Thunks to hold the tables and stacked them to form a named array.
Tab_CLPC_415ϑ
= Thunkλ(EXPAND(Tab_CLPC_415, , 4, "4.1.5"))
Tab_CLPC_413ϑ
= Thunkλ(EXPAND(Tab_CLPC_413, , 4, "4.1.3"))
Note: I added a column to hold the Table value. A stacked table could then be returned using
Tab_Arrayϑ
= VSTACK(Tab_CLPC_415ϑ, Tab_CLPC_413ϑ)
= REDUCE(HSTACK(headers,"Value"), Tab_Arrayϑ,
LAMBDA(stack,Tabϑ,
VSTACK(stack, Tabϑ())
)
)
but I chose to continue with the "ProdA" lookups, so used
= LET(
Tab_Stacked, REDUCE(HSTACK(headers,"Value"), Tab_Arrayϑ,
LAMBDA(stack,Tabϑ,
VSTACK(stack, Tabϑ())
)
),
Tab_Body, DROP(Tab_Stacked, 1),
Country_Stacked, CHOOSECOLS(Tab_Body, 1),
ProdA_Stacked, CHOOSECOLS(Tab_Body, 2),
Value_Stacked, CHOOSECOLS(Tab_Body, 4),
XLOOKUP(Country, IF(ProdA_Stacked=2, Country_Stacked), Value_Stacked)
)
By now, you are probably wishing you hadn't asked the question and I cannot be sure I have answered it since there is guesswork involved in setting up the problem!
Apr 23 2023 09:25 PM
@morphil
If your could provide us a sample file without sensitive data. It will be much more helpful to solve your concern. Like Mr. @Peter Bartholomew here is my guess approach and suggestion to your work.
=LAMBDA(country,
LET(
tableNames, CLPC_Status[Tab_Name],
tableValues, CLPC_Status[Tab_Value],
numTables, ROWS(tableNames),
result, NA(),
i, 1,
found, FALSE(),
WHILE(i <= numTables,
tableName, INDEX(tableNames, i),
tableValue, INDEX(tableValues, i),
tableRef, INDIRECT(tableName),
productCol, INDEX(tableRef, 0, MATCH(country, INDEX(tableRef, 1, 0), 0)),
hasTwo, IFERROR(MATCH(2, productCol, 0), 0),
IF(hasTwo > 0,
result = tableValue,
i = i + 1
),
found = (hasTwo > 0)
),
IF(found, result, NA())
)
)
This function takes a country as input and returns the CLPC value corresponding to the first table that contains a 2 in the column for that country, or NA if no such table is found.
Here's how the function works:
To use this function, simply enter it into a cell and provide a country as input. You can then copy the formula to other cells to calculate the CLPC value for other countries.
Note that the function assumes that all tables have the same structure, with the country names in the first column and the product names in the first row. If this is not the case, you may need to modify the function accordingly.
Apr 24 2023 08:19 AM
Apr 24 2023 09:19 AM - edited Apr 24 2023 10:41 AM
That link doesn't work for non-subscribers to the service. Can you post on GoogleDrive or OneDrive?
Apr 24 2023 09:52 AM - edited Apr 24 2023 07:33 PM
Hi @morphil
Until the LAMBDA experts suggest something more efficient...
With an Helper sheet to hide + a couple of defined names => in attached file
Apr 24 2023 02:25 PM
I combined your workbook and my named formulae
and things still seem to work. If I were developing such solutions I would probably write further Lambda functions to break the formula into smaller parts and to encapsulate the detail that need not be exposed to an end user.
Apr 24 2023 02:30 PM
Clever!
If I understand properly you first merge all Tab_CLPC_ijk in the HiddenArray with an added column containing the return values i.j.k.
Then you filter this HiddenArray for every given country and product and look for the first '2' value to return the first i.j.k
Many thanks
Apr 24 2023 07:40 PM
You got it (I wouldn't use the word 'merge' though)
Note that I updated the HiddenHeader defined name in the meantime => Please re-download the Example workbook from my previous post
Apr 26 2023 06:42 AM
Hello. Although your solution works fine, it did slow down dramatically the workbook. In my case, I have 204 countries and 15 products thus 3060 cells to be evaluated in the Result Workshet.
I hope that the #HiddenArray is evaluated only once as it is common to all formulas in the 3060 cells but I'm not sure ...
Apr 26 2023 08:36 AM
If you are able to implement the methods I provided, I would be interested to see the results regarding performance. 3000 cells is not a large number and should not cause problems unless you are performing expensive tasks involving the 3000 cells 3000 times (once for each cell).
Apr 26 2023 08:54 AM
Hello @morphil
Like I said I'm not a LAMBDA master + you asked for something generic
By the latter I understood this should work with 3 or 10 or 8 Products, hence the many XLOOKUP to return the appropriate arrays. If finally the number of Products is known/static there's probably something to do to improve the performances
Another option that I quickly experimented with > 200 countries & 15 products is Power Query. Let me know if you're interested
Apr 26 2023 04:19 PM
Hello @L z.
I'm curious about your PQ solution. Does it deal with generating the HiddenArray only or also the Result table as well ?
With regards
Apr 26 2023 04:22 PM
I will try your suggestion, but as far as I could see, it was quite similar to l.z 's one
With regards
Apr 26 2023 10:29 PM
Hello @morphil
I'm curious about your PQ solution. Does it deal with generating the HiddenArray only or also the Result table as well ?
No hidden array nor anything similar and Yes the output is a Table. I'll share it later...
Apr 26 2023 10:38 PM
SolutionPower Query option attached
Note that the 1st Refresh within an Excel session takes more time (time for the .Net Framework to load) that the next Refresh(es)
Apr 27 2023 06:34 AM
Hello @L z.
Congratulations ! Your PQ solution is just amazing. The only drawback is that the query needs to be refreshed when a user make any modifications in the Tab_CLPC_ijk tables, but the refresh is almost instantaneaous
My complete goal is however slightly more complex. The complete equation I need has one more line (1) that acts as a global enable for the 3 following lines which you kindly realized through a PQ
I will try to modify your query in ordere to add that extra condition, and may be if I can't you will be kind enough to help me again
With Best Regards
Apr 27 2023 09:12 AM
Hello @morphil
You're finally far from an iterative lambda ;-)). Anyway, if you need help with Power Query I suggest you open a separate thread/discussion (ref. that one for background if you think this is important)
Best..
Apr 26 2023 10:38 PM
SolutionPower Query option attached
Note that the 1st Refresh within an Excel session takes more time (time for the .Net Framework to load) that the next Refresh(es)