I've been using excel for many years for lots of different applications. However, a very simple 'problem' is stumping me. A colleague came to me asking how to find the longest 'name chain' in a dataset. Eg/ Daniel Craig -> Craig David -> David Bradley -> Bradley Cooper. It's more of a brain exercise than some mission-critical problem, but I jumped in, as I like a good puzzle. I started with first names in col A, last names in col B, and in col C something like '=xlookup(b2,A:A,C:C,-1)+1'. This worked, except it only found the first instance of the last name from col A, rather than the longest chain. I then moved to '=MAX(CHOOSECOLS(FILTER($A$2:$C$20,$A$2:$A$20=B2,{0,0,0}),3))+1' in col C, which is 90% there, as it looks for the largest chain, but runs into problems with circular references if you have Daniel Craig -> Craig David -> David Daniel -> Craig David -> ....
To breakdown the above formula, FILTER returns all the rows with a first name which matches the last name in question, CHOOSECOLS returns only the counts associated with these rows (which are the results of itself from other rows), MAX finds the largest, then +1 as it's the next step in the chain.
There either needs to be a way to adjust the above to return zero if it picks up the same name twice, or my next thought is recursive lambdas where you substitute the name you're referencing for "" and remove it from the lookup column for future use, but it fries my brain thinking how to structure that.
Can anyone think of a solution? I don't want to use macros.
Thanks in advance!
To breakdown the above formula, FILTER returns all the rows with a first name which matches the last name in question, CHOOSECOLS returns only the counts associated with these rows (which are the results of itself from other rows), MAX finds the largest, then +1 as it's the next step in the chain.
There either needs to be a way to adjust the above to return zero if it picks up the same name twice, or my next thought is recursive lambdas where you substitute the name you're referencing for "" and remove it from the lookup column for future use, but it fries my brain thinking how to structure that.
Can anyone think of a solution? I don't want to use macros.
Thanks in advance!
