Concatenating cells in the same column

[Shay A]

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.




Thank you!

 

[Hui]

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




Copy the following code into a Code Module in VBA

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]

Hi:

This is easy to do with a power query, find the attached. The result is in green table.

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

 

[Chihiro]

Or if you have Office 365 subscription...
In C2:
=TEXTJOIN("&",TRUE,IF($A$2:$A$10=A2,$B$2:$B$10,""))

Confirmed as Array (CTRL+SHIFT+ENTER). Copy down.

 

[Shay A]

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]

Hi:

This is easy to do with a power query, find the attached. The result is in green table.

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]

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.

#"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]

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.

#"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...

 

[Nebu]

Hi:

Find the M Code for including more categories

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