Data aggreggation issue, need macro!   

Hi,

I have this data:

column A                  B               C              D

timestamp	bs	bp	as	ap
00:07.0
00:07.0	30	2402	9	2403
00:07.0
00:07.0	29	2402
00:07.0
00:07.0			8	2403
00:07.0
00:07.0	19	2402
00:07.0
00:07.0	17	2403	30	2404

What I want to do is have a macro that can combine the data so that it
moves all the values so that I have an order book that looks liek this

timestamp	bs	bp	as	ap
00:07.0	30	2402	9	2403
00:07.0	29	2402	8	2403
00:07.0	19	2402	30	2404
00:07.0	17	2403

 I do not know VBA but I want to learn, so if you are kind enough to
post the code I will make the effort to dissect it to understand how
it works.

I have given a name to the columns
A is TIME
B is BS
C is BP
D is AS
E is AP

Thank you

date: Fri, 3 Jul 2009 02:19:05 -0700 (PDT)   author:   totalnatal

Re: Data aggreggation issue, need macro!   

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

    Application.ScreenUpdating = False

    With ActiveSheet

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = LastRow To 2 Step -1

            If .Cells(i, "B").Value = "" Then

                .Rows(i).Delete
            End If
        Next i

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To LastRow

            If .Cells(i, "B").Value <> "" Then
                If .Cells(i, "D").Value = "" Then

                    If .Cells(i + 1, "D").Value = "" Then

                        .Cells(i + 1, "B").Resize(, 2).Cut .Cells(i, "D")
                    Else

                        .Cells(i + 1, "D").Resize(, 2).Cut .Cells(i, "D")
                    End If
                End If
            End If
        Next i

        For i = LastRow To 2 Step -1

            If .Cells(i, "B").Value = "" Then

                .Rows(i).Delete
            End If
        Next i
    End With

    Application.ScreenUpdating = True

End Sub



-- 
__________________________________
HTH

Bob

"totalnatal"  wrote in message 
news:bde4ca81-8334-4111-acb6-04159fcee898@e21g2000yqb.googlegroups.com...
> Hi,
>
> I have this data:
>
> column A                  B               C              D
>
> timestamp bs bp as ap
> 00:07.0
> 00:07.0 30 2402 9 2403
> 00:07.0
> 00:07.0 29 2402
> 00:07.0
> 00:07.0 8 2403
> 00:07.0
> 00:07.0 19 2402
> 00:07.0
> 00:07.0 17 2403 30 2404
>
> What I want to do is have a macro that can combine the data so that it
> moves all the values so that I have an order book that looks liek this
>
> timestamp bs bp as ap
> 00:07.0 30 2402 9 2403
> 00:07.0 29 2402 8 2403
> 00:07.0 19 2402 30 2404
> 00:07.0 17 2403
>
> I do not know VBA but I want to learn, so if you are kind enough to
> post the code I will make the effort to dissect it to understand how
> it works.
>
> I have given a name to the columns
> A is TIME
> B is BS
> C is BP
> D is AS
> E is AP
>
> Thank you

date: Fri, 3 Jul 2009 11:13:18 +0100   author:   Bob Phillips