VLOOKUP or MATCH - Need Unique Returns on Multiple Finds

[FrenchyFri27]

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. 

[#2]

@FrenchyFri27 

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.

[#3 · Accepted solution]

@FrenchyFri27 

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)

[#4]

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)

[#5]

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

[#6]

Excellent! Glad it's working.