Excel - Compare data in two worksheets [Solved/Closed]

Cat - Jun 4, 2010 at 11:21 AM - Latest reply:  Manish
- Sep 24, 2015 at 12:54 AM
Hello,

I need help with the best way to combine information in two worksheets and identify rows where data in colum B and column C has changed only when data in column A match.

Column A = Account #
Column B = Loan Amt
Column C = Rate

I need to quickly identify if the loan amt or rate changed on an account from one day to the next.

See more 

9 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 4, 2010 at 02:05 PM
0
Thank you
Could you have a formula based column on both sheets

as

= A1 & "|" & B1 & "|" & C1

if yes then you can use (lets say its in D col)

=IF(ISERROR(MATCH(A1, Sheet2!A:A,0)), "Account Not Found", IF(ISERROR(MATCH(D1, Sheet2!D:D,0)), "Mismatch Information", "Same Information"))
Yes, I can add a column, but formula is returning 0. In the example below I'd like to identify acct 123 and acct 122 as accounts where the rate or loan amount changed.

Sheet 1

Acct # Loan Amt Rate
123 $200,000 4.25
122 $100,000 4.25
121 $200,000 4.25
120 $200,000 4.25

Sheet 2
123 $200,000 2.5
122 $15,000 4.25
121 $200,000 4.25
120 $200,000 4.25
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 4, 2010 at 06:08 PM
How did you do it ? I just did it. The first two rows says "Mismatch Information" and the bottom two says "Same Information"

You have added formula
= A1 & "|" & B1 & "|" & C1
on column D of both sheets

then in column E you had this formula ?

On Sheet1
=IF(ISERROR(MATCH(A1, Sheet2!A:A,0)), "Account Not Found", IF(ISERROR(MATCH(D1, Sheet2!D:D,0)), "Mismatch Information", "Same Information"))

On Sheet2:
=IF(ISERROR(MATCH(A2, Sheet1!A:A,0)), "Account Not Found", IF(ISERROR(MATCH(D2, Sheet1!D:D,0)), "Mismatch Information", "Same Information"))
0
Thank you
Ah...this helps. I added a colum D on both sheets and entered the formula recomentded. I than added column E to both sheets. On Sheet1, I'm getting the first two rows says "Mismatch Information" and the bottom two says "Same Information." Yeah!

Sheet 2 is giving me a bit of trouble (columnE I added: =IF(ISERROR(MATCH(A2, Sheet1!A:A,0)), "Account Not Found", IF(ISERROR(MATCH(D2, Sheet1!D:D,0)), "Mismatch Information", "Same Information")) Each of the four rows is returning "Account Not Found" and after trying to move to a new cell, a window pops prompting me to update fulues in:sheet 1. Any idea why?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 8, 2010 at 02:02 PM
0
Thank you
Does the other sheet is called "Sheet1" ?
0
Thank you
That was too easy. There was a space - Sheet 1. Thanks much for all your help! :)
0
Thank you
How to compare data in two different files
TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Sep 19, 2011 at 08:28 AM
How to compare data in two different files?

Open both files.
Select a cell from file 1 and type "=".
Goto file 2 and select a random cell, hit enter.
Now go back to file 1 and see how Excel refers to file 2.

Best regards,
Trowa
Thank you very much