Concatenate based on values in another column
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Concatenate based on values in another column

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Concatenate based on values in another column

    Hi all, first time post so I hope I'm following the correct procedure

    Was really hoping someone could give me guidance. Basically I have a file as per the information below and I need to merge Column2 and Column3 - but based on the value in Column 1.

    PARTNO1 Hyundai Accent
    PARTNO1 Hyundai i30
    PARTNO1 Hyundai i40
    PARTNO2 Hyundai Accent
    PARTNO2 Subaru Impreza
    PARTNO2 Toyota Celica
    PARTNO3 Toyota Celica
    PARTNO3 Toyota MR2

    So in the past I've done a CONCATENATE formula for each PARTNO in Column 1, but I now have significantly more part numbers and it's way too time consuming to do them all in one.

    Basically the finished file needs to look like this:

    PARTNO1 Hyundai Accent, i30, i40
    PARTNO2 Hyundai Accent, Subaru Impreza, Toyota Celica
    PARTNO3 Toyota Celica, MR2

    What's important to mention with this is that if the Car Make in Column 2 is different, it then includes the next unique Car Make from Column 2 as long as it's associated with the PARTNO in Column A.

    However, I also don't mind if it looks like this with the next unique car make on a second line.

    PARTNO1 Hyundai Accent, i30, i40
    PARTNO2 Hyundai Accent
    PARTNO2 Subaru Impreza
    PARTNO2 Toyota Celica
    PARTNO3 Toyota Celica, MR2


    Hope this has been explained well and appreciate any feedback to guide me along the way

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,311
    Post Thanks / Like
    Mentioned
    435 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Concatenate based on values in another column

    Hi & welcome to MrExcel.
    How about
    Code:
    Sub Steve1977()
       Dim Cl As Range
       Dim Dic As Object
       Dim Ky As Variant
       
       Set Dic = CreateObject("scripting.dictionary")
       With Sheets("Sheet1")
          For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
             If Not Dic.Exists(Cl.Value) Then Dic.Add Cl.Value, CreateObject("scripting.dictionary")
             If Not Dic(Cl.Value).Exists(Cl.Offset(, 1).Value) Then
                Dic(Cl.Value).Add (Cl.Offset(, 1).Value), Cl.Offset(, 2).Value
             Else
                Dic(Cl.Value)(Cl.Offset(, 1).Value) = Dic(Cl.Value)(Cl.Offset(, 1).Value) & ", " & Cl.Offset(, 2).Value
             End If
          Next Cl
       End With
       With Sheets("Sheet2")
          For Each Ky In Dic.Keys
             With .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Dic(Ky).Count)
                .Value = Ky
                .Offset(, 1).Resize(, 2).Value = Application.Transpose(Array(Dic(Ky).Keys, Dic(Ky).Items))
             End With
          Next Ky
       End With
    End Sub
    Change sheet names in red to suit.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate based on values in another column

    That's really good of you to take the time to post that - thank you!

    It's kinda working but it extracts it as the following:

    PARTNO1 Hyundai i30, i40
    PARTNO2 Hyundai Accent
    PARTNO2 Subaru Impreza
    PARTNO2 Toyota Celica
    PARTNO3 Toyota Celica, MR2
    PARTNO1 Hyundai i30, i40
    PARTNO2 Hyundai Accent
    PARTNO2 Subaru Impreza
    PARTNO2 Toyota Celica
    PARTNO3 Toyota Celica, MR2
    PARTNO1 Hyundai i30, i40
    PARTNO2 Hyundai Accent
    PARTNO2 Subaru Impreza
    PARTNO2 Toyota Celica
    PARTNO3 Toyota Celica, MR2


    So it's duplicated the data and removed 'Accent' from PARTNO1.
    Pretty amazing to get this far though!

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate based on values in another column

    STOP THE PRESS!!!!
    I've put a header in and now it works! Absolutely brilliant!

    PARTNO1 Hyundai Accent, i30, i40
    PARTNO2 Hyundai Accent
    PARTNO2 Subaru Impreza
    PARTNO2 Toyota Celica
    PARTNO3 Toyota Celica, MR2


    Are you able to explain how this works? Be nice to know the fundamentals to it all.
    To take it one step further, how would I code in a concatenate? Basically merge Column B with Column C.

    Also, where it recognises that Column B has a different value associated with the same part number (so it recognises that Hyundai, Subaru and Toyota need to be put on seperate lines). is there a way when doing the Concatenate so it lists it like this?

    PARTNO1 Hyundai Accent, i30, i40 (no change on this)
    PARTNO2 Hyundai Accent. Subaru Impreza. Toyota Celica (so the different vehicles where Column 2 is the same for the one part number, is seperated by a full stop?

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,311
    Post Thanks / Like
    Mentioned
    435 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Concatenate based on values in another column

    How about
    Code:
    Sub Steve1977()
       Dim Cl As Range
       Dim Dic As Object
       Dim Ky As Variant, K As Variant
       
       Set Dic = CreateObject("scripting.dictionary")
       With Sheets("Sheet1")
          For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
             If Not Dic.Exists(Cl.Value) Then Dic.Add Cl.Value, CreateObject("scripting.dictionary")
             If Not Dic(Cl.Value).Exists(Cl.Offset(, 1).Value) Then
                Dic(Cl.Value).Add (Cl.Offset(, 1).Value), Cl.Offset(, 2).Value
             Else
                Dic(Cl.Value)(Cl.Offset(, 1).Value) = Dic(Cl.Value)(Cl.Offset(, 1).Value) & ", " & Cl.Offset(, 2).Value
             End If
          Next Cl
       End With
       With Sheets("Sheet2")
          For Each Ky In Dic.Keys
             With .Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Value = Ky
                For Each K In Dic(Ky)
                   .Offset(, 1).Value = .Offset(, 1).Value & ", " & K & ", " & Dic(Ky)(K)
                Next K
                .Offset(, 1).Value = Replace(.Offset(, 1).Value, ", ", "", 1, 1)
             End With
          Next Ky
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate based on values in another column

    This is absolutely amazing!

    Whereby it seperates unique makes and it's respective model. At the moment it will state Hyundai Accent, Subaru, Impreza, Toyota, Celica

    But could it state

    Hyundai Accent. Subaru Impreza. Toyota Celica

    So the comma's in between Column 2 and 3 has been removed. Then before a new vehicle it puts a full stop.

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate based on values in another column

    To remove the comma's in between each model I've amended the line in the last bit to

    .Offset(, 1).Value = .Offset(, 1).Value & ", " & K & " " & Dic(Ky)(K)

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,311
    Post Thanks / Like
    Mentioned
    435 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Concatenate based on values in another column

    Try
    Code:
          For Each Ky In Dic.Keys
             With .Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Value = Ky
                For Each K In Dic(Ky)
                   .Offset(, 1).Value = .Offset(, 1).Value & ". " & K & " " & Dic(Ky)(K)
                Next K
                .Offset(, 1).Value = Replace(.Offset(, 1).Value, ". ", "", 1, 1)
             End With
          Next Ky
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate based on values in another column

    EDIT: Nice one!!! I've been looking through the various coding updates you've sent to try and get a grasp of this, but what can I say...absolutely unbelievable mate and I really value your help. What a great forum!
    Last edited by Steve1977; May 16th, 2019 at 11:30 AM.

  10. #10
    New Member
    Join Date
    May 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate based on values in another column

    Armed with applying the Macro I've ran into a peculiar issue. Basically with all the data I'm using, I need to get rid of Duplications in Column A and Column C.
    I then need to get rid of extra information contained in Brackets in Column C.

    To do this I simply created individual macro's for these two actions and added them to the beginning of the code (as per below), but when I run it, for some reason it does the following:

    Has one space before a comma
    Has two spaces before a comma
    Has one space before a fullstop.

    I did create 3 individual macro's to look for the instances above but it didn't seem to work.

    I'll still do some trial and error with it to try and find but thought I'd ask here and maybe there's a better way to apply the code rather than simply copying and pasting individual macro's?

    Code:
    ' Removes Duplicates    Columns("A:C").Select
        ActiveSheet.Range("$A$1:$C$1000000").RemoveDuplicates Columns:=Array(1, 3), _
            Header:=xlNo
    
    
    ' Removes Brackets
        Cells.Replace What:="(*)", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Data I'm using:

    100002 AUDI 100 (43, C2)
    100002 AUDI 100 (43, C2)
    100002 AUDI 100 (C1)
    100002 AUDI 100 (C1)
    100002 AUDI 100 (C1)
    100002 AUDI 100 (C1)
    100002 AUDI 100 (C1)
    100002 AUDI 100 (C1)
    100002 AUDI 100 Coupe (C1)
    100002 AUDI 100 Coupe (C1)
    100002 AUDI 50 (86)
    100002 AUDI 50 (86)
    100002 AUDI 50 (86)
    100002 AUDI 50 (86)
    100002 AUDI 50 (86)
    100002 AUDI 60
    100002 AUDI 75
    100002 AUDI 80 (80, 82, B1)
    100002 AUDI 80 (80, 82, B1)
    100002 AUDI 80 (80, 82, B1)
    100002 AUDI 80 (80, 82, B1)
    100002 AUDI 80 (80, 82, B1)
    100002 AUDI 80 (80, 82, B1)
    100002 AUDI 80 (80, 82, B1)
    100002 AUDI 80 (80, 82, B1)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (81, 85, B2)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (89, 89Q, 8A, B3)
    100002 AUDI 80 (8C2, B4)
    100002 AUDI 80 (8C2, B4)
    100002 AUDI 80 (8C2, B4)
    100002 AUDI 80 (8C2, B4)
    100002 AUDI 80 (8C2, B4)
    100002 AUDI 80 (8C2, B4)
    100002 AUDI 80 Avant (8C5, B4)
    100002 AUDI 90 (81, 85, B2)
    100002 AUDI 90 (81, 85, B2)
    100002 AUDI 90 (81, 85, B2)
    100002 AUDI 90 (81, 85, B2)
    100002 AUDI 90 (81, 85, B2)
    100002 AUDI 90 (89, 89Q, 8A, B3)
    100002 AUDI 90 (89, 89Q, 8A, B3)
    100002 AUDI 90 (89, 89Q, 8A, B3)
    100002 AUDI 90 (89, 89Q, 8A, B3)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •