Subtotals #2

When the value in column A changes, I need the amount-
subtotals for each of the values in columns B and C.

(See sample spreadsheet below

For example, for "687223007-5", I need:

For "19-038":   $  388.89
For "68-1366":      30.36
For "68-1378":       3.51
For "68-1856":      12.72
For "68-2232":     332.91
For "68-4115":     353.20

(Keep in mind that, for the value in column A, the number 
of entries in columns B and C may range from 1 to ???).

                               Gary
     
    A             B        C           D

687196004-9	19-002 		    2,335.30
687223007-5		68-1366	       20.24
687223007-5		68-1378	        2.34
687223007-5		68-1856	        8.48
687223007-5		68-2232	      223.68
687223007-5		68-4115	      242.40
687223007-5	19-038 		      257.82
687223007-5		68-1366	       10.12
687223007-5		68-1378	        1.17
687223007-5		68-1856	        4.24
687223007-5		68-2232	      109.23
687223007-5		68-4115	      110.80
687223007-5	19-038 		      131.07
687233016-4		68-2232	       22.58
687233016-4	19-003 		       76.24
687233016-4		68-2232	       27.72
687233016-4	19-003 		       78.24
687233016-4		68-2232	       28.76
687233016-4	19-003 		       80.78
687252002-0		68-1366	       31.38
687252002-0		68-1378	        3.64
687252002-0		68-1856	        8.48
687252002-0		68-2232	      134.82
687252002-0		68-4115	      129.12
687252002-0	19-003 		      465.10
687252002-0		68-1366	       31.38
687252002-0		68-1378	        3.64
687252002-0		68-1856	        8.48
687252002-0		68-2232	      139.80
687252002-0		68-4115	      129.96
687252002-0	19-003 		      480.18
689151007-7		68-2683	       60.00
689151007-7		68-2694	       10.78
689151007-7		68-2695	       26.58
689151007-7	17-001 		      513.52
689151007-7		68-1865	        4.68
689151007-7		68-2683        60.00
689151007-7		68-2694	       11.86
689151007-7		68-2695	       30.56
689151007-7	17-001 		      401.74
689151007-7		68-1865	        4.68
689151007-7		68-2683	       60.00
689151007-7		68-2694	       12.86
689151007-7		68-2695	       33.62
689151007-7	17-001 		      416.12
689151007-7		68-1865	        4.68
689151007-7		68-2683	       60.00
689151007-7		68-2694	       13.66
689151007-7		68-2695	       40.46
689151007-7	17-001 		      424.04
689151007-7		68-1865	        4.68
689151007-7		68-2683	       60.00
689151007-7		68-2694	       13.66
689151007-7		68-2695        40.46
689151007-7		68-4612	        9.90
689151007-7	17-001 		      438.38

0
gcotterl (63)
2/20/2004 1:04:10 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
461 Views

Similar Articles

[PageSpeed] 26

Gary

If you arrange the data slightly you can use excel's built 
in SubTotals function (Data Menu)

I have rearrange the data so that partnos? appear in one 
column. There is a macro to do this if this arrangement is 
suitable. The one following.

Sub MovePartNo()
  Dim c As Variant
  Dim i As Long, nr As Long
  Dim rng As Range
  nr = Application.WorksheetFunction.CountA(Range("A:A"))
  Set rng = Range(Cells(2, 3), Cells(nr, 3))
' Copy column D parts to column C
  For Each c In rng
    If IsEmpty(c) Then
      c.Value = c.Offset(0, 1)
    End If
  Next c
' delete column D
  Columns("D:D").Delete shift:=xlToRight
End Sub

The following macro is just recorded it sorts the data by 
job number (old column A) and then by Part number then it 
creates subtotals each change in part# on price

Sub sortForSubTotals()
' Sort by Job then Part
'
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, 
Key2:=Range("C2") _
        , Order2:=xlAscending, Header:=xlGuess, 
OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
        
' At each change in Part Sum Price
    Selection.Subtotal GroupBy:=3, Function:=xlSum, 
TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, 
SummaryBelowData:=True
    Columns("C:C").EntireColumn.AutoFit
End Sub

This might have to be altered to run with more data - Im 
not sure. The final data will look like this

Index	Job	Part no	Price
1	687196004-9	19-002	2,335.30
		19-002 Total	2,335.30
7	687223007-5	19-038	257.82
13	687223007-5	19-038	131.07
		19-038 Total	388.89
2	687223007-5	68-1366	20.24
8	687223007-5	68-1366	10.12
		68-1366 Total	30.36

This can be further condensed to this

Index	Job	Part no	Price
		19-002 Total	2,335.30
		19-038 Total	388.89
		68-1366 Total	30.36

If this is unsuitable someone will propably write another 
macro to suit you

Regards
Peter
>-----Original Message-----
>When the value in column A changes, I need the amount-
>subtotals for each of the values in columns B and C.
>
>(See sample spreadsheet below
>
>For example, for "687223007-5", I need:
>
>For "19-038":   $  388.89
>For "68-1366":      30.36
>For "68-1378":       3.51
>For "68-1856":      12.72
>For "68-2232":     332.91
>For "68-4115":     353.20
>
>(Keep in mind that, for the value in column A, the number 
>of entries in columns B and C may range from 1 to ???).
>
>                               Gary
>     
>    A             B        C           D
>
>687196004-9	19-002 		    2,335.30
>687223007-5		68-1366	       20.24
>687223007-5		68-1378	        2.34
>687223007-5		68-1856	        8.48
>687223007-5		68-2232	      223.68
>687223007-5		68-4115	      242.40
>687223007-5	19-038 		      257.82
>687223007-5		68-1366	       10.12
>687223007-5		68-1378	        1.17
>687223007-5		68-1856	        4.24
>687223007-5		68-2232	      109.23
>687223007-5		68-4115	      110.80
>687223007-5	19-038 		      131.07
>687233016-4		68-2232	       22.58
>687233016-4	19-003 		       76.24
>687233016-4		68-2232	       27.72
>687233016-4	19-003 		       78.24
>687233016-4		68-2232	       28.76
>687233016-4	19-003 		       80.78
>687252002-0		68-1366	       31.38
>687252002-0		68-1378	        3.64
>687252002-0		68-1856	        8.48
>687252002-0		68-2232	      134.82
>687252002-0		68-4115	      129.12
>687252002-0	19-003 		      465.10
>687252002-0		68-1366	       31.38
>687252002-0		68-1378	        3.64
>687252002-0		68-1856	        8.48
>687252002-0		68-2232	      139.80
>687252002-0		68-4115	      129.96
>687252002-0	19-003 		      480.18
>689151007-7		68-2683	       60.00
>689151007-7		68-2694	       10.78
>689151007-7		68-2695	       26.58
>689151007-7	17-001 		      513.52
>689151007-7		68-1865	        4.68
>689151007-7		68-2683        60.00
>689151007-7		68-2694	       11.86
>689151007-7		68-2695	       30.56
>689151007-7	17-001 		      401.74
>689151007-7		68-1865	        4.68
>689151007-7		68-2683	       60.00
>689151007-7		68-2694	       12.86
>689151007-7		68-2695	       33.62
>689151007-7	17-001 		      416.12
>689151007-7		68-1865	        4.68
>689151007-7		68-2683	       60.00
>689151007-7		68-2694	       13.66
>689151007-7		68-2695	       40.46
>689151007-7	17-001 		      424.04
>689151007-7		68-1865	        4.68
>689151007-7		68-2683	       60.00
>689151007-7		68-2694	       13.66
>689151007-7		68-2695        40.46
>689151007-7		68-4612	        9.90
>689151007-7	17-001 		      438.38
>
>.
>
0
2/20/2004 2:34:09 AM
Reply:

Similar Artilces:

Epson TM-T88 III issues after upgrade to 2.0
after my upgrade from 1.3 to 2.0 am not able to print from my store operations manager. I can not print from store operations manager \Journal viewer lets say I want to print receipt from one transaction or reprint a z report and I get this error: invalid operation 425: the printer cannot be accessed. It may be locked by another application. This is a Epson printer that worked fine before the upgrade, and right now am able to print all the pos transactions and get prints from my x or z report from pos If your receipt printer is installed as OPOS: Is your POS open at the same time yo...

Can't change Outlook font color #2
I had the same issue in Outlook 2007. I went in to Tools Options Mail Format and insured HTML was selected. I de-selected HTML and applied then re-selected HTML and applied and the issue was resolved. EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com/default.aspx?ref=ng ...

HRM Attendance #2
Is there a way to add in the system the Public Holidays so that when an employee applies for vacation for certain dates public holidays are not taken into account as vacation time? Is this a customization? and if yes would it be in GP or in web part? Stefanos ...

sum by coding without 0 #2
hi in the following program if there is no value in the cells a1 and b1 it returns 0 Public Sub Add2Cells() Range("C1").Value = Range("A1").Value + Range("B1").Value End Sub i need only the output if the both the cells have the data will you can tell me that Public Sub Add2Cells() If Range("A1").Value <> "" And .Range("B1").Value <> "" Then Range("C1").Value = Range("A1").Value + Range("B1").Value End If End Sub -- HTH Bob (there's no email, no ...

Excel Unable to Open Browser #2
SFEcon is website that stages MSWord and Excel documents for download. These documents contain links back to the site allowing ou correspondents to view web pages that explain things in the documents. We recently upgraded our operation with a new P4 computer, and fin Excel behaving strangely in that clicking on a link in one of ou spreadsheets yields the following error: Microsoft Excel ´┐ŻUnable to open http://www.sfecon.com/GEP.htm. Cannot locate th internet server or proxy server.´┐Ż The problem does NOT occur with links embedded in MSWord documents. We are able to isolate the error furt...

Outlook 2003 with Business Contact Manager #2
Just installed over Office 2003 the Business Contact Manager, and it keeps giving me error message "Database creation has failed" when I try to start it up. Upgraded over Office XP on Windows XP with all the latest updates. It's driving me mad. Suggestions? Thanks, T ...

changing from 2 dongles (2 databases) to 1 dongle (2lanes)
We recently bought MS RMS 1.3a, even bought support on it. But, our needs have changed we went from thinking we would have 2 seperate databases to just 1 database and needing 2 lanes. Now, our reseller who thought Microsoft would accept a return and exchange us or allow us to buy the 2 lane dongle informs us Microsoft has denied our return request. Has anyone else had similiar problems? I want to know before i call MS myself to discuss their reason for denying us. I had not know MBS to be unreasonable to customers but this is flat out rude to not allow us to do what we need and wast...

RDNS Failed #2
Can I set up Exchange 2003 to drop all email where RDNS fails? This would eliminate a large group of spam and I have done this using sendmail. You cannot do this natively. You would need to either write an event sink, or purchase a 3rd party anti-spam product that does this. Blocking based on failed RDNS may seem like a good idea, but I am of the opinion that it should not be used to block e-mail in a corporate environment. Use it to increase the spam score of an e-mail, but there are too many legitimate domains that still don't understand the concept of reverse dns to be able t...

Data to transfer from column to row #2
Dear All, I have a cell that contains like: PM-1234, PM-2345, PM-5689, PM-9999. I want to segregate these 4 tag into rows. i.e., PM-1234 PM-2345 PM-5689 PM-9999 Is there any excel guru out there to help in my problem??? Your help is greatly appreciated. Thank you, qwerty7 -- qwerty7 ----------------------------------------------------------------------- qwerty70's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3267 View this thread: http://www.excelforum.com/showthread.php?threadid=52474 If you highlight the four cells containing the data, then click <...

In a 2 Y-Axis chart, how do I assign data ranges to an axis?
I have created a 2 Y-Axis chart with 12 data ranges. Numerically, the first 8 data ranges belong on the 1st Y Axis, and the last 4 data ranges belong on the 2nd Y Axis, but MS is choosing to put the first six on the left Y Axis and the last six on the right Y Axis. I need for the chart to display 8 on the left Y Axis and 4 on the right. Please advise. Thank you I find it best to ignore the "built-in custom" types because they do what they want. The following process works on either the normal or built-in custom types. Double click a series, and on the Axis tab, select prim...

problem with subtotal
Ive writen an excel spreadsheet which holds a set of unsorted data i one sheet and the same data sorted in another. whenever someone enter data in the unsorted sheet it activates a macro which sorts the data i the other sheet and calculates subtotals on a certain field. M problem is that when the macro updates the sorted sheet, the gran total field gets pushed further and further away from the sorted rows Whenever I add a row of unsorted data the grand total field looks lik it is pushed down 4 or 5 rows in the sorted data. At the moment, th sorted data takes up a page, then there is a page o...

outlook crashes #2
when replying to an email I attempt to add text to the body of the email and Outlook crashes with the following message "Outlook.exe has generated errors and will be closed" does anyone know of a solution to this issue?? I can add text before the header but not in the body. Thanks ...

Domain Trust to 2 domains with the same NetBIOS name
Hi, I am banging my head against the wall with this one and really need some help.... Scenario:- I have 3 AD domains as follows:- Domain 1 AD namespace - uk.company.local netbios domain name = ukcompany 2003 finctional forest and domain Domain 2 AD namespace - france.company.local netbios domain name = company **same as domain 3 2000 finctional forest and domain Domain 3 AD namespace - spain.company.local netbios domain name - company **same as domain 2 2000 finctional forest and domain Is there any way that Domain 1 can create a Trust to domain 2 and domain 3 ...

Need a little Macro ... #2
Excel 2000 ... I have several WorkBooks from 2003 where many TabSheet Names contain "2003". For those TabSheets that do contain "2003" ... I would like a Macro that would look at TabSheet Name & if name contains "2003" change it to "2004". As always ... Thank you to the many Excel Magicians that support this board ... Kha Press ALT+F11, Insert > Module, insert the code below, and then run the macro: Sub Change2003() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets With ws If .Name Like "*2003*" Then ...

formula for a column #2
I'm working on a daily worksheet where all the values for a column will be the same ex. L2/C2, L3/C3, L4/C4 How do I set it up? Assuming the column you want to compute the values is in say, col M Put in M2: =IF(C2=0,"",L2/C2) and press ENTER Point the cursor to the bottom right corner of M2 (cursor will turn into a "black cross") Drag to fill as far down as required .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "msdobe" <msdobe@discussions.microsoft.com> wrote in message news:529B3703-9D9A-4560-80D...

Hiding Columns #2
I have a spreadsheet where 5 identical sheets feed into a summary sheet. The columns are dates where people enter the time that they have spent on a certain job. In the summary sheet I have a formula adding the corresponding cell in each of the sheets. I was wondering if anybody knew how I could hide a column on the summary sheet only if the formula result is zero from the data coming from the 5 other sheets. Thanks, Jane And if you really meant columns, you could use a macro: Option Explicit Sub testme01() Dim iCol As Long Dim wks As Worksheet Set wks = Worksheets(...

Moving Outlook Settings #2
Hi Just got a Tablet and would like to move all my Outlook 2003 settings from my desktop to the tablet, signature file, rules, folders etc. I know that the .pst file moves some stuff, but not sure about actual settings. Thanks in advance Irene -- All outgoing messages scanned with Norton Antivirus 2004 Irene Corb Gaffigan, CRS Principal Broker Farm Town Realty, LLC e-PRO Certified Internet Professional Allen F Hainge CyberStar (TM) 231 Depot Street Proctorsville, VT 05153 Toll Free: (800) 659-1819 Cell: (802) 353-1983 Office : (802) 226-8022 Fax: (802) 706-1000 E-M...

Exchange 2003 OWA shared calendar access #2
Hi We are using Exchange 2003 with both OWA and outlook 2003 clients. I have set the permissions on everyones calendar so everybody has reviewer access to each other calendars. All calendars can be opened when using Outlook 2003. However when using OWA only some calendars can be opened and others are stating "You are not authorized to view this page" when trying to open them even though the permissions have been set to access them. I am trying to access them by typing http://servername/exchange/username/calendar Any ideas would be greatfully received Colin ...

Budgeting Maint. Items #2
My questions is, how do you budget for things such as "car repair" or other items that you are not sure of when you will need to spend the money or how much will need to be spent; however, you are interested in keeping track of how much money has accrued. For example, I budget $100.00/month on Car Repair. This money may not be used for several months; however, I need to know how much I have saved for car repair when a problem does occurr. In six months, I should be able to see that $600.00 in my savings account is dedicated to Car Repair. I haven't found a way to do this ...

Cash requirements report #2
Hi everyone Is it possible to print the Vendor Cash Requirement report in the original currency rather than in the functionnal currency? Thanks Hi Patrick, We are using Version 8 and without doing some report modifications I don't think there is any way to get that report in the original currency. (that would make too much sense !!) This is just one example of where in my humble opinion Microsoft misses the boat when dealing with Multi-Currency. From an operations standpoint you most often want to see the Originating currency amounts because that is the currency of the actual t...

Comparing 2 speadsheets
Hello! I have 2 spreadsheets of data (they are lists of attorneys with their name, phone, address etc..) and I want to check to see if they have the same information, or if one is diferent from the other and what fields, if any, are different. Is there an easy way to accomplish this? Thanks for any help you can provide. Hi, The easiest & quickest way I know for doing a "one off" check (if the sheets are small or expected to be very similar eg both sorted alphabetically etc) is to insert a new spreadsheet in the file (or in one of the files if the 2 sheets are in separate ...

Email does not automatically download #2
When I start-up, am online or click Send/Recieve, Outlook downloads the email automatically. When the computer is in the ppower save mode, it does not download even though the "al Accounts" and "offline" schedules are set for 10 download every 10 min and the scheduled download is not disabled. The email accounts are POP3 accounts and I am on a cable system. I am using XP Pro and Outlook 2003. How do I get the system to automatically download when I am out so I do not exceed my ISP mailbox limit and lose messages? ...

Physical Inventory #2
Another one! How best to input new quantities after doing a physical inventory. OK, you go to Manager>Inventory>Physical Inventory and select "Generate for all items..." and select "All items". After hafing generated the items list, how do you input the new count for the items? Is manual entry the only way? Thanks, Note the "Import File" button... Create a new Physical inventory - Blank, for manual entry Generate a CSV file with lookup code, quantity , and Date counted (optional). This is the type of content that most handheld scanners will gene...

cannot delete messages #2
I am unable to delete messages in my inbox. Whether I hilite and hit the delete key or try dragging to delete file they won't delete. When shutting down outlook an error message pops up saying" some items could not be deleted. Now up to 190 messages in my inbox and counting.....any suggestions out there..... ...

Jet 2.0 dB corruption under Vista
Old VB 3.0 app using Jet 2.0 engine runs fine under very OS from 3.1 to XP but hangs then corrupts DB under Vista Home Premium. All running locally - not network shared, so that KB article doesn't apply (seems to be for 4.0 anyway). Any thoughts or is this the final nail in the coffin for Vista? It came pre-installed on the laptop, and I've had just about enough of the problems caused by this OS and might upgrade to XP... On Wed, 16 Jan 2008 20:31:31 -0700, "V Green" <vanceg@nowhere.net> wrote: >Old VB 3.0 app using Jet 2.0 engine runs fine >under very OS f...