| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group By Calculations and Labeling
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Group By Calculations and Labeling
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" 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Group By Calculations and Labeling
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Axis labeling | Charts and Charting in Excel | |||
| Labeling results | New Users to Excel | |||
| arrays and labeling | Excel Programming | |||
| X-axis labeling | Charts and Charting in Excel | |||
| labeling | Excel Programming | |||
Linear Mode
