Previous Topic Next Topic

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 replies

Answer 1 / 5
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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)

=IF(ISERROR(MATCH(A1, Sheet2!A:A,0)), "Account Not Found", IF(ISERROR(MATCH(D1, Sheet2!D:D,0)), "Mismatch Information", "Same Information"))
0
Cat
Jun 4, 2010 at 04:06 PM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
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
Answer 2 / 5
Cat
Jun 8, 2010 at 01:40 PM
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?
0
Answer 3 / 5
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Jun 8, 2010 at 02:02 PM
Does the other sheet is called "Sheet1" ?
0
Answer 4 / 5
Cat
Jun 9, 2010 at 11:18 AM
That was too easy. There was a space - Sheet 1. Thanks much for all your help! :)
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
riz
Sep 19, 2011 at 07:31 AM
How to compare data in two different files
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
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
0
Manish
Sep 24, 2015 at 12:54 AM
Thank you very much
0