which cell is different

[SamS]

I have about 2500 rows and 7 columns of data A - G (text), each row of data should be the same information in each of the columns but as it turns out people have modified some data even when asked not to. In column H, I would like a formula to return the odd piece of data in each row, is this possible without resorting to VBA.

Column A is what the cells should be matched against as this was the original data.

 

[DonkeyOte]

Slightly confused by your post - can you provide a small sample of data ?

 

[SamS]

lasw10

due to work restrictions I can not use the addin to show actual data but it would be something like below (minus col & row headers, the first column was the original data and in the sample the following 4 columns was the returned data

462062020P000 462062020P000 462062020P000 462062020P000 462062020P000 462062020P040
468062020P000 468062020P000 468062020P000 468062020P000 457062020P000 462062020P000
466566060P000 466566060P000 468062020P000 466566060P000 466566060P000 466566060P000

in row 1 the last cell is different
in row 2 the 5th cell is different
in row 3 the 3rd cell is different

What I need is to identify these differences in the next blank column

 

[DonkeyOte]

Just to clarify...

Row 2 has 2 variances - the last two are both different to first entry so do you only want first error ?

would you be willing to use conditional formatting to highlight the erroneous entries rather than putting the offending values in new column ?

 

[DonkeyOte]

the below would return first non-matching entry... I'm sure this isn't very elegant!

This would go in G1 assuming first row of entries A1:F1 and A1 is test value.

=OFFSET($G1,0,-1*MAX(CHOOSE({1,2,3,4,5},($A1<>$B1)*5,($A1<>$C1)*4,($A1<>$D1)*3,($A1<>$E1)*2,($A1<>$F1)*1)))

 

[SamS]

my error, the last entry in row 2 should have been the same as the first column.

I need to have the modified data in the next col. I have already tried conditioning which hilites it for me

 

[SamS]

your last post came as I posting mine - will try your solution

Thanks

 

[Peter_SSs]

If you have at most 1 difference each time then maybe this.

Formula in H1 copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	462062020P000	462062020P000	462062020P000	462062020P000	462062020P000	462062020P040	462062020P000	462062020P040
2	468062020P000	468062020P000	468062020P000	468062020P000	457062020P000	468062020P000	468062020P000	457062020P000
3	466566060P000	466566060P000	468062020P000	466566060P000	466566060P000	466566060P000	466566060P000	468062020P000
4	466566060P000	466566060P000	466566060P000	466566060P000	466566060P000	466566060P000	466566060P000
Find Difference

 

[DonkeyOte]

I told you mine was not very elegant

That said the one I did was designed to pull first mismatch ... though the above could just pull first x characters of the result of the SUBSTITUTE to retrieve first mismatch assuming each entry in range (B:F) was of equal length (say len of A1)...

eg

=LEFT(SUBSTITUTE(.....),LEN(A1))

 

[SamS]

Guys, thanks for your help. I will try both solutions in the morning (8pm my time now) as my workbook keeps coming up with an error as soon as I try to enter any more data on it. Will try and reconstruct the spreadsheet but otherwise will have to get our IT gurus on to it and they may take a while.

Thanks once again for your help.