Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Consolidating columns of text based
Thanks Thanks: 0 Likes Likes: 0

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

    Question Consolidating columns of text based

    I have a data set that looks like this:

    Col1 Col2
    a sam
    b sam
    c sam
    d sam
    e sam
    f sam
    g sam
    a bill
    b bill
    d bill
    g bill
    h bill
    d dave
    e dave
    f dave

    and I need to convert it to look like this:
    NewCol1 NewCol2
    a sam|bill
    b sam|bill
    c sam
    d sam|bill|dave
    e sam|dave
    f sam|dave
    h bill

    Basically, I need to consolidate all of the values from Col2 into a single data element where the values in col1 are repeated. Been looking at a creative Vlookup combined with a concat but cannot figure it out. My dataset is about 6000 rows.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Consolidating columns of text based

    What version of Xl are you using?
    - 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
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Consolidating columns of text based

    Excel 2016

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,853
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Consolidating columns of text based

    with Power Query aka Get&Transform

    Column1 Column2 Column1 Custom
    a sam a sam|bill
    b sam b sam|bill
    c sam c sam
    d sam d sam|bill|dave
    e sam e sam|dave
    f sam f sam|dave
    g sam g sam|bill
    a bill h bill
    b bill
    d bill
    g bill
    h bill
    d dave
    e dave
    f dave


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Group = Table.Group(Source, {"Column1"}, {{"Count", each _, type table}}),
        List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column2")),
        Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text})
    in
        Extract
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

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

    Default Re: Consolidating columns of text based

    Ok... I am sorry, I am sure that solves the problem but that is substantially beyond my capabilities... I do not have any idea how to execute that nor where to put it so I could execute it.

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,853
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Consolidating columns of text based

    no problem, wait for another solution

    have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Consolidating columns of text based

    Or with a macro
    Code:
    Sub Roonie847()
       Dim Cl As Range
       
       With CreateObject("scripting.dictionary")
          .CompareMode = 1
          For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
             If Not .exists(Cl.Value) Then
                .Add Cl.Value, Cl.Offset(, 1).Value
             Else
                .Item(Cl.Value) = .Item(Cl.Value) & "|" & Cl.Offset(, 1).Value
             End If
          Next Cl
          Range("D2").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
       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

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

    Default Re: Consolidating columns of text based

    Thanks for all of the help folks... I clearly need to take some Excel classes. It looks like that what I need to do is going to require something well beyond my abilities. Was thinking this was a simple thing that would not require coding or macros.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,228
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Consolidating columns of text based

    If you had TEXTJOIN it could be done with formulae, but as you dont PQ or VBA is probably the easiest option.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Consolidating columns of text based

    Trying again...
    Last edited by Toadstool; Sep 7th, 2019 at 01:53 PM.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

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
  •