SOLVED

Formula Help Match Receipt Records with closest previous date

%3CLINGO-SUB%20id%3D%22lingo-sub-1890493%22%20slang%3D%22en-US%22%3EFormula%20Help%20Match%20Receipt%20Records%20with%20closest%20previous%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1890493%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20serious%20help%20as%20I%20have%20tried%20all%20kinds%20of%20different%20formulas%20and%20nothing%20has%20worked.%20I%20have%20three%20sheets%20of%20data.%20The%20first%20sheet%20(we'll%20call%20sheet1)%20is%20a%20receipt%20record%20with%20Part%20Number%2C%20Supplier%20Number%2C%20Date%2C%20Qty%20Received.%20This%20is%20where%20the%20formula%20resides%20that%20I%20am%20having%20issues%20with%20figuring%20out.%20Sheet2%20has%20a%20list%20of%20rejected%20parts%2C%20with%20Part%20Number%2C%20Supplier%20Number%2C%20Date%2C%20Qty%20Rejected.%20I%20am%20trying%20to%20extract%20the%20Rejected%20quantities%20from%20sheet2%20and%20place%20them%20on%20sheet1.%20I%20created%20a%20UID%20by%20Concatenating%20Part%20Number%2C%20Supplier%20Number%20and%20Date%20in%20a%20column%20in%20both%20sheets.%20I%20can%20easily%20pick%20up%20the%20data%20where%20the%20receipts%20in%20sheet%20one%20match%20the%20rejects%20in%20sheet2%20by%20using%20Sumif%20formula.%20Now%20to%20the%20difficult%20part.%20Not%20all%20parts%20rejected%20are%20going%20to%20match%20the%20date%20received%20so%20where%20these%20do%20not%20match%20I%20need%20to%20find%20the%20closest%20previous%20receipt%20record%20Sheet1%20I%20can%20match%20it%20with.%20I%20cant%20use%20where%20data%20is%200%20or%20not%20found%20then%20match%20using%20part%20number%20because%20then%20it%20will%20just%20pull%20everything%20twice.%20I%20could%20spend%20hours%20with%20a%20mile%20long%20post%20on%20everything%20I%20have%20tried.%20Rather%20than%20do%20that%20I%20will%20keep%20it%20short%20and%20listen%20for%20your%20suggestions.%20There%20is%20one%20solution%20and%20I%20know%20the%20first%20one%20most%20will%20want%20to%20post%20here%20which%20may%20work%20but%20involves%20an%20array%20formula%20Cntrl%2BShift%2BEnter%20and%20that%20would%20slow%20the%20work%20book%20down%20dramatically.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1890493%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1890661%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20Match%20Receipt%20Records%20with%20closest%20previous%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1890661%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612943%22%20target%3D%22_blank%22%3E%40heylookitsme%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20it%20possible%20to%20provide%20small%20sample%20file%2C%20just%20few%20records%20on%20each%20sheet%3B%20and%20manually%20added%20desired%20result%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1890988%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20Match%20Receipt%20Records%20with%20closest%20previous%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1890988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EI%20have%20attached%20a%20sample%20file%20here.%20Thanks%20for%20taking%20the%20time%20to%20help%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I need serious help as I have tried all kinds of different formulas and nothing has worked. I have three sheets of data. The first sheet (we'll call sheet1) is a receipt record with Part Number, Supplier Number, Date, Qty Received. This is where the formula resides that I am having issues with figuring out. Sheet2 has a list of rejected parts, with Part Number, Supplier Number, Date, Qty Rejected. I am trying to extract the Rejected quantities from sheet2 and place them on sheet1. I created a UID by Concatenating Part Number, Supplier Number and Date in a column in both sheets. I can easily pick up the data where the receipts in sheet one match the rejects in sheet2 by using Sumif formula. Now to the difficult part. Not all parts rejected are going to match the date received so where these do not match I need to find the closest previous receipt record Sheet1 I can match it with. I cant use where data is 0 or not found then match using part number because then it will just pull everything twice. I could spend hours with a mile long post on everything I have tried. Rather than do that I will keep it short and listen for your suggestions. There is one solution and I know the first one most will want to post here which may work but involves an array formula Cntrl+Shift+Enter and that would slow the work book down dramatically.

4 Replies
Highlighted

@heylookitsme 

Is it possible to provide small sample file, just few records on each sheet; and manually added desired result?

Highlighted

@Sergei BaklanI have attached a sample file here. Thanks for taking the time to help me.

Highlighted

@Sergei BaklanI also said three sheets of data in my original post and it should have been 2 sheets of data.

Highlighted
Best Response confirmed by heylookitsme (Occasional Contributor)
Solution

@Sergei BaklanI ended up creating a secondary pivot table from the Rejects Sheet that allowed me to match using a separate formula reference. Thanks for your time looking into this. I have another post I did today that if you have time I would like for you to look at.