merge cells with reference to other cells

noubaba

New Member
Joined
Apr 10, 2018
Messages
17
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,455
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
 

noubaba

New Member
Joined
Apr 10, 2018
Messages
17
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.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,613
Office Version
2019
Platform
Windows
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.
 

noubaba

New Member
Joined
Apr 10, 2018
Messages
17
Is there any solution to do this task by VBA or Formula? Expert please help
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,613
Office Version
2019
Platform
Windows
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
 

noubaba

New Member
Joined
Apr 10, 2018
Messages
17
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

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,613
Office Version
2019
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,037
Messages
5,472,112
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top