+ Reply to Thread
Results 1 to 6 of 6

Help needed! Identifying duplicate values with functions

  1. #1
    Registered User
    Join Date
    01-14-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    4

    Help needed! Identifying duplicate values with functions

    Hey guys, I am really stuck so any help would be hugely appreciated.

    I am creating an inventory model for work and have created a dashboard where you can choose a part number and the availability of that part shows up and then you can enter the number of items ordered. Once the user hits enter, the order goes into a purchase log with the time and date of the order.

    Here is where I am having trouble... I want to subtract the purchase log orders from the stock levels for each part number which are displayed in various sheets. I can do that when a part has only entered the log once, but when there is more than one transaction for the same part number I canít seem to solve the issue. I tried an index and match function but that simply returns the first transaction for each part number.

    Please refer the the attached example where I would like to adjust the stock level in the part number sheets.

    Again, any help would be hugely appreciated!

    Thanks,

    PdubNZL
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Help needed! Identifying duplicate values with functions

    Maybe
    in Apples Part Number!C2
    =SUMIF('Example Purchase Log'!D$2:D$5,A2,'Example Purchase Log'!B$2:B$5)
    and copy down the column
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Help needed! Identifying duplicate values with functions

    Hi

    Apples Part Number!C2: =SUMIF('Example Purchase Log'!D:D,'Apples Part Number'!A2,'Example Purchase Log'!B:B)

    This does assume that the part number is unique for the Item. if this is not the case, then you could use a sumproduct formula to do the combination of part number and item.

    HTH

    rylo

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    Re: Help needed! Identifying duplicate values with functions

    How about pivot table?

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    01-14-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Help needed! Identifying duplicate values with functions

    Hey guys,

    Thanks you so much for the help. Problem solved!

    I cant thank you enough

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

    Re: Help needed! Identifying duplicate values with functions

    glad I could help.

    If your question is solved, please mark it as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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