+ Reply to Thread
Results 1 to 1 of 1

Reconciling Two Spreadsheets with Repeating Values

  1. #1
    Registered User
    Join Date
    07-31-2019
    Location
    Austin, Texas
    MS-Off Ver
    Professional 2010
    Posts
    4

    Question Reconciling Two Spreadsheets with Repeating Values

    Hi Excel Forum Community! I just joined and this is my first post (and my reason for officially joining!). I have been trying to figure out a formula to reconcile two spreadsheets: One source worksheet (named 'products') with product that should be shipped back to us with a second spreadsheet (named 'received') that contains what we actually received.

    Spreadsheet 'products' has the following columns:
    return-date:
    The date the product was "ordered" and in queue to be sent to me
    sku: The identifying unit/title for each product - these are usually duplicated, which is where my problem lies
    quantity: quantity
    serial-number: Unique serial for each sku

    Spreadsheet 'received' has the following columns:
    order-date:
    The date the sku was added to an order to be shipped back
    order-id: The order the sku was added to
    shipment-date: The date the sku was shipped back
    sku: The identifying unit/title for each product - these are usually duplicated, which is where my problem lies
    quantity: quantity
    received-date: The date I actually received it back
    serial-number: The serial number I captured when the product arrived

    I need to add a column to 'products' that checks if that specific line was received or at least shipped back if not received. I was able to write a VLOOKUP/IFERROR formula that checked for the serial being present on the 'received' worksheet. That part is easy. But when I got to the products that were not yet received, checking to see if they were sent is where I ran into issues. Since the products are duplicated, my formula was checking to see if any of the unreceived product from 'products' has been shipped back and would not account for products already assigned to a previous line from 'products'.

    TL;DR: I am expecting 10 of Product A. Two units have been received and match serials. Of the 8 Product A unaccounted for, even though only 5 others have been sent back, my formula returns a value for all 8.

    Current Formula: =IFERROR((VLOOKUP(D2,received!G:G,1,FALSE)),IFERROR(VLOOKUP(B2,received!D:D,1,FALSE),"Not Found"))
    Attached Files Attached Files

+ 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. Sum values in a table by Date and repeating values down column A
    By Motox in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-07-2018, 05:15 AM
  2. [SOLVED] How to find non-repeating values in Column B for repeating values in Column A
    By jdegeorge in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-16-2017, 01:43 PM
  3. [SOLVED] Reconciling macro
    By mimijo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-07-2015, 12:07 PM
  4. Reconciling 2 Spreadsheets
    By xuetengwang in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2013, 08:12 PM
  5. creating a repeating formula that doesn't show repeating values
    By cybershot in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-09-2010, 06:12 PM
  6. Reconciling Two Lists
    By DAlter in forum Excel General
    Replies: 4
    Last Post: 07-27-2009, 12:35 PM
  7. Replies: 3
    Last Post: 06-21-2006, 10:10 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