• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenating cells in the same column

Shay A

Member
Hi,
I in the attached file I have a column of stores id and a column of related items belonging to each store. I would like to create a loop which will go through each cell in the id column and will concatenate to items if both items belong the same store.

upload_2018-12-3_17-58-13.png


Thank you!
 

Attachments

Hui

Excel Ninja
Staff member
Shay

I would use a user defined Function Concatif()

To use in C2: =ConcatIf($B$2:$B$10,$A$2:$A$10,A2," & ")
Copy C2 down


upload_2018-12-4_11-59-18.png

Copy the following code into a Code Module in VBA

Code:
Function ConcatIf(Src As Range, ChkRng As Range, myVal As Variant, Optional Sep As String) As String
Dim c As Range
Dim retVal As String
Dim i As Integer

retVal = ""
i = 1

For Each c In ChkRng
  If c = myVal Then
  retVal = retVal + Src(i) + Sep
  End If
  i = i + 1
Next

ConcatIf = Left(retVal, Len(retVal) - Len(Sep))
End Function
You can read more about Concatif in the comments of this post
https://chandoo.org/wp/how-to-add-a-range-of-cells-in-excel-concat/
 

Nebu

Excel Ninja
Hi:

This is easy to do with a power query, find the attached. The result is in green table.
Code:
let
    Source = Excel.Workbook(File.Contents("C:\Users\nebusud\Downloads\Concat.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Store ID", Int64.Type}, {"Equipment", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Store ID])&"-" &[Equipment]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Equipment]), "Equipment", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Equipment", each Text.Trim(Text.AfterDelimiter([Cash Register],"-")&"&"&Text.AfterDelimiter([CCTV],"-"),"&")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Cash Register", "CCTV"})
in
    #"Removed Columns"
Thanks
 

Attachments

Shay A

Member
Thank you all, I will see what's easier for me to use.
BTW- Is it possible to use a UDF in a personal macro workbook?
 

Shay A

Member
Hi:

This is easy to do with a power query, find the attached. The result is in green table.
Code:
let
    Source = Excel.Workbook(File.Contents("C:\Users\nebusud\Downloads\Concat.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Store ID", Int64.Type}, {"Equipment", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Store ID])&"-" &[Equipment]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Equipment]), "Equipment", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Equipment", each Text.Trim(Text.AfterDelimiter([Cash Register],"-")&"&"&Text.AfterDelimiter([CCTV],"-"),"&")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Cash Register", "CCTV"})
in
    #"Removed Columns"
Thanks
Hi,
So I would need to copy this M code at the end of my current M code? Assuming I already have s file with a Power Query code on it? I would also need to tweak it so it will fit my needs!

TY
 

Nebu

Excel Ninja
Hi:

No, the M code I have given is from the start, if you already have codes that connect to source you do not need the lines to connect the source again. Open the excel file I have uploaded and go through the logic I have applied. You may have to look at the following lines, the logic are in these 4 lines.

Code:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Store ID])&"-" &[Equipment]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Equipment]), "Equipment", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Equipment", each Text.Trim(Text.AfterDelimiter([Cash Register],"-")&"&"&Text.AfterDelimiter([CCTV],"-"),"&")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Cash Register", "CCTV"})
in
    #"Removed Columns"
Thanks
 

Shay A

Member
Hi:

No, the M code I have given is from the start, if you already have codes that connect to source you do not need the lines to connect the source again. Open the excel file I have uploaded and go through the logic I have applied. You may have to look at the following lines, the logic are in these 4 lines.

Code:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Store ID])&"-" &[Equipment]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Equipment]), "Equipment", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Equipment", each Text.Trim(Text.AfterDelimiter([Cash Register],"-")&"&"&Text.AfterDelimiter([CCTV],"-"),"&")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Cash Register", "CCTV"})
in
    #"Removed Columns"
Thanks

Hi Again,
I have tried adding another item but that is not reflected in the output...
 

Attachments

Nebu

Excel Ninja
Hi:

Find the M Code for including more categories
Code:
let
    Source = Excel.Workbook(File.Contents("C:\Users\nebusud\Downloads\Concat (3).xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Store ID", Int64.Type}, {"Equipment", type text}}),
    #"Unique ID" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Store ID])&"-" &[Equipment]),
    #"Pivoted Column" = Table.Pivot(#"Unique ID", List.Distinct(#"Unique ID"[Equipment]), "Equipment", "Custom"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"Cash Register", "CCTV", "Table"},Combiner.CombineTextByDelimiter("&", QuoteStyle.None),"Merged"),
    #"Equipment" = Table.AddColumn(#"Merged Columns", "Equipment", each Text.Trim(Text.Remove([Merged],{"0".."9","-"}),"&")),
    #"Removed Columns" = Table.RemoveColumns(Equipment,{"Merged"})
in
    #"Removed Columns"
Thanks
 

Attachments

Top