Same report is run each week, need to remove duplicates, consolidate new data with old

[ImHorhay]

Hi Guru's,

I think what I need here is help developing a SEARCH/INDEX/VLOOKUP that works at aligning multiple criteria (material + batch + SLED/BBD + total quantity nearest value), so new data can be consolidated with old and duplicate data removed. I'm okay with Excel, but seem to have tapped my own available knowledge and am hoping one of you fine folks can help a brother out?

I've attached a sample of the report I export from SAP - it's for at risk inventory and is run weekly. The purple shaded columns have been added for notes, all columns to the left of the purple ones are hard formatted from the SAP export to Excel.

The challenge with this report is that a lot of the data each week is mostly the same as the data from the week prior, and some of that data has been acted upon and updated with notes in the original data set - I want to keep that original data and consolidate it with the new data so it isn't shown as a duplicate row in the file.

I'd like for this file to be a live document that multiple users can access and update so we can stop having to reinvent the wheel every week trying to remember what action was taken against materials that are in process of being resolved but can't be removed from the spreadsheet until physically gone or no longer at risk and appearing on the weekly report. Current process employed to consolidate data between weekly reports is to append the data sets, differentiate one week from the other by highlighting the text a different colour; sort by material, batch and SLED/BBD and then manually begin removing duplicates.

I don't use Remove Duplicates because there are multiple rows of the same material and batch with different quantity that using Remove Duplicates would likely remove many rows of data that are not actually duplicates.

Any help would be appreciated Gurus, thanks in advance for your time and energy looking at this.

George

[mikeTRON]

In order to do what you are wanting to do, you need to find a way to identify unique rows. This can be as simple (and ugly) as concatenating all of the data dimensions into one "Unique Key" Then you just look for the unique key in the new report, and if it exists, purge it, otherwise add any NEW ones to the bottom (or top) of your list.

[ImHorhay]

Thank you, mikeTRON, this did the trick nicely, and is a much faster method than the one currently in use!