How to convert at single colum list into a multi column table

Discussion in 'Excel' started by DanM, Jul 15, 2014.

  1. DanM

    DanM

    Joined:
    Jul 5, 2014
    Messages:
    3
    Likes Received:
    0
    Hi,

    I have a listing (imported from a text file) that looks something like this (no blank rows)

    Animal: Dog
    Color: Brown
    Height: 20 inches
    Name: Spot

    Animal: cat
    Color: white
    Height: 12 inches
    Name: cutey

    Animal: bird
    Color: green
    Height: 6 inches
    Name: butch

    I'd like to get it in a format lie this:
    Animal: Dog cat bird
    Color: Brown white green
    Height: 20 inches 12 inches 6 inches
    Name: Spot cutey butch

    Any Ideas?

    Dan
     
    DanM, Jul 15, 2014
    #1
    1. Advertisements

  2. DanM

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    Allen, TX
    To save you some typing and typos, you can cut-n-paste these in the appropriate cells:
    =IF(LEFT($A2,LEN(B$1))=B$1,MID($A2,LEN(B$1)+1,22),"")
    =CONCATENATE(B1,B2,B3,B4,B5,B6,B7,B8,B9,B10,B11,B12,B13)​


    DanM1.jpg

    DanM2.jpg
     
    XLPadawan, Jun 13, 2016
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. mary86004

    Excel doesn't filter entire list.

    mary86004, Mar 17, 2012, in forum: Excel
    Replies:
    1
    Views:
    745
    CarolB
    Apr 24, 2012
  2. CarolB

    Advanced Drop Down List Option

    CarolB, Apr 17, 2012, in forum: Excel
    Replies:
    0
    Views:
    676
    CarolB
    Apr 17, 2012
  3. buffLouis

    NA in pivottable list

    buffLouis, Sep 4, 2012, in forum: Excel
    Replies:
    0
    Views:
    562
    buffLouis
    Sep 4, 2012
  4. Jorge
    Replies:
    0
    Views:
    550
    Jorge
    Aug 27, 2013
  5. Jasmeet
    Replies:
    0
    Views:
    622
    Jasmeet
    Jan 26, 2014
  6. Hwalker1
    Replies:
    2
    Views:
    486
    XLPadawan
    Jun 13, 2016
  7. Shivesh
    Replies:
    1
    Views:
    266
    XLPadawan
    Jun 14, 2016
  8. TryllZ
    Replies:
    4
    Views:
    229
    XLPadawan
    Jun 9, 2016
Loading...