+ Reply to Thread
Results 1 to 2 of 2

Inventory trying to identify how many kits I can make when comparing multiple components

  1. #1
    Registered User
    Join Date
    12-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Inventory trying to identify how many kits I can make when comparing multiple components

    Morning All,
    Currently am working on an excel sheet that has our product inventory in it. Many of the parts we put together are kitted, i.e they require several other items to make them. I have an excel sheet that lists the part #'s and the qty's required to make them, and I have a Vlookup that tells me the qty of each that I have in stock. What I want to do is have it tell me how many complete kits I could build given the stock that I have on hand. Any ideas on the best way to do this?

    Column A Column B Column C Column D
    Main Part Qty Required to build Main part Qty on Hand Desired result total qty of main part that can be built,
    Component A 1 10 in this example the answer should be 7 as component B wouldn't
    Component B 2 15 have enough stock to produce more.
    Component C 1 25


    Any suggestions would be greatly appreciated, thanks

  2. #2
    Registered User
    Join Date
    12-13-2013
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Inventory trying to identify how many kits I can make when comparing multiple componen

    That format didn't come out clearly when I posted the thread.

    Column A, List the main part and the items required to build it
    Column B has the Qtys of each of the subcomponents to build the main item
    Column C has qty of each item that I have on hand
    Column D, My goal is to have this return the Total qty of the main item that I could build given the subcomponents I have on hand

    Assuming the main part requires
    Component A qty 1 Currently qty 10 on hand
    Component B qty 2 Currently qty 15 on hand
    Component C qty 1 Currently qty 25 on hand

    Given this example I should be able to make 7 of the main part as I don't have enough of component B to build more.

    Any idea on what formula I would use to get this result?

+ 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. Replies: 8
    Last Post: 04-04-2016, 08:03 AM
  2. Replies: 7
    Last Post: 06-13-2013, 06:56 AM
  3. [SOLVED] Comparing 2 Spreadsheets to Identify duplicate rows
    By eagle670 in forum Excel General
    Replies: 6
    Last Post: 04-09-2013, 10:13 AM
  4. [SOLVED] Comparing 2 Lists to Identify Differences
    By ORRACLE1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-10-2012, 02:26 PM
  5. Replies: 0
    Last Post: 03-13-2005, 02:06 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