move data from one column to multiple columns

M

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
113
I am trying to copy sections of one column to multiple columns.
For example, how can I copy the PROFIT data (for AAEQX) from Column K into Column P, but then copy ACWIX into Column Q, and so on (I have 226 of these to do)?

In the example, for the first couple columns, I simply used '=' and the cell address; but I need to automate this, somehow, as I have too much data to do this to (plus, I know I'll confuse myself).

Excel 2010
JKLMNOPQ
(blank)
0-99
100-199
200-299
300-399
400-499
500-599
600-699
700-799
800-899
900-999
1000-1099
1100-1199
1200-1299
1300-1399
1400-1499
1500-1599
1600-1699
1700-1799
1800-1899
1900-1999
2000-2099
2100-2199
2200-2299
2300-2399
2400-2499
2500-2599
>2600
(blank)
0-99
100-199
200-299
300-399
400-499
500-599
600-699
700-799
800-899
900-999
1000-1099
1100-1199
1200-1299

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FDE9D9"]Mutual Fund[/TD]
[TD="bgcolor: #FDE9D9"]Distance miles[/TD]
[TD="bgcolor: #FDE9D9"]Profit[/TD]
[TD="bgcolor: #FDE9D9, align: right"][/TD]
[TD="bgcolor: #FDE9D9"]Distance miles[/TD]
[TD="bgcolor: #FDE9D9"]AAEQX[/TD]
[TD="bgcolor: #FDE9D9"]ACWIX[/TD]
[TD="bgcolor: #FDE9D9"]ADENX[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]AAEQX[/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"](blank)[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]21.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0-99[/TD]
[TD="align: right"]21.3[/TD]
[TD="align: right"]13.7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]100-199[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]37.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]200-299[/TD]
[TD="align: right"]37.3[/TD]
[TD="align: right"]44.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]19.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]300-399[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]17.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]18.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]400-499[/TD]
[TD="align: right"]18.2[/TD]
[TD="align: right"]44.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]28.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]500-599[/TD]
[TD="align: right"]28.4[/TD]
[TD="align: right"]53.9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]34.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]600-699[/TD]
[TD="align: right"]34.4[/TD]
[TD="align: right"]98.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]30.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]700-799[/TD]
[TD="align: right"]30.0[/TD]
[TD="align: right"]71.7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]34.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]800-899[/TD]
[TD="align: right"]34.2[/TD]
[TD="align: right"]65.6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]30.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]900-999[/TD]
[TD="align: right"]30.5[/TD]
[TD="align: right"]31.2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]22.7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000-1099[/TD]
[TD="align: right"]22.7[/TD]
[TD="align: right"]33.8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"]42.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1100-1199[/TD]
[TD="align: right"]42.3[/TD]
[TD="align: right"]64.9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]22.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1200-1299[/TD]
[TD="align: right"]22.2[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"]35.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1300-1399[/TD]
[TD="align: right"]35.1[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1400-1499[/TD]
[TD="align: right"]43.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1500-1599[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1600-1699[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]111.3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1700-1799[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]49.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1800-1899[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1900-1999[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2000-2099[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2100-2199[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2200-2299[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2300-2399[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2400-2499[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2500-2599[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]>2600[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #FFFF00"]ACWIX[/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]

[TD="align: right"]13.7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]

[TD="align: right"]44.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]

[TD="align: right"]17.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]

[TD="align: right"]44.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]

[TD="align: right"]53.9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]

[TD="align: right"]98.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]

[TD="align: right"]71.7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"][/TD]

[TD="align: right"]65.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"][/TD]

[TD="align: right"]31.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="align: right"][/TD]

[TD="align: right"]64.9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4​
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
profit data appears to be in L

do you mean you want to insert a new column and put the value into it

this will push all columns one to the right
 
Upvote 0
Well, I already have the column headers listed (see Column O,P, and there are 220 more).
From the original Profit column, I am trying to copy data from it to the columns on the right, but only for a specific portion (for each Mutual fund--- note: I only show a couple because of the sheer length). I did a couple examples to see the what the finished product should look like (Col O & P)


oldbrewer said:
profit data appears to be in L

do you mean you want to insert a new column and put the value into it

this will push all columns one to the right
Click to expand...
 
Upvote 0
mark91345 said:
Well, I already have the column headers listed (see Column O,P, and there are 220 more).
Click to expand...
You already have 222 column headers in cells O1:IB1? If so, then please answer all of these questions...

1) Will you always have the same 222 Mutual Fund names in Column J?

2.A) If so, will they always be in the same order as the headers?

2.B) If not, would it be alright not to have the header in O1:IB1 and let the code put in the headers for the data you actually have in the order the data is presented in?
 
Upvote 0
1) Yes, I will always have the same 222 Mutual Fund (Col J)
2) Always in the same order (alphabetical)
2b) I would be THRILLED to have the headers (the mutual funds like AAEQX, etc.) be put in by the code.

Rick Rothstein said:
You already have 222 column headers in cells O1:IB1? If so, then please answer all of these questions...

1) Will you always have the same 222 Mutual Fund names in Column J?

2.A) If so, will they always be in the same order as the headers?

2.B) If not, would it be alright not to have the header in O1:IB1 and let the code put in the headers for the data you actually have in the order the data is presented in?
Click to expand...
 
Upvote 0
mark91345 said:
1) Yes, I will always have the same 222 Mutual Fund (Col J)
2) Always in the same order (alphabetical)
2b) I would be THRILLED to have the headers (the mutual funds like AAEQX, etc.) be put in by the code.
Click to expand...
Okay, assuming Column N exists and all column to the right of Column N are blank, give this macro a try...
Code: 
Sub RearrangeMutualFundData()
  Dim Ar As Range, LastRow As Long, Blanks As Range
  LastRow = Cells(Rows.Count, "K").End(xlUp).Row
  Set Blanks = Range("J1:J" & LastRow).SpecialCells(xlBlanks)
  For Each Ar In Blanks.Areas
    With Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
      .Value = Ar(1).Offset(-1)
      Ar.Offset(, 2).Copy .Offset(1)
    End With
  Next
End Sub
 
Upvote 0
Thank you, Rick. This really made it easy for me. I tried reading the script to understand, but I see that I am going to have to learn this. Just to be sure, this is called "Visual Basic", correct?
 
Upvote 0
mark91345 said:
Thank you, Rick. This really made it easy for me. I tried reading the script to understand, but I see that I am going to have to learn this. Just to be sure, this is called "Visual Basic", correct?
Click to expand...
Actually, Visual Basic for Applications (VBA for short). If you are thinking of buying books or training, make sure the "for Application" or "A" is in there... Microsoft has a product they call VB or Visual Basic which is not the same thing at all.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Stringing If Statements together!
Replies
2
Views
402
Metalboy
M
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
425
johnnyL
J
J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
C
  • Question Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
861
onlyadrafter
O

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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