Comparing Data in Two Worksheets

[CynTV]

Greetings,

I would like to be able to compare data between two worksheets and find any cells that contain data that doesn't match. For instance, if I have a row with Bill Johnson's address, phone, and email in one spreadsheet, but his phone number is missing or incorrect on the second spreadsheet, I would like to be able to figure out some way to see this. I thought that I could use a VLOOKUP; however, I need to compare every cell in each row and there could be quite a few columns, I am thinking it would require a nested VLOOKUP perhaps using range names to make it easier. This is a task that will need to be done rather frequently.

Any suggestions on the most efficient way to go about this is greatly appreciated. I don't really use VBA.


Best regards.

 

[LloydFinancials]

The "=" sign between the 2 OFFSETS returns an array of {TRUE,TRUE,TRUE,FALSE}. True has a numerical value of 1 and false 0. When you multiply the array * 1, you get {1,1,1,0}, and then you MATCH 0 for the column number. Another way to write it is to remove the "1*" and "MATCH(FALSE," instead of "MATCH(0,".

Extract the two offsets, individually, and paste into 2 other cells preceded by "=", and click CSE. Then click into either of these two formulas, click F9, and then you should see the array that each offset creates in your formula bar.

CSE the following into another cell: =OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH(A1,Sheet2!$A$1:$A$3,0),1)),,,1,4)=OFFSET(A1,,,1,4).

Enter formula and hit F9 to see the relationship by column.

Resources, I have found, are best researched when problem specific. Google "Excel compare 2 ranges" and see what you get.

 

[CynTV]

The "=" sign between the 2 OFFSETS returns an array of {TRUE,TRUE,TRUE,FALSE}. True has a numerical value of 1 and false 0. When you multiply the array * 1, you get {1,1,1,0}, and then you MATCH 0 for the column number. Another way to write it is to remove the "1*" and "MATCH(FALSE," instead of "MATCH(0,".

Extract the two offsets, individually, and paste into 2 other cells preceded by "=", and click CSE. Then click into either of these two formulas, click F9, and then you should see the array that each offset creates in your formula bar.

CSE the following into another cell: =OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH(A1,Sheet2!$A$1:$A$3,0),1)),,,1,4)=OFFSET(A1,,,1,4).

Enter formula and hit F9 to see the relationship by column.

Resources, I have found, are best researched when problem specific. Google "Excel compare 2 ranges" and see what you get.

This is very helpful, thank you for taking the time to help me with this. I appreciate it.

 

[LloydFinancials]

Your welcome.