Solved: How to comparing two columns

[harish1805]

Hi,
I wanted to compare two columns A and B. If its different then the result should be 0 and if its complete match it should be 1. If the strings in the columns A and B are matching but numbers are not then we have to check if the numbers are between +1 and -1, if its between +1 and -1 then it should give result as 1 else 0.

Attaching the data sheet, this will make it more clearer.

[Bob Phillips]

In your workbook, give your expected results and explain why.

[harish1805]

In your workbook, give your expected results and explain why.

Please find expected result and the reason for it in this attached file:

[Bob Phillips]

This seems to do it

=(LEFT(A2,FIND(" ",A2)-1)=LEFT(B2,FIND(" ",B2)-1))*(ABS(MID(A2,FIND(" ",A2)+1,9)-MID(B2,FIND(" ",B2)+1,9))<=1)

[harish1805]

This seems to do it

=(LEFT(A2,FIND(" ",A2)-1)=LEFT(B2,FIND(" ",B2)-1))*(ABS(MID(A2,FIND(" ",A2)+1,9)-MID(B2,FIND(" ",B2)+1,9))<=1)

What if there is no space? how can get the first integer value.

[harish1805]

also there can be more than one space, attaching the updated example file:

[harish1805]

This seems to do it

=(LEFT(A2,FIND(" ",A2)-1)=LEFT(B2,FIND(" ",B2)-1))*(ABS(MID(A2,FIND(" ",A2)+1,9)-MID(B2,FIND(" ",B2)+1,9))<=1)

Please let me know if you have any idea how to solve it? Waiting for your reply.

[Aflatoon]

Crosspost here (http://www.excelforum.com/excel-programming/823112-comparing-two-columns.html).

[harish1805]

Crosspost here (http://www.excelforum.com/excel-programming/823112-comparing-two-columns.html).

thanks it worked for me sometime back!

[Bob Phillips]

So it was solved over there, even though it doesn't cater for no spaces, nor multiple spaces?

[harish1805]

So it was solved over there, even though it doesn't cater for no spaces, nor multiple spaces?

Yes, this formule took care of space issues.



=LOOKUP(99^99,CHOOSE({1;2},0,--(ABS(SUBSTITUTE(A2,LEFT(B2,LEN(B2)-1),"")-RIGHT(B2,2))<=1)))

[Bob Phillips]

It works for 'abde 02' and 'abde 03', but not for 'abde 02' and 'abde 03'

[harish1805]

It works for 'abde 02' and 'abde 03', but not for 'abde 02' and 'abde 03'

ok got that, do you mean 'abde 02' and 'abde03'

[Bob Phillips]

I was referring to two spaces in 1, one in the other, but that example fails too.

[shrivallabha]

This formula works for sample data posted at post#06:
=IF(AND(ISNUMBER(FIND(REPLACE(B2,LEN(B2)-1,2,""),A2,1)),(RIGHT(B2,2)-RIGHT(A2,2))>=-1,(RIGHT(B2,2)-RIGHT(A2,2))<=1),1,0)