1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Shay A, Dec 3, 2018.

  1. Shay A

    Shay A Member

    Messages:
    213
    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!

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,668
    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 (vb):

    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/
    Thomas Kuriakose likes this.
  3. Nebu

    Nebu Excel Ninja

    Messages:
    2,118
    Hi:

    This is easy to do with a power query, find the attached. The result is in green table.
    Code (vb):
    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

    Attached Files:

    Thomas Kuriakose likes this.
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,115
    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.
    Thomas Kuriakose likes this.
  5. Shay A

    Shay A Member

    Messages:
    213
    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?
  6. Shay A

    Shay A Member

    Messages:
    213
    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
  7. Nebu

    Nebu Excel Ninja

    Messages:
    2,118
    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 (vb):
    #"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

Share This Page