+ Reply to Thread
Results 1 to 8 of 8

Can I have Excel count up my stock requirements?

  1. #1
    Registered User
    Join Date
    02-21-2007
    Location
    Yorkshire, United Kingdom
    MS-Off Ver
    Office for Mac 2011
    Posts
    25

    Cool Can I have Excel count up my stock requirements?

    Good Morning Folks

    Each morning I pull a download from my website of what has been sold.
    This in on a line item basis, where column A gives the product code, column B gives the qty sold. Then the next line moves onto the next code (if there are no more products on that order, it moves onto the next order)

    (Please see attached sheet - its probably easier?!)

    What I need to do is Excel add up how many of each product code has been sold, so I can double check I have the stock to fulfil the order, if not I can have it delivered that day.

    I used to do this using a COUNTIF function to count the product codes (and then filtering the duplicate rows of product codes) however this is only counting how many times the code appears, and doesn't account for when someone orders 3 of that item on a given row.

    Can anyone help me with this please? I've tied myself up in knots so far!

    Kindest Regards
    Mark
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi,
    does a Pivot Table help ( see attached)?

    Cheers
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-21-2007
    Location
    Yorkshire, United Kingdom
    MS-Off Ver
    Office for Mac 2011
    Posts
    25
    Hi

    Thanks for that, it certainly does help! I just need to learn how to use them now!

    Kind Regards
    Mark

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Here is a good place to start..
    But be aware that there are other possibilities

  5. #5
    Registered User
    Join Date
    02-21-2007
    Location
    Yorkshire, United Kingdom
    MS-Off Ver
    Office for Mac 2011
    Posts
    25
    Hi

    So I finally got round to sorting this out, and have a Pivot Table setup which works a treat.

    Now what I'm trying to do is create a Macro which does all this - so the staff in my packing room can download a sales report and just hit a keyboard shortcut and they'll be presented with this pivot table from which to compile an order.

    I have recorded a macro in which I delete all unrequired columns, and created the pivot table. However when I run it on a sheet I get the error:

    Runtime Error '1004'
    AddFields method of PivotTable class failed

    with the option to debug or end.

    This is way out of my league - is creating a PivotTable via Macro even possible?

    Kind Regards
    Mark

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Can you post the macro you recorded ( or the sheet including the macro)?

  7. #7
    Registered User
    Join Date
    02-21-2007
    Location
    Yorkshire, United Kingdom
    MS-Off Ver
    Office for Mac 2011
    Posts
    25
    Hi arthurbr

    Sure, here is the code:

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 29/09/2008 by Mark Norton
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Columns("A:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:AT").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:C").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "C1:C3").CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("WarehouseLocation"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
    "WarehouseLocation", "SKU")
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Amount")
    .Orientation = xlDataField
    .Caption = "Sum of Amount"
    .Function = xlSum
    End With
    End Sub


    Many thanks
    Mark

  8. #8
    Registered User
    Join Date
    02-21-2007
    Location
    Yorkshire, United Kingdom
    MS-Off Ver
    Office for Mac 2011
    Posts
    25
    arthurbr

    I think I worked it out. In the code:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "C1:C3")

    the source data should be "A:C" which I have fixed and it now works.
    Many apologies if you have spent time trying to sort this, I'm a little shocked myself that I made it work :-)

    Kindest Regards
    Mark

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Best way to conduct an online voting tournament using Excel?
    By Tony Speroni in forum Excel General
    Replies: 0
    Last Post: 10-14-2007, 04:41 PM
  2. Excel and Web Query Question?
    By gsmonk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2007, 06:21 PM
  3. processing a string by Excel
    By mark_neil2 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-20-2007, 07:35 AM
  4. Excel 2003 Not Excelling in Handling 2000 .xls File
    By pands in forum Excel General
    Replies: 4
    Last Post: 02-27-2007, 05:56 PM
  5. Going insane!
    By kik in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 01-31-2007, 08:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1