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]

Do both table have the same column headers and order of column headers?

 

[CynTV]

Do both table have the same column headers and order of column headers?

Thank you for your reply. Yes, they do. The tables will always be the same, it is just that the data in the cells may change.

I appreciate your help.

Best regards,

 

[LloydFinancials]

This formula will give the column # for the value that does not match.

Excel 2010
	A	B	C	D
1	1	2	3	4
2	5	6	7	8
3	10	11	12	13
Sheet1

Excel 2010
	A	B	C	D	E	F
1	5	11	7	8		2
2	10	11	12	13
3	1	2	3	9		4
Sheet2

Cell Formulas
Range	Formula
F1	{=IFERROR(MATCH(0,1*(OFFSET(INDIRECT("Sheet1!"&ADDRESS(MATCH(A1,Sheet1!$A$1:$A$3,0),1)),,,1,4)=OFFSET(A1,,,1,4)),0),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[CynTV]

This formula will give the column # for the value that does not match.

Excel 2010

	A	B	C	D
1	1	2	3	4
2	5	6	7	8
3	10	11	12	13

<tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C	D	E	F
1	5	11	7	8		2
2	10	11	12	13
3	1	2	3	9		4

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula F1 {=IFERROR(MATCH(0,1*(OFFSET(INDIRECT("Sheet1!"&ADDRESS(MATCH(A1,Sheet1!$A$1:$A$3,0),1)),,,1,4)=OFFSET(A1,,,1,4)),0),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you for your help. My apologies for the delay in responding. I seem to be getting a circular reference error when I try using the formula provided. See the following screenshot. Please let me know if I am doing something wrong. Error Message - cynthia.lhv's library


Thanks again for taking time out to help me with this.

Best regards,


Cynthia

 

[LloydFinancials]

What is the sheet name for the cell A1 where you have the formula?

 

[LloydFinancials]

OK, so the A1 in the formula, i.e. MATCH(A1... and OFFSET(A1..., that A1 needs to be the first cell in the table that you are comparing (the table for which you want to see the mismatches). The way it is designed is that the formula is in a cell to the right of the main table. That way, you can copy it down. Can you put the formula to the right of the table you want to compare and adjust the references?

 

[CynTV]

OK, so the A1 in the formula, i.e. MATCH(A1... and OFFSET(A1..., that A1 needs to be the first cell in the table that you are comparing (the table for which you want to see the mismatches). The way it is designed is that the formula is in a cell to the right of the main table. That way, you can copy it down. Can you put the formula to the right of the table you want to compare and adjust the references?

Thanks, again for your help.

I seem to be getting closer, but I am obviously still doing something wrong. I have attached a link to the workbook Compare Worksheets - cynthia.lhv's library. If you don't mind, please take a look. If you would let me know where I am going wrong, that would be great.

Thanks again.

 

[LloydFinancials]

No, problem, thanks for the note. The 4 in the second offset is the number of data columns in the table on the Compare worksheet. So, in this case, change it to 3. Then, use Control+Shift+Enter instead of just enter after making the change. Copy down.

If you get an error when trying to copy down "Cannot change part of an array", Copy the formula in D1 (just the formula and not the cell), and paste into E1. Delete cells D1:D3, and then paste the formula back, enter with Control + Shift + Enter, and copy down.

 

[CynTV]

Thanks for your help with this. It works perfectly.

=IFERROR(MATCH(0,1*(OFFSET(INDIRECT("Sheet2!"&ADDRESS(MATCH(A1,Sheet2!$A$1:$A$3,0),1)),,,1,3)=OFFSET(A1,,,1,3)),0),"")

I would like to make sure I understand why it works. If you do not have time, please let me know - I appreciate the time you have taken to help me already; however, I was hoping you would let me know what role the elements I have formatted in Bold play in the formula. Specifically, the "1*" and the ",,,". Also, if you have any recommendations for resources or techniques to help me with learning more complicated nested formulas like this one, that would be great.

Again thanks for your help!...and for your patience. This is great.