Help with code to re-arrange data for pareto analysis

[Steve S]

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

 

[Bob Phillips]

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]

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

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)