Need to separate column data into headers

N

nsyrax

New Member
Joined
Jan 14, 2014
Messages
1
I have a spreadsheet that has a single column with multiple values, an identifier and a percentage. What I need to do is separate that data so that the identifier is copied and made into a column header and the percentage related to that identifier is in that column. HERE is a sample of the data as it is on sheet one, and as I need it on sheet 2.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
1.Enter following in C1, press SHIFT+CRTL+ENTER,
=IF(COLUMN(A1)>MAX(LEN($B$2:$B$14)-LEN(SUBSTITUTE($B$2:$B$14,"%",))),"",MID($B$2,IF(COLUMN(A1)=1,1,FIND("^",SUBSTITUTE($B$2,"%","^",COLUMN(A1)-1))+2),5))
NOTE: I tried to used array formula to get investment code but still miss one in L1, you need to manually enter "PRRIX" in L1. or you could manually complete the code list form D1 to L1.
2. enter following in C2, then drag it to copy it to L14 (no need to press 2 keys together)
=IF(ISERROR(FIND(C$1,$B2)),"",SUBSTITUTE(MID($B2,FIND("%",$B2,FIND(C$1,$B2))-2,3),",",)-0)
NOTE:
per my formula, the cell highlighted by yellow in row4 is different from yours in your file, i think mine is correct. if not, probably you need to advise people who try to help you the additional criterial to get the data from column B.




[TABLE="width: 916"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 340pt; mso-width-source: userset; mso-width-alt: 16099;" width="453"> <col style="width: 48pt;" span="10" width="64"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl69, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl69, width: 453, bgcolor: transparent"]B[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]D[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]E[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]F[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]G[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]H[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]I[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]J[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]K[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]L[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Name[/TD]
[TD="class: xl65, bgcolor: transparent"]Investments[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX[/TD]
[TD="class: xl65, bgcolor: transparent"]PTSAX[/TD]
[TD="class: xl65, bgcolor: transparent"]VIFSX[/TD]
[TD="class: xl65, bgcolor: transparent"]FCNTX[/TD]
[TD="class: xl65, bgcolor: transparent"]DODGX[/TD]
[TD="class: xl65, bgcolor: transparent"]VMISX[/TD]
[TD="class: xl65, bgcolor: transparent"]LSBDX[/TD]
[TD="class: xl65, bgcolor: transparent"]CSRIX[/TD]
[TD="class: xl65, bgcolor: transparent"]NAESX[/TD]
[TD="class: xl70, bgcolor: #FFC000"]PRRIX[/TD]
[TD="class: xl73, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bob[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,15%,PTSAX,12%,VIFSX,8%,FCNTX,7%,DODGX,20%,VMISX,10%,LSBDX,13%,CSRIX,5%,NAESX,10%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]15%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]8%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]13%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10%[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Sara[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,15%,DODGX,40%,VMISX,20%,LSBDX,10%,CSRIX,5%,NAESX,10%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]15%[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]40%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]20%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10%[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Philip[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,15%,PRRIX,10%,PTSAX,25%,FCNTX,15%,DODGX,15%,VMISX,5%,LSBDX,5%,CSRIX,5%,NAESX,5%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]15%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]25%[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]15%[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]15%[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]5%[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]5%[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5%[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]5%[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10%[/TD]
[TD="class: xl73, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]George[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,15%,PTSAX,15%,FCNTX,10%,DODGX,20%,VMISX,10%,LSBDX,15%,CSRIX,5%,NAESX,10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]15%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]15%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]15%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl73, bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tina[/TD]
[TD="class: xl65, bgcolor: transparent"]JTLGX,50%,ICEUX,10%,PRRIX,10%,DODGX,15%,VMISX,5%,CSRIX,5%,NAESX,5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]15%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl72, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl73, bgcolor: transparent"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Vicki[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,15%,ML7XS,15%,PTSAX,15%,FCNTX,20%,DODGX,10%,VMISX,10%,CSRIX,5%,NAESX,10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]15%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]15%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl72, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl73, bgcolor: transparent"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Harold[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,25%,VIFSX,5%,FCNTX,20%,DODGX,20%,VMISX,20%,LSBDX,5%,CSRIX,5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]25%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl72, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl73, bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,15%,FCNTX,35%,DODGX,20%,VMISX,15%,LSBDX,10%,CSRIX,5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]15%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]35%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]15%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl72, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl73, bgcolor: transparent"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Margeret[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,20%,VIFSX,20%,FCNTX,20%,DODGX,20%,CSRIX,20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]20%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl72, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl73, bgcolor: transparent"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Eric[/TD]
[TD="class: xl65, bgcolor: transparent"]JTLRX,10%,PRRIX,10%,ML7XS,50%,PTSAX,10%,VIFSX,9%,VMISX,6%,CSRIX,5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]9%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]6%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]5%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl72, bgcolor: #8DB4E2, align: right"]10%[/TD]
[TD="class: xl73, bgcolor: transparent"]11[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Rick[/TD]
[TD="class: xl65, bgcolor: transparent"]JTLGX,25%,VIFSX,25%,VMISX,25%,CSRIX,25%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]25%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]25%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]25%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl72, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl73, bgcolor: transparent"]12[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Angela[/TD]
[TD="class: xl65, bgcolor: transparent"]ICEUX,33%,VMISX,34%,CSRIX,33%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]33%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]34%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]33%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl72, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl73, bgcolor: transparent"]13[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Amy[/TD]
[TD="class: xl65, bgcolor: transparent"]VMISX,50%,CSRIX,50%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]50%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl68, bgcolor: #8DB4E2, align: right"]50%[/TD]
[TD="class: xl68, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl72, bgcolor: #8DB4E2"] [/TD]
[TD="class: xl73, bgcolor: transparent"]14[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Return column header name for item located in an array
Replies
4
Views
190
DanteAmor
DanteAmor
L
  • Solved
Repeat formula through column X times
Replies
9
Views
234
Fluff
Fluff
A
  • Question Question
populate data based on Matching headers for multiple sheets across files
Replies
0
Views
182
Amer Omar
A
P
  • Question Question
Formula to locate and remove outlying values from a large data set
Replies
3
Views
158
hagia_sofia
hagia_sofia
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
288
mumps
mumps

Forum statistics

Threads
1,223,712
Messages
6,174,031
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top