SOLVED

# VLOOKUP or MATCH - Need Unique Returns on Multiple Finds

Copper Contributor

# 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 into one row for that particular workorder. The work orders are itemized by labor hours so they appear in column A multiple times. Some workorders have no extra ingredients.

Here is the data I have:

Column A              Column B

Workorder#           Extra Ingredients

123                        Seeds

456

789                        Apple

963

852                        Orange

741                        Orange

741                        Orange

963

123                        Seeds

123                        Sugar

852

456                        Sugar

456

456                        Sugar

789

741                         Apple

741                         Orange

741                         CSyrup

741                         Apple

What I'm looking for is a formula that puts these values (in another tab) like this:

Column A       Column B

Workorder      Extra(s)

123                 Seeds, Sugar

789                 Apple

852                 Orange

741                 Orange, Apple, CSyrup

456                 Sugar

Work orders without any extra ingredients need not be shown in this list. The list is substantial, where maybe there are 100 unique work orders, you'll find those work order number upward in the thousands.

Any help would be greatly appreciated.

5 Replies

# Re: VLOOKUP or MATCH - Need Unique Returns on Multiple Finds

An alternative could be Power Query. In the attached file you can add data into the blue dynamic table and then click in any cell of the green table and right-click with the mouse and select refresh in order to update the green result table.

best response confirmed by FrenchyFri27 (Copper Contributor)
Solution

# Re: VLOOKUP or MATCH - Need Unique Returns on Multiple Finds

``````'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) <> "")))

={"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:

# Re: VLOOKUP or MATCH - Need Unique Returns on Multiple Finds

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:

Analyze is asking for input a,v example: Analyze(a,v). I cannot replicated the below (which you have):

# Re: VLOOKUP or MATCH - Need Unique Returns on Multiple Finds

Nevermind I got it ! Thank you so much. Had a minor typo, found this after retyping manually.

# Re: VLOOKUP or MATCH - Need Unique Returns on Multiple Finds

1 best response

Accepted Solutions
best response confirmed by FrenchyFri27 (Copper Contributor)
Solution

# Re: VLOOKUP or MATCH - Need Unique Returns on Multiple Finds

``````'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) <> "")))

={"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: