Recursive lambda

JeteMc answered the original Q. GregB11 made the first O365-type formula:

https://www.excelforum.com/excel-for...ml#post5919354



I took it to the next stage. However, as I said in the thread, my brain only works in straight lines. I can't do recursive lambdas. The original post referred to thousands of staff memebrs and 15 reporting levels. Can anyone out there see how to calculate the number of direct and indirect reportees under each staff member with a recursive lambda, or similar, thus avoiding the need for 15 of these????

Level2,FILTER(a,ISNUMBER(MATCH(b,Level1,0)),"")

The formula looks for the number of indirect calls, 1 level further. (Indirect, indirect calls are not counted).
For that reason, a regular iterative formula can also be found that calculates this.

This formula (available in O365 V2401 or newer) returns the same results, but sorted by manager name:

Please Login or Register  to view this content.

Oops, without realizing it I changed Glenn's formula just a little bit, so indirect indirect calls were no longer counted.
I'm sorry Glenn.

Glenn's original formula also counts indirect indirect calls.

Therefore, ignore my previous post.

I'll think about a recursive lambda tomorrow.

I haven't been able to come up with a recursive lambda for this problem.

I did come up with another iterative solution, which can very easily be adjusted to the maximum number of levels.

This example assumes a maximum of 6 levels.
With more or fewer levels, only the red part of the formula needs to be adjusted.

Please define in the Name Manager ic:

Formula:

Please Login or Register  to view this content.

and try this formula:

Please Login or Register  to view this content.

This formula uses GROUPBY (available in O365 V2401 or newer)

Hahaha. Lovely. And wayyy beyond my pay grade. I just don't follow what ic is doing. I DO only think in straight lines.

Of course, I can't resist the temptation to tweak the bit I do understand to keep everyone in hierarchical order:

Formula:

Please Login or Register  to view this content.

I think it is 2402

I don't know what this will be like with a large dataset and many more levels, but seems ok with the sample. I'm pretty sure it could be tidier though!

Lambda named CountSubs:

Formula:

Please Login or Register  to view this content.

The output formula then becomes:

Formula:

Please Login or Register  to view this content.

Sample attached.

Hi everyone!

Check this formula with recursion. Blessings!

Originally Posted by rorya

Lambda named CountSubs:

Formula:

Please Login or Register  to view this content.

Nice real recursive solution Rory. I will study your solution.

Originally Posted by John Vergara

Check this formula with recursion.

I've checked your formula John, but I don't see recursion.

Thank you Hans - appreciate that!

The LAMBDA defined for f in John's formula appears to be recursive?

Originally Posted by Glenn Kennedy

what ic is doing?

Thanks for the feedback and rep. Glenn. .


From a list of people, ic looks for other people who have called one of them and adds them to the list.
Ic is called the first time with the manager for which you want to know the number of indirect calls.
And after the last call, ic returns everyone who called the manager directly or indirectly.

Originally Posted by HansDouwe

I've checked your formula John, but I don't see recursion.

Yes Hans, the formula is recursive, only it does not need name generation (inside the formula it calls itself).

Originally Posted by rorya

The LAMBDA defined for f in John's formula appears to be recursive?

Yes rorya, it is.

With Rorya's excellent proposal, I have designed a new recursive function, removing unnecessary variables within it. Check the attachment, blessings!

Originally Posted by John Vergara

Inside the formula it calls itself.

Thank you John.
I didn't know that was possible outside the name manager.
I keep learning things about Excel here.
It makes sense for me to study this carefully.

When you've studied it, can you teach the rest of us?

Always things to learn - every day is a lesson.

Mynda has a write up of that 'inline' lambda approach here: https://www.myonlinetraininghub.com/...mbda-functions

It's definitely an easier way to develop and debug than the name manager!

Thanks, Rory.