move contents of column C based on criteria related to column A

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
>