Results 1 to 8 of 8

Thread: Transpose Data

  1. #1

    Transpose Data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I want to take the description to the left of ":" and make a column heading and put the detail to the right of ":" below the headings:

    DBV:1
    RTX:AE-941/PLACEBO
    VIN:AE941PL
    ALRDIN:9999999A
    DBV:2
    RTX:ALVAC CEA/B7.1
    VIN:ALVST
    ALRDIN:9999999A
    DBV:3
    RTX:ANASTROZOL MA 27 STUDY
    VIN:ANA1TST
    ALRDIN:02224135

    Want it to look like:
    DVB RTX VIN ALRDIN
    1
    2
    3

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    If this is a one-off, then assuming your list starts in A1:
    in B1 put this:
    =LEFT(A1,FIND(":",A1)-1)
    In C1 put this:
    =MID(A1,FIND(":",A1)+1,LEN(A1))

    Copy both formulas down.
    Select the resulting two rows, and copy (Ctrl C) then paste special values (Alt E S V, click OK) , to turn it into hard-coded values
    now copy the two rows again, and select a cell where you want the results, and paste special transpose (Alt E S E, click OK)

  3. #3
    Quote Originally Posted by JeffreyWeir View Post
    If this is a one-off, then assuming your list starts in A1:
    in B1 put this:
    =LEFT(A1,FIND(":",A1)-1)
    In C1 put this:
    =MID(A1,FIND(":",A1)+1,LEN(A1))

    Copy both formulas down.
    Select the resulting two rows, and copy (Ctrl C) then paste special values (Alt E S V, click OK) , to turn it into hard-coded values
    now copy the two rows again, and select a cell where you want the results, and paste special transpose (Alt E S E, click OK)
    Thanks for this. What you described worked perfectly but it is not the outcome I was hoping for.
    I am looking for the end result to only have (4) column headers with the detail listed below:

    DBV RTX VIN ALRDIN
    1
    2
    3
    etc

    Thanks again.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Assuming original data is in Sheet1, starting at A1, and that the information grouping is consistent, then in Sheet2, list the headers across row1 starting at A1, then in A2 enter formula:

    =TRIM(SUBSTITUTE(INDEX(Sheet1!$A:$A,4*(ROWS(A$1:A1)-1)+COLUMNS($A1:A1)+ROW(Sheet1!$A$1)-1),A$1&":",""))

    copied down and across as far as needed....
    Attached Files Attached Files


  5. #5
    Quote Originally Posted by NBVC View Post
    Assuming original data is in Sheet1, starting at A1, and that the information grouping is consistent, then in Sheet2, list the headers across row1 starting at A1, then in A2 enter formula:

    =TRIM(SUBSTITUTE(INDEX(Sheet1!$A:$A,4*(ROWS(A$1:A1)-1)+COLUMNS($A1:A1)+ROW(Sheet1!$A$1)-1),A$1&":",""))

    copied down and across as far as needed....

    *** Worked perfectly - thank you very much!

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You are welcome


  7. #7
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    man, I sooo misinterpeted that question! Nice formula, NBVC.
    Christine: just to confirm, your data always repeats the thing to the left of the colon every 4th row?

  8. #8
    Quote Originally Posted by JeffreyWeir View Post
    man, I sooo misinterpeted that question! Nice formula, NBVC.
    Christine: just to confirm, your data always repeats the thing to the left of the colon every 4th row?
    Yes ... exactly.

Posting Permissions

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