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

Report
-
 Manish -
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.

5 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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"))
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?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Does the other sheet is called "Sheet1" ?
That was too easy. There was a space - Sheet 1. Thanks much for all your help! :)
How to compare data in two different files
Posts
2670
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 22, 2020
446
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