Adding Sum

I made a query report with unique records to display,I don't want repeating 
item displayed in the report. 
What should I do to sum the other qty delivered in a single item.

Item                  Qty            Date
Pork                  50            08/01/05
                        60            08/10/05
                        20            08/15/05

should be

Item                  Qty                     Date
Pork                   130          08/01/05 - 08/15/05

thanks & Godspeed.
0
nono6537 (2)
8/3/2005 1:51:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
906 Views

Similar Articles

[PageSpeed] 19

One way

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim dteMax As Date
Dim dteMin As Date
Dim nAmount As Double
Dim sCat As String
Dim iRow As Long

    Columns("D:F").Insert
    iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
    dteMax = Range("C2").Value
    dteMin = Range("C2").Value
    nAmount = Range("B2").Value
    sCat = Range("A2").Value
    iRow = 1
    For i = 3 To iLastRow
        If Cells(i, "A").Value = "" Then
            nAmount = nAmount + Cells(i, "B").Value
            If Cells(i, "C").Value > dteMax Then
                dteMax = Cells(i, "C").Value
            ElseIf Cells(i, "C").Value < dteMin Then
                dteMin = Cells(i, "C").Value
            End If
        Else
            Cells(iRow, "D").Value = sCat
            Cells(iRow, "E").Value = nAmount
            Cells(iRow, "F") = Format(dteMin, "mm/dd/yy") & " - " & _
                              Format(dteMax, "mm/dd/yy")
            sCat = Cells(i, "A").Value
            nAmount = 0
            dteMax = Cells(i, "C").Value
            dteMin = Cells(i, "C").Value
            iRow = iRow + 1
        End If
    Next i
    Cells(iRow, "D").Value = sCat
    Cells(iRow, "E").Value = nAmount
    Cells(iRow, "F") = Format(dteMin, "mm/dd/yy") & " - " & _
                       Format(dteMax, "mm/dd/yy")

End Sub



-- 
 HTH

Bob Phillips

"nono" <nono@discussions.microsoft.com> wrote in message
news:0B5D4B71-CF5D-4133-A2A3-0D28AC40FA00@microsoft.com...
> I made a query report with unique records to display,I don't want
repeating
> item displayed in the report.
> What should I do to sum the other qty delivered in a single item.
>
> Item                  Qty            Date
> Pork                  50            08/01/05
>                         60            08/10/05
>                         20            08/15/05
>
> should be
>
> Item                  Qty                     Date
> Pork                   130          08/01/05 - 08/15/05
>
> thanks & Godspeed.


0
bob.phillips1 (6510)
8/3/2005 8:20:05 AM
Reply:

Similar Artilces:

sum based on three variables #2
Hello Group This was SENT yesterday but hasn't shown up on the newsgroup? I have created a database of labels we send to outside fillers. What I would like to do (without using autofilter or pivot tables) is sum the number of labels sent to a filler by month. So for example range a1:a200 = date with a short date in range b1:b200 (mmm - I thought it would be easier to calculate), range c1:c200 = filler (we have five), range d1:d200 = label description and finally range e1:e200 is the quantity of labels sent. So a running total could be maintained by Month, Filler and total of labels se...

Adding Attachments to the KB (Knowledge Base)
I have a customer that needs the ability to add attachments (files, docs, xls, etc.) to the KB. Is there a way this can be done easily? afraid not you cannot add notes to a kb article. You can add comments but these do not let you add attachments I hope this helps Andreas Donaubauer MCP seit 1999, MCSE, MCSA MVP f�r CRM andreas[at]donaubauer.com www.crmfaq.de "michaelcwest" <michaelcwest@discussions.microsoft.com> schrieb im Newsbeitrag news:F0C44103-B397-4AEA-81ED-049F20FE3507@microsoft.com... > I have a customer that needs the ability to add attachments (files, doc...

Checking for the existence of a dynamically added style
I have a custom control that I would like to have add style rules to the Page that contains it. I know how to do this, using the following method: Me.Page.Header.StyleSheet.CreateStyleRule However, I only want to add the style rule once, since it is the same for every instance of the custom control. When adding scripts, I would simply use the following two statements to add a script and check whether it was already added: Me.Page.ClientScript.RegisterClientScriptBlock Me.Page.ClientScript.IsClientScriptBlockRegistered But I could not find anything like this for stylesheets...

[P2007]
Hi, I have a PM user who has just changed the login account password in AD and after that he is no longer able to login to PWA, an access denied message will show up instead. Even if I assign him as an administrator, he is still getting the same problem. He has no issue using Project Professional (with his account) to connect to the project server, it is just the PWA that he is unable to access. Did anyone encountered the same issue before? Regards Godrid Godrid: Most likely his desktop is sending the incorrect credentials. Verify that the user didn't save the passw...

Macro to change the PivotField to sum
I'm trying to find/creat a macro that will automatically change the Field in my pivottable to Sum. The problem that I'm running into is when I have have a new field name or a new pivottable name. I can creat one where the pivottable and the PivotField are always the same but that really doesn't help me. Any help would be greatly appreciated. Thanks Vick Debra Dalgleish has an addin that you may like. http://www.contextures.com/xlPivotAddIn02.html The code is unprotected, so you could just extract that portion and include it your macro if you want. Vick wrote: > >...

Adding an average line to a chart
Hot a chart with numerous columns (using the 3D standard one); want to add a moving average line of the data in the graph. How can I do this - do I need to change the chart type? -- DJ You can't do this with a 3D type chart, but that's okay, because you shouldn't use a 3D chart anyway. They distort and even misrepresent the data, and people misread and misinterpret them all the time. Here's how to add such a line to a regular column chart: http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. ht...

Total the sum in a cell
I need to get the total sum from B1:B26 and get it to total in B27. What is the formula? Hi Misty a copy of options in cell B27 type =SUM(B1:B26) or click in cell B27 and click the autosum icon on the toolbar (the funny E shaped one) Cheers JulieD "Misty" <Misty@discussions.microsoft.com> wrote in message news:0EC468DA-81F7-4189-B4BD-4EE43B1BB17D@microsoft.com... >I need to get the total sum from B1:B26 and get it to total in B27. What >is > the formula? =SUM(B1:B26) Regards, Peo Sjoblom "Misty" wrote: > I need to get the total sum from...

SUM REPHRASED
select a cell with a number, then (ctrl) select other cells with numbers... the sum of the cells should display at the bottom, with each additional cell that you select... how do i set this up/do this??? Right-click on Status Bar and select Sum from the list. If you don't see the Status Bar, go to View>Status Bar. Gord Dibben MS Excel MVP On Tue, 6 May 2008 09:54:03 -0700, acp20770 <acp20770@discussions.microsoft.com> wrote: >select a cell with a number, then (ctrl) select other cells with numbers... >the sum of the cells should display at the bottom, with each add...

Adding a second NIC for DPM Backups
We currently have DPM 2007 deployed and it is working fine on our 192.168.x.x network. We have added a second NIC to our DPM server and connected this NIC to a seperate 172.18.2.x network that we are calling the "Backup Network". We would like all DPM backup traffic to use the 172.18.2.x network to transmit backup data to DPM, which still using the primary 192.168.x.x NIC to be able to communicate with AD and so on. What is the easiest way to configure DPM or the server DPM clients to have it transmit DPM data over the 172.18.2.x network rather than using the 192.168...

adding combo to toolbar
Hi, I want to put a combo box on a toolbar. The following is my code. The combobox showed, but the size is not enlarged, it still the same size as other toolbar button. Can any one tell me how to fix this? Thanks int nToolBtnCnt = pToolBar->GetToolBarCtrl().GetButtonCount(); //convert last button to a seperator and get its position RECT lastBtnRect; UINT nID, nStyle; int nImage; pToolBar->GetButtonInfo( nToolBtnCnt-1, nID, nStyle, nImage ); pToolBar->SetButtonInfo( nToolBtnCnt-1, nID, TBBS_SEPARATOR, 160 ); pToolBar->GetItemRect( nToolBtnCnt-1, &lastBtnRect ); //expand the...

PV, Annuity, Lump Sum
I am looking at John Walkenbach's Excel 2007 Formulas. On page 302, he has the result of a formula that I think is an error. I am not confident enough to be sure. In fact, my answer doesn't look right. The formula is based on the following scenario: Your brother-in-law wants you to invest in his carpet cleaning business. If you'll invest $50,000 now, he will pay you $200 per month for five years and also pay you $60,000 at the end of the five years. Are you making a good investment? Here are the arguments: Rate: 0.8% Period: 60 Payment: $200 FV: $60,000 Type: 1 Walkenbach's...

Unable to Sum cells in Excel
Hello, I am having issues trying to use the SUM function to autosum a couple of cells that have If functions within them. I am trying to do the following: In Q12 I am trying to get the Sum of N12, O12 and P12. I have only tried =SUM(N12:P12) as the function formatting the cell as text, general, numeric etc but the total always ends up being 0.00, I never get an addition of the values in the other cells. Each of the N, O and P cells has the following function within it, =IF(F12="","",IF(F12>=90%,"5",IF(F12>=85%,"4",IF (F12>=78%,"...

Despite formula I use (for example SUM) result is always zero.
Despite formula I use (for example the most simple SUM) it always shows me zero result even it should show a lot more. Values in other cells (cells that I sum) are in number format. Why this happens?? It doesn't matter whether the cells are FORMATTED to DISPLAY as number; what matters is whether the CONTENTS are numbers. My guess is that they are text that might look like a number. If one of your numbers is in A2, what do the formulae =ISTEXT(A2) and =ISNUMBER(A2) show? -- David Biddulph "Anida" <Anida@discussions.microsoft.com> wrote in message news:...

If sign of sum is negative change to positive
I am using a formula that may produce positive or negative results. Once it has produced the sum the sign is immaterial. I need the result to add as a positive. I need something that says if the sum is less than 0 change the result to positive. How do I do that? Hi, =ABS(yoursum) Mike "BigR" wrote: > I am using a formula that may produce positive or negative results. Once it > has produced the sum the sign is immaterial. I need the result to add as a > positive. > I need something that says if the sum is less than 0 change the result to >...

Adding new form to a MFC.exe application (how?)
Hi, I moved from VC6 to VC7.1 and most of the IDE has changed. I used to be able to add a new form bia RH clicking on the project (from ClassView) and then selecting new Form. Anyone knows how I can add a new form to my papplication under VC71? mtia I was not aware that 'forms' existed in VS6. Dialogs, yes. VS7 was designed by an idiot, who had no idea how people actually program. It is a prime example of incompetent design coupled with irresponsible design reviews. joe On Sat, 22 Oct 2005 13:36:19 +0000 (UTC), "E.T. Grey" <nebula@alpha-centauri.com> ...

Complex conditional summing
I'm trying to use an array formula SUM with nested IFs to accomplish some complex conditional sums. If I use "COUNT" as the outermost function I get a number that appears to be correctly counting the target cells. I've added a nested "IF(ISNUMBER(range))" to the count function and verified that it returns the same number as the function without it, so it would seem all the selected cells are, in fact, numbers. I've even calculated a separate array formula of =AND(ISNUMBER (range)) which returns TRUE, again to confirm all the cells in the range contain nu...

Adding a bitmap to the ListCtrl form view window
Hi fellow programmers, I would like a web address or code except demonstrating "how to" dynamically open a window bitmap file and insert into a CImageList object and then use the CListCtrl box to display the bitmap in a report view. I do not want to pre-define the file names, as a resource ID. The bitmap files are 140 x 70 pixels. The CImageList::Add function requires a CBitmap*... but the CBitmap class does not allow you to easily open a bitmap file. ...

Breakdown Accounts Sum Cannot Equal Zero
This is our first payroll of the New Year and when Accounting posted payroll, the posting Journal threw a bunch of errors saying that Breakdown accounts sum cannot be zero. We have a new head of accounting and this is her first New Year, so we may have forgotten a step before posting. I didn't really see anything in the KB that pertained to this problem, so any help would be appreciated. Is a Quick Journal being used to post payroll? This is often done if a journal entry is entered to 'post payroll' in order to get the transactions into the check register. When se...

why doesn't sum add time
I have a starting time in cell A1 of 5:15, and in B1 I place :46, and I put sum of A1+B1 in column A3. I don't get 6:01, but instead I get #VALUE! What am I doing wrong? Hi have you entered your times really as time values?. What do the formulas =ISNUMBER(A1) and =ISNUMBER(B1) rreturn? -- Regards Frank Kabel Frankfurt, Germany "jvoortman" <jvoortman@canada.com> schrieb im Newsbeitrag news:460f01c4906a$30243970$a501280a@phx.gbl... > I have a starting time in cell A1 of 5:15, and in B1 I > place :46, and I put sum of A1+B1 in column A3. I don't > get 6:01, b...

Autocomplete emptying after adding contacts
Hi, I'm having an issue with Microsoft Outlook 2007 for a few of the users on my network. When new contacts are added to their contact list, their autocomplete list emptys until they repopulate it. For a solution I've had them download and use NK2Viewer, but I was wondering if their was anything anybody could think of to help me resolve this issue. It's an intermittent issue, and it's one I myself don't have. Sometimes when new contacts are dragged to their contact folder they no longer have their autocomplete and sometimes they do. My first thought ...

adding a business contact to an existing account
Whenever I try to add a new business contact to an existing account, I get the following message: "The form required to view this message cannot be displayed. Contact your administrator." BCM sometimes has what's known as a "forms cache" problem. Clearing the cache usually fixes it. Choose Tools | Options | Other | Advanced Options | Custom Forms, then click Manage Forms, then Clear Cache. If simply clearing the cache doesn't fix the issue, try the other steps detailed at http://support.microsoft.com/default.aspx?scid=kb;en-us;836558 The bulk of the forms ...

how to: if a value is entered in one row, then associated data is cummulatively added to a 'total' on another sheet ?
Excel Version : 12.2.3 OS X 10.6.2 Intel Hi =97 I am very new to Excel, and am learning a lot, but have found it difficult to work out a new problem. I am trying to nut out a formula to do the following (I have searched all over, but realise I don't really know what my search terms should be). For arguments sake, I have two worksheets, A and B. Worksheet A is used as a meta-summary of what will happen elsewhere in the Workbook. Worksheet B is where the raw data is entered (as it will in C, D, E, etc, in the future). I have created a drop down list of categories for use in Wo...

Summing monthly data from weekly data input, with a twist
I'm hoping an Excel wizard can help me... I know enough about Excel to be dangerous, but this issue is giving me a migraine. I have weekly data organized by row with the first cell of the row indicating the week (i.e. Jan 3, 2008). Subsequent week dates are 7 days apart through the remainder of the year. The trick is...I want to be able to sum a month's data and display it in a "Current Month" column, based on the current date, so that only the current month's data displays. For example, in July 2008, only the sum of available data for July will display. Once ...

adding one same data to each of four other data points in column c
My chart has four seperate options, I am wanting to add one same data to each of the four options to have a total amount, including the one like data, to each of the four options. I want this chart to be a column chart. ...

Change email in AD problem
We have a small group and the people before me didn't think very far ahead. I have a 2 users named bill and so the first one got the email bill@coname.com and the second one got billf@coname.com. There is lots of confusion now and bill gets emails for billf here is the problem bill is part time and billf is full time and has time sensitive information that gets sent to bill. To fix the problem would be to change bill to billc. Sounds easy enough but when I made the change in AD: full email smtp alias Now if you email bill from inside the network it still goes to billc and if you emai...