After exporting a MSProject gantt chart into excel, I need to move/copy
contents of column c (Task) into columns to the right based on Outline Level
in column A. Example: Outline Level=1 in column D/L1, Outline Level=2 in
column E/L2. I wrote an IF statement to do this but the text copied into the
L1-8 columns cuts off because the columns are not very wide and there are
formulas in each.

"Outline
Level" ID Task L1 L2 L3 L4 L5 L6 L7 L8
1 1078 Test
2 3487 Input
3 8873 IT work
4 8737 Enhance
5 3483 Train
6 7483 Review
7 8473 Reallocate
8 7483 Finished

Can't you just widen the columns?

If you can put formulas in D2 to K2, you can try this:

In D2:
=if($a2=column()-3,$c2,"")
and drag across to column K

Then select D2:K2 and drag down as far as you need.



Debra wrote:

After exporting a MSProject gantt chart into excel, I need to move/copy
contents of column c (Task) into columns to the right based on Outline Level
in column A. Example: Outline Level=1 in column D/L1, Outline Level=2 in
column E/L2. I wrote an IF statement to do this but the text copied into the
L1-8 columns cuts off because the columns are not very wide and there are
formulas in each.

"Outline
Level" ID Task L1 L2 L3 L4 L5 L6 L7 L8
1 1078 Test
2 3487 Input
3 8873 IT work
4 8737 Enhance
5 3483 Train
6 7483 Review
7 8473 Reallocate
8 7483 Finished

--

Dave Peterson

We have been manually sorting by Outline Level and cutting and pasting 1's in
col D, 2's in col E, etc. By moving the data the text in a cell "lays over"
the empty cells. This keeps the spreadsheet a manageable size. I think I
need a macro similar to the one shown below, except it should move column C
based on the number specified in col A (Outline Level) which is 1-8.
Sub test()
Dim from_cells_count As Long
Dim i As Long
Dim row_num As Long
Dim col_num As Long

from_cells_count = (Range("A" & Rows.Count).End(xlUp).Row)
For i = 1 To from_cells_count
If Int(i / 7) < i / 7 Then
row_num = Int(i / 7) + 1
col_num = (i Mod 7) + 1
Else
row_num = i / 7
col_num = 8
End If
Cells(row_num, col_num) = Range("A" & i)
Next i
End Sub


"Dave Peterson" wrote:

Can't you just widen the columns?

If you can put formulas in D2 to K2, you can try this:

In D2:
=if($a2=column()-3,$c2,"")
and drag across to column K

Then select D2:K2 and drag down as far as you need.



Debra wrote:

After exporting a MSProject gantt chart into excel, I need to move/copy
contents of column c (Task) into columns to the right based on Outline Level
in column A. Example: Outline Level=1 in column D/L1, Outline Level=2 in
column E/L2. I wrote an IF statement to do this but the text copied into the
L1-8 columns cuts off because the columns are not very wide and there are
formulas in each.

"Outline
Level" ID Task L1 L2 L3 L4 L5 L6 L7 L8
1 1078 Test
2 3487 Input
3 8873 IT work
4 8737 Enhance
5 3483 Train
6 7483 Review
7 8473 Reallocate
8 7483 Finished

--

Dave Peterson