move data from one column to multiple columns

[mark91345]

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

	J	K	L	M	N	O	P	Q
1	Mutual Fund	Distance miles	Profit		Distance miles	AAEQX	ACWIX	ADENX
2	AAEQX	(blank)	0.0		(blank)	0.0	0.0
3		0-99	21.3		0-99	21.3	13.7
4		100-199	0.0		100-199	0.0	0.0
5		200-299	37.3		200-299	37.3	44.5
6		300-399	19.5		300-399	19.5	17.0
7		400-499	18.2		400-499	18.2	44.5
8		500-599	28.4		500-599	28.4	53.9
9		600-699	34.4		600-699	34.4	98.5
10		700-799	30.0		700-799	30.0	71.7
11		800-899	34.2		800-899	34.2	65.6
12		900-999	30.5		900-999	30.5	31.2
13		1000-1099	22.7		1000-1099	22.7	33.8
14		1100-1199	42.3		1100-1199	42.3	64.9
15		1200-1299	22.2		1200-1299	22.2	0.0
16		1300-1399	35.1		1300-1399	35.1	0.0
17		1400-1499	43.0		1400-1499	43.0	0.0
18		1500-1599	0.0		1500-1599	0.0	0.0
19		1600-1699	0.0		1600-1699	0.0	111.3
20		1700-1799	0.0		1700-1799	0.0	49.5
21		1800-1899	0.0		1800-1899	0.0	0.0
22		1900-1999	0.0		1900-1999	0.0	0.0
23		2000-2099	0.0		2000-2099	0.0	0.0
24		2100-2199	0.0		2100-2199	0.0	0.0
25		2200-2299	0.0		2200-2299	0.0	0.0
26		2300-2399	0.0		2300-2399	0.0	0.0
27		2400-2499	0.0		2400-2499	0.0	0.0
28		2500-2599	0.0		2500-2599	0.0	0.0
29		>2600	0.0		>2600	0.0	0.0
30	ACWIX	(blank)	0.0
31		0-99	13.7
32		100-199	0.0
33		200-299	44.5
34		300-399	17.0
35		400-499	44.5
36		500-599	53.9
37		600-699	98.5
38		700-799	71.7
39		800-899	65.6
40		900-999	31.2
41		1000-1099	33.8
42		1100-1199	64.9
43		1200-1299	0.0

<tbody>
</tbody>

Sheet4​

 

[oldbrewer]

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

 

[mark91345]

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)

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

 

[Rick Rothstein]

Well, I already have the column headers listed (see Column O,P, and there are 220 more).

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?

 

[mark91345]

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.

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?

 

[Rick Rothstein]

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.

Okay, assuming Column N exists and all column to the right of Column N are blank, give this macro a try...

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

 

[mark91345]

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?

 

[Rick Rothstein]

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?

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.

 

[kylefoley76]

Hi Rick,
Sorry I can't send you a pm because it's turned off on your profile. Do you know the answer to this one
http://www.mrexcel.com/forum/excel-questions/756729-material-implication.html
I used your macro to write it and I made some tweeks.