VLOOKUP values with merged cells

R

Ranju78

New Member
Joined
Apr 27, 2018
Messages
15
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...
[TABLE="width: 216"]
<tbody>[TR]
[TD]Seller[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]Apricotes[/TD]
[/TR]
[TR]
[TD]Lemons[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Apricots[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[/TR]
</tbody>[/TABLE]

And I need output like :
[TABLE="width: 274"]
<tbody>[TR]
[TD]Seller[/TD]
[TD]Products[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]Orange,Banana,Apples[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]Aprocotes,Lemons[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]Banana,Oranges,Lemons[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Apples,Apricotes,bananas[/TD]
[/TR]
</tbody>[/TABLE]

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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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.
 
Upvote 0
try PowerQuery

Code: 
[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Result[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Seller[/td][td=bgcolor:#70AD47]Product[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Adam[/td][td=bgcolor:#E2EFDA]Bananas,Oranges,Lemons[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Robert[/td][td]Apricotes,Lemons[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Sally[/td][td=bgcolor:#E2EFDA]Orange,Bananas,Apples[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Tom[/td][td]Apples,Apricots,Bananas[/td][/tr]
[/table]
 
Upvote 0
leiarquel said:
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.
Click to expand...
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....
 
Last edited:
Upvote 0
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:
[TABLE="class: cms_table_head"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #70AD47"]Seller[/TD]
[TD="bgcolor: #70AD47"]Product[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]Adam[/TD]
[TD="bgcolor: #E2EFDA"]Bananas,Oranges,Lemons[/TD]
[/TR]
[TR]
[TD]Robert[/TD]
[TD]Apricotes,Lemons[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]Sally[/TD]
[TD="bgcolor: #E2EFDA"]Orange,Bananas,Apples[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Apples,Apricots,Bananas[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Seller[/td][td=bgcolor:#5B9BD5]Product[/td][td][/td][td=bgcolor:#70AD47]Seller[/td][td=bgcolor:#70AD47]Product[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Adam[/td][td=bgcolor:#DDEBF7]Bananas[/td][td][/td][td=bgcolor:#E2EFDA]Adam[/td][td=bgcolor:#E2EFDA]Bananas,Oranges,Lemons[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00][/td][td]Oranges[/td][td][/td][td]Robert[/td][td]Apricotes,Lemons[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00][/td][td=bgcolor:#DDEBF7]Lemons[/td][td][/td][td=bgcolor:#E2EFDA]Sally[/td][td=bgcolor:#E2EFDA]Orange,Bananas,Apples[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFC000]Robert[/td][td]Apricotes[/td][td][/td][td]Tom[/td][td]Apples,Apricots,Bananas[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFC000][/td][td=bgcolor:#DDEBF7]Lemons[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]Sally[/td][td]Orange[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00][/td][td=bgcolor:#DDEBF7]Bananas[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00][/td][td]Apples[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFC000]Tom[/td][td=bgcolor:#DDEBF7]Apples[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFC000][/td][td]Apricots[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFC000][/td][td=bgcolor:#DDEBF7]Bananas[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[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
 
Last edited:
Upvote 0
sandy666 said:
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Seller[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Seller[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Product[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFFF00"]Adam[/TD]
[TD="bgcolor: #DDEBF7"]Bananas[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Adam[/TD]
[TD="bgcolor: #E2EFDA"]Bananas,Oranges,Lemons[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFFF00"][/TD]
[TD]Oranges[/TD]
[TD][/TD]
[TD]Robert[/TD]
[TD]Apricotes,Lemons[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #DDEBF7"]Lemons[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Sally[/TD]
[TD="bgcolor: #E2EFDA"]Orange,Bananas,Apples[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFC000"]Robert[/TD]
[TD]Apricotes[/TD]
[TD][/TD]
[TD]Tom[/TD]
[TD]Apples,Apricots,Bananas[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFC000"][/TD]
[TD="bgcolor: #DDEBF7"]Lemons[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFFF00"]Sally[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #DDEBF7"]Bananas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFFF00"][/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFC000"]Tom[/TD]
[TD="bgcolor: #DDEBF7"]Apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFC000"][/TD]
[TD]Apricots[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #FFC000"][/TD]
[TD="bgcolor: #DDEBF7"]Bananas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Code: 
[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
Click to expand...


Thank you so much. I will try the code and let you know..
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
3K
Kevin_Li
Kevin_Li
N
  • Question Question
Find cell value from Row & Column match
2
Replies
10
Views
963
proficient
proficient
C
  • Solved
Vlookup with multiple option
Replies
3
Views
487
DanteAmor
DanteAmor
P
  • Question Question
Code used for comparison between 2 list.
Replies
3
Views
634
Tupe77
T
C
  • Solved
Having the table array in vlookup change to a different tab for each cell
Replies
4
Views
787
Automatrix
A

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top