+ Reply to Thread
Results 1 to 3 of 3

Macro Not counting all items

  1. 09-19-2015, 09:19 AM #1
    velocitygraphix
    velocitygraphix is offline
    Registered User
    Join Date
    04-23-2013
    Location
    TN, United States
    MS-Off Ver
    Excel 2007
    Posts
    40

    Macro Not counting all items

    Hello guys, I have a script that I run to give us a list of products that have been sold. Its basically an order summary so we know how many items of each product to pull. The script works great unless a customer orders two of the same product. For example, if customer A orders 1 Red, B orders 1 Red and 1 Blue, and C orders 2 Red and 1 Blue, then the script would show 3 red, and 2 Blue….instead of 4 red. It is counting the SKUs and not accounting for the quantity purchased….Any ideas? I have included the script

    Please Login or Register  to view this content.

    Place this test data in A1C1 and run the macro. Make sure to have 2 sheets and the results will be on the second sheet. Notice that everything is counted as one even though there are 2 green items. If anyone could please figure this out that would be great. Thank you!


    Ship To:
    test xxx
    xxx
    xxx
    Order ID: xxx
    Shipping Address:
    test xxx
    sss
    sss3
    Order Date: Sep 19, 2015
    Shipping Service: Standard
    Buyer Name: xxx
    Seller Name: xxxx
    Quantity Product Details Price Total
    1 Test Red
    SKU: Item1
    ASIN: 123445
    Listing ID: 0430PVAJLO3
    Order Item ID: 29378
    Condition: New
    $11.99
    Subtotal: $11.99
    Shipping: $6.99
    Total: $18.98
    ORDER TOTAL: $18.98



    Ship To:
    test xxx
    xxx
    xxx
    Order ID: xxx
    Shipping Address:
    test xxx
    sss
    sss3
    Order Date: Sep 19, 2015
    Shipping Service: Standard
    Buyer Name: xxx
    Seller Name: xxxx
    Quantity Product Details Price Total
    1 Test Blue
    SKU: Item2
    ASIN: 1234567
    Listing ID: 0118MCPABGF
    Order Item ID: 80738
    Condition: New
    $8.99
    Subtotal: $8.99
    Shipping: $5.99
    Total: $14.98
    ORDER TOTAL: $14.98



    Ship To:
    test xxx
    xxx
    xxx
    Order ID: xxx
    Shipping Address:
    test xxx
    sss
    sss3
    Order Date: Sep 19, 2015
    Shipping Service: Standard
    Buyer Name: xxx
    Seller Name: xxxx

    Quantity Product Details Price Total
    2 Test Green
    SKU: Item3
    ASIN: 123456789
    Listing ID: 1102IZVLUCR
    Order Item ID: 54466
    Condition: New
    $10.99
    Subtotal: $10.99
    Shipping: $16.00
    Total: $26.99
    ORDER TOTAL: $26.99
    Reply With QuoteRegister To Reply
  2. 09-19-2015, 04:30 PM #2
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Not counting all items

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  3. 09-19-2015, 04:54 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Macro Not counting all items

    If you would consider a formula approach...
    K
    L
    M
    N
    O
    1
    		 Ship To: Red 1 Test Red
    1
    1
    2
    		 test xxx Blue 1 Test Blue
    1
    1
    3
    		 xxx Green 2 Test Green
    2
    2
    4
    		 xxx
    5
    		 Order ID: xxx

    L1:L3 = your products
    M1=INDEX($K:$K,MATCH("*"&$L1&"*",$K:$K,0)) to pull out the items - if there are more than 1 "item set", I will beed to adjust this
    N1=--LEFT(M1,FIND(" ",M1,1)) to extract the QTY

    OR, you can do both in 1 step...
    O1=--LEFT(INDEX($K:$K,MATCH("*"&$L1&"*",$K:$K,0)),FIND(" ",INDEX($K:$K,MATCH("*"&$L1&"*",$K:$K,0)),1))
    All copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro, Counting Items and moving results to a new table. Help needed
    By mitko007 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-26-2015, 04:49 AM
  2. Macro, Counting Items and moving results to a new table. Help needed
    By mitko007 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-21-2015, 06:25 AM
  3. Counting items based on a list of items
    By jbaumrind in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-01-2015, 12:56 PM
  4. Counting items.
    By Chris TRN in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-04-2013, 01:29 PM
  5. Counting items
    By AMYA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-18-2009, 01:27 PM
  6. Counting the items
    By Tyrone Lopez in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-25-2006, 02:51 AM
  7. Help counting items please
    By Dave \Doc\ Corio in forum Excel General
    Replies: 5
    Last Post: 08-20-2005, 09:05 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1