VLOOKUP values with merged cells

[Ranju78]

Hi, I am trying to get all the values from rows with VLOOKUP.
I have data which has seller name in merged cell and Product is in differrent rows.For Example: Adam seller has Bananas,Oranges And Lemons...

Seller	Product
Adam	Bananas
Oranges
Lemons
Robert	Apricotes
Lemons
Sally	Orange
Bananas
Apples
Tom	Apples
Apricots
Bananas

<tbody>
</tbody>

And I need output like :

Seller	Products
Sally	Orange,Banana,Apples
Robert	Aprocotes,Lemons
Adam	Banana,Oranges,Lemons
Tom	Apples,Apricotes,bananas

<tbody>
</tbody>

When I used VLOOKUP ,I am getting only first row value.
Please guide..

 

[leiarquel]

Try to list their products horizontally

 

[Ranju78]

Try to list their products horizontally

There is another related cell .So i can not list horizontally.There is no way I can get that output?

 

[leiarquel]

i dont think you can use the vlookup for the format of your data. your seller columns must be the seller names only and for products try to use a number of columns say product1, product2, product3 and so on.

 

[sandy666]

try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Product] <> null then [Seller] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Product] = null then [Seller] else null),
    #"Merged Columns" = Table.CombineColumns(#"Added Conditional Column1",{"Custom.1", "Product"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Product.1"),
    #"Filled Down" = Table.FillDown(#"Merged Columns",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Count],"Product.1")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Seller"}, {"Custom.1", "Product"}})
in
    #"Renamed Columns"[/SIZE]

Result
Seller	Product
Adam	Bananas,Oranges,Lemons
Robert	Apricotes,Lemons
Sally	Orange,Bananas,Apples
Tom	Apples,Apricots,Bananas

 

[Ranju78]

i dont think you can use the vlookup for the format of your data. your seller columns must be the seller names only and for products try to use a number of columns say product1, product2, product3 and so on.

The formatting came in weird way in my question.Let me make it clear again
There is merge rows in column A .For example row A1:A3 has saller name Adam and A4:A5 has Robert
-----------------------------------------------------------
| Column A | Column B |
-----------------------------------------------------------
Row 1 | Adam | Bananas |
Row 2 | | Oranges |
Row 3 | | Lemons |
Row 4 | Robert | Apricots |
Row 5 | | Lemons |

So on ......

Now in another sheet I need the output like :
-----------------------------------------------------------
| Column A | Column B |
-----------------------------------------------------------
Row 1 | Adam | Bananas,Oranges,Lemons
Row 2 | Robert | Apricots,Lemons

And so on....

I hope it make sense now....

 

[Ranju78]

The formatting comes in weird way in my question.
Let me make it clear .
So the data is like this :
seller Adam is in merged cell A1:A3 and in B1-Bananas, B2-Oranges,B3-Lemons
seller Robert is in merged cell A4:A5 and in B4-Apricots,B5-Lemons
seller Sally is in merged cell A6:A8 and in B6- Orange,B7-Bananas,B8-Apples
and so on..
So the output i want in another sheet is:

Seller	Product
Adam	Bananas,Oranges,Lemons
Robert	Apricotes,Lemons
Sally	Orange,Bananas,Apples
Tom	Apples,Apricots,Bananas

<tbody>
</tbody>

 

[sandy666]

Seller	Product		Seller	Product
Adam	Bananas		Adam	Bananas,Oranges,Lemons
	Oranges		Robert	Apricotes,Lemons
	Lemons		Sally	Orange,Bananas,Apples
Robert	Apricotes		Tom	Apples,Apricots,Bananas
	Lemons
Sally	Orange
	Bananas
	Apples
Tom	Apples
	Apricots
	Bananas

[SIZE=1]let
    Source = Excel.Workbook(File.Contents("[I][COLOR="#FF0000"]path to the file with source range[/COLOR][/I]"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Seller", type text}, {"Product", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Seller"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Seller"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each Table.Column([Count],"Product")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Product", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"})
in
    #"Removed Columns"[/SIZE]

yellow / orange cells are merged

btw. table tool doesn't reflect merged cells in the post that is why there are colors

 

[Ranju78]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Seller[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Seller[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product[/COLOR]
Adam	Bananas		Adam	Bananas,Oranges,Lemons
	Oranges		Robert	Apricotes,Lemons
	Lemons		Sally	Orange,Bananas,Apples
Robert	Apricotes		Tom	Apples,Apricots,Bananas
	Lemons
Sally	Orange
	Bananas
	Apples
Tom	Apples
	Apricots
	Bananas

<tbody>
</tbody>

[SIZE=1]let
    Source = Excel.Workbook(File.Contents("[I][COLOR=#FF0000]path to the file with source range[/COLOR][/I]"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Seller", type text}, {"Product", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Seller"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Seller"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each Table.Column([Count],"Product")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Product", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"})
in
    #"Removed Columns"[/SIZE]

yellow / orange cells are merged

btw. table tool doesn't reflect merged cells in the post that is why there are colors

Thank you so much. I will try the code and let you know..