Help with code to re-arrange data for pareto analysis

Hi all,

As I have no VBA experience, please would it be possible for someone to help
me with a problem I cannot easily solve. I would be grateful if someone
could point me in the right direction with some code.


I have the following data sheet : Four columns A,B,C,D - Not limited to 3
rows could be many hundreds and a different number each time the code is
run. Column A always has a date - Columns B,C and D have error codes -
sometimes one of the B,C or D can be blank, but never all three. I would
like to do pareto analysis by date on the collected error codes so
ultimately I would like on a seperate sheet - two columns A and B - A
containing the date and B containing the all the error codes. I can remove
rows which are blank in col B at a later stage.

A B C D
1/1/06 AA BB CC
3/1/06 BB
9/1/06 AA EE



I would like code that translates it to :

A B
1/1/06 AA
3/1/06 BB
9/1/06 AA
1/1/06 BB
3/1/06
9/1/06 EE
1/1/06 CC
3/1/06
9/1/06


Thank you for any help that would point me in the right direction for
solving this problem I have.

Regards

Steve

Steve,

Here is some code.

Sub Test()
Dim iLastRow As Long
Dim iLastCol As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = iLastCol To 3 Step -1
Rows(iLastRow + 1).Insert
Cells(iLastRow + 1, "A").Value = Cells(i, "A").Value
Cells(iLastRow + 1, "B").Value = Cells(i, j).Value
Cells(i, j).Value = ""
Next j
Next i

End Sub

To add it, go to the VBIDE, Alt-F11, insert a code module, Insert>Module,
and paste the code.

Go back to Excel, and run the macro, Tools>Macro>Macros... and select the
macro and press Run.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Steve S" <not@home> wrote in message
news:[email protected]...
> Hi all,
>
> As I have no VBA experience, please would it be possible for someone to
help
> me with a problem I cannot easily solve. I would be grateful if someone
> could point me in the right direction with some code.
>
>
> I have the following data sheet : Four columns A,B,C,D - Not limited to 3
> rows could be many hundreds and a different number each time the code is
> run. Column A always has a date - Columns B,C and D have error codes -
> sometimes one of the B,C or D can be blank, but never all three. I would
> like to do pareto analysis by date on the collected error codes so
> ultimately I would like on a seperate sheet - two columns A and B - A
> containing the date and B containing the all the error codes. I can remove
> rows which are blank in col B at a later stage.
>
> A B C D
> 1/1/06 AA BB CC
> 3/1/06 BB
> 9/1/06 AA EE
>
>
>
> I would like code that translates it to :
>
> A B
> 1/1/06 AA
> 3/1/06 BB
> 9/1/06 AA
> 1/1/06 BB
> 3/1/06
> 9/1/06 EE
> 1/1/06 CC
> 3/1/06
> 9/1/06
>
>
> Thank you for any help that would point me in the right direction for
> solving this problem I have.
>
> Regards
>
> Steve
>
>

Thanks Bob ...This works a treat ...


"Bob Phillips" <[email protected]> wrote in message
news:#[email protected]...
> Steve,
>
> Here is some code.
>
> Sub Test()
> Dim iLastRow As Long
> Dim iLastCol As Long
> Dim i As Long, j As Long
>
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = iLastRow To 1 Step -1
> iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
> For j = iLastCol To 3 Step -1
> Rows(iLastRow + 1).Insert
> Cells(iLastRow + 1, "A").Value = Cells(i, "A").Value
> Cells(iLastRow + 1, "B").Value = Cells(i, j).Value
> Cells(i, j).Value = ""
> Next j
> Next i
>
> End Sub
>
> To add it, go to the VBIDE, Alt-F11, insert a code module, Insert>Module,
> and paste the code.
>
> Go back to Excel, and run the macro, Tools>Macro>Macros... and select the
> macro and press Run.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Steve S" <not@home> wrote in message
> news:[email protected]...
> > Hi all,
> >
> > As I have no VBA experience, please would it be possible for someone to
> help
> > me with a problem I cannot easily solve. I would be grateful if someone
> > could point me in the right direction with some code.
> >
> >
> > I have the following data sheet : Four columns A,B,C,D - Not limited to
3
> > rows could be many hundreds and a different number each time the code is
> > run. Column A always has a date - Columns B,C and D have error codes -
> > sometimes one of the B,C or D can be blank, but never all three. I
would
> > like to do pareto analysis by date on the collected error codes so
> > ultimately I would like on a seperate sheet - two columns A and B - A
> > containing the date and B containing the all the error codes. I can
remove
> > rows which are blank in col B at a later stage.
> >
> > A B C D
> > 1/1/06 AA BB CC
> > 3/1/06 BB
> > 9/1/06 AA EE
> >
> >
> >
> > I would like code that translates it to :
> >
> > A B
> > 1/1/06 AA
> > 3/1/06 BB
> > 9/1/06 AA
> > 1/1/06 BB
> > 3/1/06
> > 9/1/06 EE
> > 1/1/06 CC
> > 3/1/06
> > 9/1/06
> >
> >
> > Thank you for any help that would point me in the right direction for
> > solving this problem I have.
> >
> > Regards
> >
> > Steve
> >
> >
>
>