Merge Data?

Hello everybody,

My department is about to begin a seasonal program that requires extensive data management and I will need to keep track of orders that change on daily basis.

The program consists of outsourcing orders from customers that require a specific service. In the interest of confidentiality I will use generalities to describe the service and orders.

Say for example I run a report every day of ALL the orders that are created in our system for a work week (Mon - Fri). Of those orders a certain amount of customers request S service. I download the file and have my orders for Monday. Now I am only concerned with the orders that request that specific S service. On Tuesday I run the report again and have ALL the same orders that I had yesterday plus additional new ones that were entered since Monday, including those requesting S service. Again, I am only concerned with orders requesting S service because those orders will get outsourced. On Monday order ABC might be a new order requesting S service with X status, but on Tuesday order ABC might have been outsourced already so it would show up with Y status. I want to be able to get rid of the row that is in X Status merge the two worksheets and be able to view and control an updated version with the merge data that has purged the duplicate orders.

My question is, how can a create an automated process that would allow me to create a list of those orders and update it the next day by merging the two worksheets that have the same data date range of Mon - Fri without having to manually manipulate the data every day that I import it from the list of order. The range of date is fixed so I can't just import data from a specific day of the week. I want to be able to start a list on Monday of all my orders with the requested S service for the week and continue to add those particular orders to my list each day without having to manually delete orders that have been outsourced (duplicates or sorting or filtering) and adding to existing worksheet. I know what I want my data to be, I just don't know how to start and maintain that process. Basically, I need to keep track of orders with S service and know when they have been outsourced, but I need to automate the process so that Excel can just look at the date and know which order has changed, or has been outsourced and merge the data to only contain the current and true information.

Any help or suggestions are greatly appreciated. I just want to find a common sense, practically, and efficient solution to dealing with data that will change from one day to another, without having to waste time finding, sorting, filtering, deleting, or other steps to update my data on a daily basis. It is essential to accurately track those orders that have been outsourced and purge the data rows that is the same order but that has not been sorted or compared. There is also no column for status, the only indication that the order has been outsourced is that the account number will have an S after it to let the user know the order has been outrsourced. So Monday the order will be ABC but Tuesday or any other day that week or weeks from Monday it will be ABCS. I need to get rid of ABC and keep ABCS. I was thinking of using a Pivot Table, running a macro, consolidating, formula/functions but wouldn't know where to start.

Thank you for viewing my post, and help in advanced.

Welcome to the forums!

Attach a sample workbook or workbooks. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I hope that you are not just using the names of the days of the week and not real dates as you are almost certain to lose data if you delete the previous week's data and replace it with current data. All formula based extraction of data to a new worksheet will lose the data from the week before. Use real dates and append your new data to the bottom of the old data. Doing this, you will have a history of transactions and you will be able to re-construct your data tracking when something goes wrong.

Depending upon what your real data looks like, you should be able to use a Pivot Table to summarize your data in the manner that you describe. If a Pivot table won't give what you want, a formula base solution or VBA may provide the answer. Without seeing realistic data of what you receive and what you expect out of it, a real answer cannot possibly be given unless by fluke.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

Thanks Pete. I used the canned reply. I thought that it had been edited to reflect that defect. Oh well just created my own.

I'm not sure what you want except there are entries that are followed by the same order number only with an S at the end and these are highlighted on the various worksheets. I inserted a worksheet to extract all those order numbers. To do so, I used a helper column on the Master worksheet with this formula in J2 and filled down to identify the items that had an S appended and the values with the S.

Formula:

Please Login or Register  to view this content.

On a new worksheet (red tab) I copied the headers from the Master worksheet and pasted them into the first row of the new worksheet. In A2 of the new worksheet, I entered this array formula and filled across and down. Enter with Ctrl + Shift + Enter

Formula:

Please Login or Register  to view this content.