merge cells with reference to other cells

noubaba

New Member
Joined
Apr 10, 2018
Messages
19
Hi,
I need help to merge fruits named cell with refence to other cells. Follows I explained with example what I want to do with formula or with few clicks. Actually I am extracting huge data from SAP and SAP giving me fruits names in different rows in front of each country name and codes, countries names could be duplicated but coded are unique, and I want to merge fruit names in one cell as I merge them manually in last cell. Can you please help me to develop a formula or trick for this task. Formula can read codes or read country name like USA and then merge next cells till blanks cells under USA or Brazil.
this what I want to do
1002323USAappleapple
mango
Guava
Litchi
mango
Guava
Litchi
10025454Brazilappleapple
Jackfruit
Orange
Banana
Jackfruit
Orange
Banana
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Source:
IDCountryItems
1002323USAapple
mango
Guava
Litchi
10025454Brazilapple
Jackfruit
Orange
Banana

Result:
IDCountryItems
1002323USAapple mango Guava Litchi
10025454Brazilapple Jackfruit Orange Banana

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    FillD = Table.FillDown(Source,{"ID", "Country"}),
    Group = Table.Group(FillD, {"ID", "Country"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Items", each List.Distinct([Count][Items])),
    Extract = Table.TransformColumns(List, {"Items", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text})
in
    Extract
 
Upvote 0
thank you Sandy, is this VBA code?, sorry I did not get it I copied in VBA but not working, can you please explain little bit. Thanks in advance
And really appreciate if you can help to develop formula for this task.
 
Upvote 0
To fully understand PQ, pick up a copy of the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. A really powerful step by step primer that will have you understanding and using PQ immediately to solve many of your very difficult manipulations and reordering of data to allow for useful analysis.
 
Upvote 0
VBA Code:
Option Explicit

Sub NoPQ()
    Dim i As Long, lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To lr Step 4
        Range("E" & i) = Range("C" & i) & Chr(10) & Range("C" & i + 1) & Chr(10) & Range("C" & i + 2) _
                       & Chr(10) & Range("C" & i + 3)
    Next i
End Sub
 
Upvote 0
Thank you sir for support, but follows are the result i got from given MACRO. Can you please help further, actually I am extracting huge data on daily basis, following given data is just as example.
IDCountryItemsItems
apple
mango
Guava
1002323USAapple
mango
Guava
LitchiLitchi
apple
Jackfruit
Orange
10025454Brazilapple
Jackfruit
Orange
BananaBanana
Banana
Banana
Jackfruit
1008655UKBanana
Banana
Jackfruit
mangomango
Orange
Orange
 

Attachments

  • results with given Macro.JPG
    results with given Macro.JPG
    117.3 KB · Views: 1
  • Result I want to get.JPG
    Result I want to get.JPG
    65.5 KB · Views: 1
Upvote 0
change this line of code

VBA Code:
For i = 1 To lr Step 4


to


VBA Code:
For i = 2 To lr Step 4

You had no headers in your original data. In the future please present an accurate picture of your data as we are not mind readers and use what you present as gospel.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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