How to match and merge lookup results in Excel?

Question

I'm a total newbie with Excel and I'm stuck at doing this. I'm guessing there must be some work around "lookup" functions but I don't know which one and how to start.

Let's say I have a first sheet with 2 columns:

ItemA	ItemB
plane	apple
bus	banana
bus	strawberry
car	pear

As you can see, the first column does not contain unique values ("bus" appears twice)

I have a second sheet with two columns:

ItemA	ItemB
plane
bus
car
car
plane(carriage return)car

Here again, the first column can have duplicates (here "car") but also a single cell can contain several values separated by the CR (carriage return) character.

What I'd like is to find a function that will take all values of (ItemB) from the first sheet and copy them into the ItemB column of the second one.

So the "bus" item will have "banana(carriage return)strawberry", the two "car" will have "pear" filled up and "plane(carriage return)car" will have "apple(carriage return)pear" value in column ItemB.

Second sheet will be:

ItemA	ItemB
plane	apple
bus	banana(carriage return)strawberry
car	pear
car	pear
plane(carriage return)car	apple(carriage return)pear

Is it possible to do such thing?

Thank you !

Answer 1

The Data tab contains all the tools needed to accomplish this task. First, convert the original data into tables. Name the first table "SourceTable" and the second "TargetList". Now create a new request and replace its code with the following

let
    ListByRef = (celltext, table) =>
        let
            MakeTable = Table.FromList(Lines.FromText(celltext), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            MergeValues = Table.NestedJoin(MakeTable,{"Column1"},table,{"ItemA"},"NewColumn",JoinKind.Inner),
            Extract = Table.ExpandTableColumn(MergeValues, "NewColumn", {"ItemB"}, {"ItemB"}),
            ClearTable = Table.SelectColumns(Extract,{"ItemB"}),
            SingleCell = Lines.ToText(Table.Column(ClearTable , "ItemB"))
        in
            SingleCell, 
    Source = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
    Target = Excel.CurrentWorkbook(){[Name="TargetList"]}[Content],
    SingleColumn = Table.SelectColumns(Target,{"ItemA"}),
    Result = Table.AddColumn(SingleColumn, "ItemB", each ListByRef ([ItemA],Source))
in
    #"Result"

(For more information about using PowerQuery for various transformations of tabular data, see the training videos on YouTube)