[SOLVED] Comparing 2 Worksheets to find Unique additions

Hi

I'm new to this stuff, so please excuse my naiveté.

I receive a daily file consisting of sales records. Unfortunately, the file isn't just new sales but rather it's a cumulative file including all previous sales. The file does not contain a date field (don't ask why!), and is sorted in such a way that the new records are not obvious.

Is there a way for me, a newbie, to compare the 2 files (yesterday's to today's) so that I can identify the new records?

I'm trying to borrow this formula from a post by NBVC to compare two different spreadsheets to see if a record in one (all related columns) appears exactly in another spreadsheet.

{REMOVED NBVC's borrowed formula as he replied to another post stating that my search type didn't meet the criteria for using MATCH). :-(

I found DAVESEXEL's formula that I modified...

=IF(COUNTIF([Sample1.xls]Sheet1!$A$2:$G$1000,Sheet1!A2:G2)=0,"Unique","") This isn't working.

Am I on the right track or is some other approach better? Still trying to search for more existing posts.

Thanks!


Uploaded sample files.

SAMPLE1 has 2 records that also appear in SAMPLE2. SAMPLE2 had a 3rd record which is uniqie. How can I compare the two files so that I can find unique records?

Hi ExcelNewbie2011
it will work if you limit the count to one column "EE File Number (6)"

Please Login or Register  to view this content.

or combine all the columns to one and compare these columns between workbooks

Pike

There are no column values that are unique, as you had hoped, but each record is unique. So, I figured out how to combine the columns (=CONCATENATE), created a new column ("H") in each spreadsheet for the concatenated (and trimmed) values of each record, and used your modifications to DAVESEXL's formula to find the new records. Worked like a charm!

=CONCATENATE(TRIM(A4),TRIM(B4),TRIM(C4),TRIM(D4),TRIM(E4),TRIM(F4),TRIM(G4))

The only problem is figuring out a way to automate the concatenation process instead of having to manually insert the formula for the required number of rows.

This is the formula that is working, slightly modified to "fix" the range in Sheet2 as was done in Sheet 1...

=IF(COUNTIF([Sample1.xls]Sheet1!$H$2:$H$6000,Sheet1!$H$2:$H$6000)=0,"New Hire","Existing EE")

I bumped up the range from 1000 to 6000, but would like it to be the actual record count.

So, is there some way (I guess a macro) that could be used to automate this?

Also, I just noticed that both files have to be open for the values to remain in the new file. Is there a way for the formula to refer to the other file even if it's closed? I ask because the new file with the identifed UNIQUE records will be sent around, but will be useless if the formula results in VALUE#?

Thanks ever so much!

Hi ExcelNewbie2011
Not, with the formula you started with. you can copy the column and then special paste the "values" back into the column.The formula will be replaced with the values

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save