Loading data into master sheet using daily reports

Hey guys,

So, I’m trying to create a database, of sorts. I run several reports every morning that leave me with the information I need, but I would like to be able to store them in a master sheet, thus creating a database. That master sheet will be used by my co-workers and, at times, my customers or boss. Add in a few hundred new lines every day and it becomes a task in itself just to keep the spreadsheet up to date, manually. I’m only needing to do this now because the system I use now will be shut down starting next Monday. I (someone who has never had any formal excel training) have been tasked with creating a new way to track orders.

I can create a unique identifier tag for each line, but I don’t know how to program or create a macro/equation to be able to filter and replace values in the tag’s row, based solely on the tag. The combination of columns A&B in my test report will give me the unique tags. From there, I need columns E-L to be able to be updated from the new reports each morning.

If Sheet 2 contains a list of items that I have updated during the day and wish to place into the master sheet (Sheet1)… What way would be best? Does a method already exist that someone knows of? I can try to learn how to do this on my own, but I don’t know the language needed to write the macro and any necessary equations. I know I can learn this, I just don’t know how long it might take.

My goal is to have my reports dump information into a master sheet, but only replacing/adding onto existing rows and columns that correspond with each other and creating new lines when no pair is found.

I’ve attached what my report should look like after I’ve filtered out the unneeded information. It will be this, every day, except with a few hundred more lines.

Imagine Sheet 1 is the master sheet and sheet 2 is the newly uploaded information.



I'd appreciate any and all input.

If the unique data somehow updated the main sheet, yes. I would also have to have the unique data be exactly the same as the data from the generated reports every morning. The data from the morning reports are what I need to work on. I can't see this being easier. I'm of the mind, currently, that being able to update the main sheet once a day (or more for extra reports) would be the easiest method.

If I can program a macro to do something...

Merge columns A & B on Sheet 2
Run for row 1 the following:
Search A on sheet 1 using sheet 2 A&B as search parameters
If match found, replace E,F,G,H,I,J,K Sheet 1 with E,F,G,H,I,J,K sheet 2
Merge L Sheet 1 and L Sheet 2 under current row
If no match found Insert all fields into first empty row
Delete Row 2 and move all rows up

I'm thinking this would be all I need, but I don't know how to write it and I'm not sure how to insert variables (IF Sheet2 A1 = Sheet 1 any row column A) to make it so the corresponding row is now used for inserting/merging until that macro is complete. The macro would be on a continuous cycle until all rows on sheet 2 are deleted.

Am I still going about this the wrong way?

I see what you're getting at. The unique data isn't all of what I need, though. The unique data would only be brand new entries. Any open entries from the previous day or report would be a duplicate now. However, if I simply pasted it all onto the main sheet and clumped duplicates together... I could manually sort and filter. Now I'm back to using up time just getting the spreadsheet updated, which is exactly what I don't want to do.

Is there a way then to take duplicate information and have certain columns overridden and others merged with a macro?