PDA

View Full Version : Solved: How to comparing two columns



harish1805
04-04-2012, 01:13 AM
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
04-04-2012, 02:46 AM
In your workbook, give your expected results and explain why.

harish1805
04-04-2012, 03:31 AM
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
04-04-2012, 03:39 AM
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
04-04-2012, 03:45 AM
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
04-04-2012, 03:55 AM
also there can be more than one space, attaching the updated example file:

harish1805
04-04-2012, 04:29 AM
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
04-04-2012, 05:33 AM
Crosspost here (http://www.excelforum.com/excel-programming/823112-comparing-two-columns.html).

harish1805
04-04-2012, 06:04 AM
Crosspost here (http://www.excelforum.com/excel-programming/823112-comparing-two-columns.html).

thanks it worked for me sometime back!

Bob Phillips
04-04-2012, 06:05 AM
So it was solved over there, even though it doesn't cater for no spaces, nor multiple spaces?

harish1805
04-04-2012, 06:11 AM
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
04-04-2012, 06:15 AM
It works for 'abde 02' and 'abde 03', but not for 'abde 02' and 'abde 03'

harish1805
04-04-2012, 06:19 AM
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
04-04-2012, 08:21 AM
I was referring to two spaces in 1, one in the other, but that example fails too.

shrivallabha
04-04-2012, 10:25 PM
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)