Transpose Data

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
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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)
 

Christine

New member
Joined
Jun 17, 2013
Messages
4
Reaction score
0
Points
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)

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.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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....
 

Attachments

  • Book2.xlsx
    10 KB · Views: 10

Christine

New member
Joined
Jun 17, 2013
Messages
4
Reaction score
0
Points
0
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!
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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?
 
Top