Group By Calculations and Labeling

F

fzl2007

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
 
J

Jim Cone

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 USA
http://excelusergroup.org/




"fzl2007" <[email protected]>
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
 
F

fzl2007

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" <[email protected]>
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
Click to expand...


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
 
Top