Excel - Compare data in two worksheets
Solved/Closed
Related:
- Excel - Compare data in two worksheets
- Transfer data from one excel worksheet to another automatically - Guide
- Number to words in excel - Guide
- Tmobile data check - Guide
- Gif in excel - Guide
- Display two columns in data validation list but return only one - Guide
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
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"))
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"))
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?
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
Jun 8, 2010 at 02:02 PM
Does the other sheet is called "Sheet1" ?
Didn't find the answer you are looking for?
Ask a question
How to compare data in two different files
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Sep 19, 2011 at 08:28 AM
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
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
Jun 4, 2010 at 04:06 PM
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
Jun 4, 2010 at 06:08 PM
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"))