Forum Discussion
Solved
VLOOKUP or MATCH - Need Unique Returns on Multiple Finds
Hello All, Need help on this one. Having issues. I'm trying to summarize a huge list of data. In this huge list of data I need to extract (summarize) extra ingredients used by work order number i...
If you have access to REDUCE, this may work for you:
'Dynamic' a dynamic range for your data in columns A and B =LET(arr, Sheet1!$A$2:$B$10000, FILTER(arr, (TAKE(arr, , 1) <> "") * (TAKE(arr, , -1) <> ""))) 'Header' - an array constant ={"WO#", "Extra(s)"} 'Analyze' Lambda - to be called within REDUCE. Filters each unique Work Order, obtains the unique ingredients, strings them together, and stacks. =LAMBDA(a,v,LET( WO, TAKE(Dynamic, , 1), ingredients, TAKE(Dynamic, , -1), filtered, TEXTJOIN(", ", 1, UNIQUE(FILTER(ingredients, WO = v))), VSTACK(a, HSTACK(v, filtered)) )) Sheet level formula: =REDUCE(Header,UNIQUE(TAKE(Dynamic,,1)),Analyze)
If you have access to REDUCE, this may work for you:
'Dynamic' a dynamic range for your data in columns A and B
=LET(arr, Sheet1!$A$2:$B$10000, FILTER(arr, (TAKE(arr, , 1) <> "") * (TAKE(arr, , -1) <> "")))
'Header' - an array constant
={"WO#", "Extra(s)"}
'Analyze' Lambda - to be called within REDUCE. Filters each unique Work Order, obtains the unique ingredients, strings them together, and stacks.
=LAMBDA(a,v,LET(
WO, TAKE(Dynamic, , 1),
ingredients, TAKE(Dynamic, , -1),
filtered, TEXTJOIN(", ", 1, UNIQUE(FILTER(ingredients, WO = v))),
VSTACK(a, HSTACK(v, filtered))
))
Sheet level formula:
=REDUCE(Header,UNIQUE(TAKE(Dynamic,,1)),Analyze)
- Thank you Patrick. I think I'm almost there, when I try to replicate the actual sheet-level formula it doesn't mirror what you have. I have the following appear:
=REDUCE(Header,UNIQUE(Take(Dynamic,,1)),Analyze(
Analyze is asking for input a,v example: Analyze(a,v). I cannot replicated the below (which you have):
=REDUCE(Header,UNIQUE(TAKE(Dynamic,,1)),Analyze)- Nevermind I got it ! Thank you so much. Had a minor typo, found this after retyping manually.
- Excellent! Glad it's working.
