+ Reply to Thread
Results 1 to 4 of 4

Pull values once and add the qty's for each instance

  1. #1
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Pull values once and add the qty's for each instance

    Reposting from original post...........I'm okay at Excel with formulas and VBA but this one I just don't know where to start. So, I知 needing a formula that will extract one time for a part number that may have many instances but add the quantities for each instance. In the attached image I have the table "Parts and Quantities to Re-Order per SKU". A SKU can have up to 4 different part numbers. Columns W, Y, AA and AC are the part numbers required for each sku (sku not shown on far left) and the quantity to be deducted from "In-House" inventory just to the right of each part #.

    In the Blue table, "Inventory to Order Per Part#", I have manually added each part number and its results down to line 8 just for demonstration of the result I知 looking for. The Blue table is where I need the formulas to extract the data from the "Parts and Quantities to Re-Order per SKU" table.

    The objective is to get each part # in the blue table only listed once but to add the quantities required from each instance of the part number. A part number can be in any of the 4 columns depending on the sku so each part# would have to be looked at in all 4 part # columns. This workflow sheet goes clear down to row 228 and some weeks we can have almost all of the rows with a sku.

    A formulas are preferred, rather than VBA, so that it will update as I enter sku's.

    Hopefully the workbook uploaded.
    Attached Files Attached Files
    Last edited by Andy C.; 09-14-2021 at 05:02 PM.

  2. #2
    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,917

    Re: Pull values once and add the qty's for each instance

    Quote Originally Posted by Andy C. View Post
    Reposting from original post...........I'm okay at Excel with formulas and VBA but this one I just don't know where to start. So, I知 needing a formula that will extract one time for a part number that may have many instances but add the quantities for each instance. In the attached image I have the table "Parts and Quantities to Re-Order per SKU". A SKU can have up to 4 different part numbers. Columns W, Y, AA and AC are the part numbers required for each sku (sku not shown on far left) and the quantity to be deducted from "In-House" inventory just to the right of each part #.

    In the Blue table, "Inventory to Order Per Part#", I have manually added each part number and its results down to line 8 just for demonstration of the result I知 looking for. The Blue table is where I need the formulas to extract the data from the "Parts and Quantities to Re-Order per SKU" table.

    The objective is to get each part # in the blue table only listed once but to add the quantities required from each instance of the part number. A part number can be in any of the 4 columns depending on the sku so each part# would have to be looked at in all 4 part # columns. This workflow sheet goes clear down to row 228 and some weeks we can have almost all of the rows with a sku.

    A formulas are preferred, rather than VBA, so that it will update as I enter sku's.

    Hopefully the workbook uploaded.
    Just to assure you that your initial post is still there.
    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

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Pull values once and add the qty's for each instance

    assuming XL2010, whilst not ideal (given INDIRECT) you could use something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the above would return all unique Parts and the respective (cumulative) Quantity from the "Parts & Quantities To Re-Order per SKU" table

  4. #4
    Registered User
    Join Date
    09-25-2016
    Location
    Florida
    MS-Off Ver
    365
    Posts
    51

    Re: Pull values once and add the qty's for each instance

    Quote Originally Posted by XLent View Post
    assuming XL2010, whilst not ideal (given INDIRECT) you could use something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the above would return all unique Parts and the respective (cumulative) Quantity from the "Parts & Quantities To Re-Order per SKU" table
    Your formula worked like a champ! Thanks, Flame & Xlent

+ 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. Pull each instance of a Set Criteria/Value in a coulmn
    By Cbirchfi in forum Excel General
    Replies: 3
    Last Post: 10-12-2016, 10:13 AM
  2. Replies: 1
    Last Post: 04-20-2015, 03:18 PM
  3. Replies: 3
    Last Post: 07-15-2014, 09:50 AM
  4. [SOLVED] Highlighting all duplicate values except first instance
    By Ankka in forum Excel General
    Replies: 5
    Last Post: 05-22-2014, 04:39 AM
  5. Identify a range, pull first instance of a symbol, repeat
    By Jbm444 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2010, 07:01 PM
  6. Pull every instance INDEX/MATCH criteria into consecutive rows
    By PowerSchoolDude in forum Excel General
    Replies: 4
    Last Post: 01-13-2010, 06:42 PM

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