Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

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

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

    Default Re: Consolidating columns of text based

    @Toadstool
    Your results for f, g & h look wrong, if you are using the data supplied in the op.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Consolidating columns of text based

    It's unclear why your NewCol2 doesn't have an entry for g?

    If I can use worker columns this does the job, but you'll need to copy N right if you've more than 6 Col2s for a Col1 and add ampersands to H.

    A B C D E F G H I J K L M N
    1 Col1 Col2 NewCol1 NewCol2 Toad New Col1 Toad New Col2 Wrk1 Wrk2 Wrk3 Wrk4 Wrk5 Wrk6
    2 a sam a sam|bill a sam|bill sam |bill
    3 b sam b sam|bill b sam|bill sam |bill
    4 c sam c sam c sam sam
    5 d sam d sam|bill|dave d sam|bill|dave sam |bill |dave
    6 e sam e sam|dave e sam|dave sam |dave
    7 f sam f sam|dave f sam|dave sam |dave
    8 g sam h bill g sam|bill sam |bill
    9 a bill h bill bill
    10 b bill
    Roonie847

    Worksheet Formulas
    Cell Formula
    H2 =I2&J2&K2&L2&M2&N2
    I2 =IFERROR(INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:I$1))),"")
    J2 =IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:J$1))),"")
    K2 =IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:K$1))),"")
    L2 =IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:L$1))),"")
    M2 =IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:M$1))),"")
    N2 =IFERROR("|"&INDEX($B$2:$B$16,AGGREGATE(15,6,ROW($A$2:$A$16)-1/($A$2:$A$16=$G2),COLUMNS($I$1:N$1))),"")

    Array Formulas
    Cell Formula
    G2 {=IFERROR(INDEX($A$2:$A$16, MATCH(0, COUNTIF(G$1:$G1, $A$2:$A$16), 0)), "")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    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
  •