Transpose Data

C

Christine

New member
Joined
Jun 17, 2013
Messages
4
Reaction score
0
Points
0
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
 
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)
 
JeffreyWeir said:
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)
Click to expand...

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.
 
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....
 

Attachments

  • Book2.xlsx
    10 KB · Views: 10
NBVC said:
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....
Click to expand...


*** Worked perfectly - thank you very much!
 
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?
 
You must log in or register to reply here.
Back
Top