# Excel - Compare data in two worksheets

Solved/Closed
Cat - Jun 4, 2010 at 11:21 AM
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.

## 5 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 4, 2010 at 02:05 PM
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)

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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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"

= A1 & "|" & B1 & "|" & C1
on column D of both sheets

then in column E you had this formula ?

On Sheet1

On Sheet2:
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 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 8, 2010 at 02:02 PM
Does the other sheet is called "Sheet1" ?
That was too easy. There was a space - Sheet 1. Thanks much for all your help! :)

Didn't find the answer you are looking for?