On Jul 13, 3:10*pm, "Jim Cone" wrote:
Sounds like a take home test or just homework.
If not, what part of your code you are having trouble with?
--
Jim Cone
Portland, Oregon *USAhttp://excelusergroup.org/

"fzl2007"
wrote in message
This is my original data
ParentID ParentName ChildID ChildName Date A B C D E KK MM
1135909 ABC 884358 Jack 20100331 2000 4000 4000 2000 12000 19588 19588
1135909 ABC 975555 John 20100331 0 3000 0 0 3000 19588 19588
1838974 BBB 766258 Mary 20100331 9250 9250 0 0 18500 18500 21150
2112439 XYZ 54656 Faye 20100331 5000 0 0 0 5000 17823 19919
2112439 XYZ 91754 Jeff 20100331 5000 0 0 0 5000 17823 19919
2112439 XYZ 450753 Jean 20100331 2000 0 157 0 2157 17823 19919
2112439 XYZ 733054 Min 20100331 5229 0 252 0 5481 17823 19919
...

I need to format it to be like this,
A B C D E KK KK-E MM KK-MM
ParentID 1135909
ParentName ABC 2000 7000 4000 2000 15000 19588 4588 19588 0
884358 Jack 2000 4000 4000 2000 12000
975555 John 0 3000 0 0 3000

ParentID 1838974
ParentName BBB 9250 9250 0 0 18500 18500 0 21150 -2650
766258 Mary 9250 9250 0 0 18500

ParentID 2112439
ParentName XYZ 17229 0 409 0 17638 17823 185 19919 -2096
54656 Faye 5000 0 0 0 5000
91754 Jeff 5000 0 0 0 5000
450753 Jean 2000 0 157 0 2157
733054 Min 5229 0 252 0 5481

Create a line for each ParentID. On this line, display the total for
column of *A, B, C, D, E and its value of volume KK and MM.. *Column
KK and MM are always the same for each ParentID. *On this total line,
calculate and display the difference for columns KK-E and KK-MM.

On the total line, display the ParentName and above it display the
ParentID.

I appreciate your input.
Faye


This is something I try to produce at work. I can either do it with
SAS or Excel...

This is my current code. I don't know how to add the calculated for
columns KK-E and KK-MM and the totals for column A, B, C, D and E.

Thank you for your time.

Faye Larson
Dallas, TX


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

Set sh = ActiveSheet
With ActiveSheet

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

.Rows(i).Copy sh.Range("A" & i)
If .Cells(i, "A").Value = Cells(i - 1, "A").Value Then

sh.Range("A" & i).Resize(, 2).Value = ""
Cells(i, "K").Value = ""
Cells(i, "L").Value = ""

Else
sh.Rows(i).Insert

sh.Rows(i + 1).Insert

End If
Next i
.Rows(1).Resize(2).Copy sh.Range("A1")
End With


End Sub