The Opposite of Remove Duplicates

[Mom2BAD]

Hi All

Facilities submit data to me on a regular basis and my job is to notify them if they are being compliant with their submissions. The problem is that facilities come and go but their historical data is important so they are never removed from the database where data is uploaded.  When I want to pull a quarterly report to enter the data for the active facilities in my worksheet for mail merge, I have about 300 facilities that no longer exist in my worksheet. I have been adding all of the facilities from the data source, filling the cell color of the data source one color and the cells on the worksheet another.  I then sort on a field that exists for both sources. This creates a worksheet with alternating colors. I delete the ones that do not exist in both colors. After that, I sort on cell color and I have my clean resource data to add to the mail merge worksheet. I copy and paste the new numbers from the resource database onto my worksheet.  This is very cumbersome and time consuming.  I know there has to be a way to do this in Excel without so much manual labor.  I have tried searching for a solution, but I just haven't found one.

Anyone have any ideas?

[#2]

@Mom2BAD 

It's difficult to picture exactly what it is you're doing. And difficult to discern your level of knowledge and comfort with Excel.

I'm quite sure--to the extent that I've accurately followed the task--that it could be facilitated by the appropriation of a few select Excel functions. So here are a couple starter thoughts. Feel free to come back with more questions or clarification. If it is possible to upload a sample file or two, that would help as well.

So the starter thoughts:

When I've had to combine files that are similar is layout (i.e., same columns), I will differentiate the various rows in terms of their original source by adding a column before blending them. This is an alternative to changing color background as you're doing. So, for example, I combine downloaded data from all my credit cards into a single file, but first add a column that identifies which card each row represents....Amex, Citibank, Chase, etc.  It's a single column in the combined database, but each row is clearly identified, and it's very easy to sort or filter them by that column or any other. You could accomplish this in yours by adding a column "SOURCE" or something to that effect, and entering "Historical" in one of the sets of records and "Current" in the other. Or whatever identifiers you wanted. You don't need to resort to the color as the basis for identifying.

Anyway, if that doesn't make sense, it would be easier to help with specifics if you could post some representative examples of your actual files. Just make sure any confidential or private data are rendered anonymous.