comparing records from one excel sheet to another

al4629740
al4629740 used Ask the Experts™
on
I have an excel sheet of records.  Every month I recieve a new sheet of updated records.  I want to see which records are new, but I don't have any way of noticing them.  Is there a way to compare the two excel sheets and note only the changes that have been added?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
Do you have a unique ID that you could run a match on ?

Also, check out this excellent article by Dave Brett, which will give you the add-in and the knowledge to easily compare two worksheets.

Thomas

Author

Commented:
yes.  there is an EIN column
So the easy way, if I understand your problem statement, is to use a MATCH function on every row in the new sheet and search for the EIN in the column on the old sheet that contains the EIN.  Make sure your 3rd parameter in the MATCH is 0, for an exact match.  The formula will return an error for every EIN in the new sheet not found in the old.  You can process the error condition to your liking (via an IF statement, etc).
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Top Expert 2008

Commented:
Can you post samples of data?

Author

Commented:

Author

Commented:
I just posted a sample.  I will be comparing this exact sheet which gets additions each month.
Did you try the match function?  Searching for a value in a list is it's purpose - one that it accomplishes with ease.  It will let know know, when searching for an exact match (vs the closet value) whether one is found or not.  Using it, you can definitively tell the new EIN numbers added each period.

Author

Commented:
If I have two different sheets though, how would I compare the first sheet with the second sheet
The second argument of MATCH is the lookup array.  From the Excel help, "the range of cells being searched".  Using typical spreadsheet mechanics, when you get to this argument, use your mouse to select the worksheet and then highlight the range you are wanting to search.  Since, I assume, you are wanting to copy the formula down for each row in the new sheet, make sure and use an absolute range (after search range highlighted, hit the F4 key once). Finish the formula with ",0)" and you are done.
Top Expert 2008

Commented:
Are you trying to check that all cells are the same, or just that a line didn't exist previously?

Author

Commented:
that a line didn't previously exist compared to the old sheet.

The sheet gets added to monthly and then they send me the whole new one each month
Top Expert 2008
Commented:
In cell ag2, you can add the following formula:

=IF(ISNA(MATCH($A2,[Book1]Sheet1!$A:$A,0)),"New Line","Existing line")

Assuming book1]Sheet1 is where the existing data lies.

Copy down, filter on new lines and you should be set.

Thomas

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial