+ Reply to Thread
Results 1 to 3 of 3

Find future On Hand Quantity for duplicate values

  1. #1
    Forum Contributor
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    101

    Find future On Hand Quantity for duplicate values

    Hey guys,

    Happy New Year!

    I am trying to calculate "Remaining OH" qtys based on Total OH (On Hand) and Future Usage of parts that repeat themselves in a column for future dates. In the attached Sample Data, Column A has part numbers and some of them repeat themselves for future dates in column D (Sorted earliest to latest). Column B and C are Current OH and Future Requirement values respectively. Column E is where the calculation should happen where I am expecting the see how much I will have left after each part has been used on a specific date. Highlighted are the parts that repeat more than once in the table. For Ex: Part # 102 starts with an OH qty of 10 and 5 of which are being used on 2/15/2021 resulting in 5 as Remaining OH Qty. 8 of the same part# 102 are needed on 12/14/2021, hence the Remaining OH should be -3 (5-8). It repeats again third time in the table where 5 are required and the resulting Remaining OH Qty should be -8 (-3-5).

    In my original file, I've had luck with solving the issue if the part# repeats only twice. Anything more than that does not return the expected value. I've manually entered the expected results in Column E. Feel free to add a column to enter formula and return expected values.

    If the explanation is not clear, Please feel free to shoot your questions.

    Thanks in advance

    VJ
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Find future On Hand Quantity for duplicate values

    Try at E2

    =IFERROR(LOOKUP(9^9,E$1:E1/(A$1:A1=A2)),B2)-C2
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    101

    Re: Find future On Hand Quantity for duplicate values

    Hi Bo_Ry,

    This is amazing. It worked. I just figured out a way using Index and match but your method is simple. If you don't mind, could you explain what Lookup(9^9) does? I'm new to this concept.

    VJ

+ 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. [SOLVED] Formula to recalculate on hand quantity
    By RSa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2021, 06:19 PM
  2. [SOLVED] find the future values within a range
    By WalaVita in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-05-2019, 03:31 PM
  3. Update quantity on hand of products and raw material
    By controlrex in forum Excel General
    Replies: 3
    Last Post: 04-21-2016, 03:54 PM
  4. Finding price with duplicate Quantity values
    By grantjw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-02-2015, 09:06 AM
  5. [SOLVED] Running total of Quantity on Hand Vs Sales Vs Quantity to Produce
    By jespo1351 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2015, 07:34 AM
  6. [SOLVED] Find the most occurring values in Column A and consolidating each value's quantity
    By savagebabs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2014, 03:18 PM
  7. [SOLVED] How do I find duplicate rows, add quantity field & retain one reco
    By Pearl in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-23-2006, 04:15 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1