Comparing two Rows in Excel and highlight the Mismatch Cell

[pratheesh1983]

Hello,

Need help in formulating a formula:

In my team, we gets multiple file contains Client details from various other teams which we directly upload it to system. The files contains data in table form with proper preagreed headers. Unfortunately, we noticed off late, that few teams send data by changing the table format like by adding one extra column, or change a header. Because of which few data in system will get missed or may get corrupted.

To avoid the above issue, we came up with proposal to create a excel tool to compare the current file header Vs existing pre-agreed header. Basically what we planned is..In excel we will put the existing header in one Row and in row, below to it we will put the new file header. Excel will compare the both the Rows and provide a result as Pass (If all header is Matching) or Fail (if Header is not Matching).

The below is the example for the same. The List is of Arizona Top - The expected/agreed header is pasted next to agreed. New file header is pasted next to "Received"- The expectation for us is, the excel should Top Cell Vs Direct Below cell eg from below: Client Vs Client, C-ID Vs C-ID, Address Vs Contact No., Region Vs Address...and populate a result Pass/ Fail next to "Result-". In the below case, since in the new file a new column got added i.e Contact No. because of which the Result should. be fail.
I tried to get a new True False formula but some List have 30 to 40 headers, so practically it will make sense to get the result in one cell it self. Hence would request if some one can assist in putting formula here.

[TABLE="width: 666"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD]List Name [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Arizona Top[/TD]
[TD] Agreed [/TD]
[TD]Client[/TD]
[TD]C-ID[/TD]
[TD]Address[/TD]
[TD]Region[/TD]
[TD]Tier[/TD]
[TD]Remarks[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Recived[/TD]
[TD]Client[/TD]
[TD]C-ID[/TD]
[TD]Contact No.[/TD]
[TD]Address[/TD]
[TD]Region[/TD]
[TD]Tier[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD]Result - Fail


[/TD]
[TD] [/TD]
[TD]


[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

This is what I came up with, I used C1:Z1 in my sample, change the references to cover a larger than needed range as you wish.

Book1
	A	B	C	D	E	F	G	H	I
1	Arizona Top	Agreed	Client	C-ID	Address	Region	Tier	Remarks
2		Recived	Client	C-ID	Contact No.	Address	Region	Tier	Remarks
3	Result	Fail
Sheet57

Cell Formulas
Range	Formula
B3	=IF(SUMPRODUCT(--(C1:Z1=C2:Z2))<>COLUMNS(C1:Z1),"Fail","Pass")

 

[Peter_SSs]

.. or reversing the comparison (still comparing columns C:Z), it shortens to

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Arizona Top	Agreed	Client	C-ID	Address	Region	Tier	Remarks
2		Recived	Client	C-ID	Contact No.	Address	Region	Tier	Remarks
3	Result	Fail
Compare

However, the above formula also returns "Fail" for the data below. Should it be "Pass" since all the same fields appear, just in a different order?

Excel Workbook
	A	B	C	D	E	F	G	H
1	Arizona Top	Agreed	Client	C-ID	Address	Region	Tier	Remarks
2		Recived	Client	C-ID	Region	Remarks	Address	Tier
Compare (2)