Match cells in excel with all characters same except one [Solved/Closed]

Murtuza 6 Posts Wednesday September 4, 2013Registration date September 7, 2013 Last seen - Sep 4, 2013 at 01:19 PM - Latest reply: ac3mark 9942 Posts Monday June 3, 2013Registration dateModeratorStatus July 17, 2018 Last seen
- Sep 27, 2015 at 06:39 PM
Hi I was wondering if it was possible in excel to write a formula to perform the following task. I have two columns of data, col A and Col B . For each of the cells in column A excel should match the first five characters in column B .if it finds a cell in column B which has the same first five characters as in column A , it should check if all of the remaining characters except any one is same.If it is then it should say true or else false.
So for eg my data could look like this
A B
abcdegh abcdeijk
- abcdefgh
- abcedrtg
In this case I should get true as the second cell in col B satisfies both the conditions.Also the length of the strings in col A is not consistent. so one could be 10 character long and other could be 8 character long and so on and so forth.
Need this urgently so will appreciate any hrlp on this
See more 

13 replies

Best answer
ac3mark 9942 Posts Monday June 3, 2013Registration dateContributorStatus July 17, 2018 Last seen - Sep 4, 2013 at 02:26 PM
5
Thank you
Well, for that function you get the LEFT.

as in...
=IF(LEFT(F2,5)=LEFT(G2,5),IF(F2=G2,TRUE, FALSE),FALSE)

I REALLY HOPE THIS ISNT A SCHOOL PROJECT.


So lets take a look at what happened:

First Formula:
=If(logic test, true, false)

So we then nested in the LEFT Method, that says look at the cell, and return the LEFT MOST 5 characters in the logical test of the IF. So if the left most matches, it returns a true.

Second Formula:
So in the True column of the first IF statement, we have to run what happens when it is true.

=if(f2=g2,true,false)

SO we then look at the whole value of the cell, and match it completely, nested within another if statement.


Please understand, you can use RIGHT to check the right most for trailing characters, also.

Have Fun!

//ark
-Contributor

Thank you, ac3mark 5

Something to say? Add comment

CCM has helped 1701 users this month

Murtuza 6 Posts Wednesday September 4, 2013Registration date September 7, 2013 Last seen - Sep 7, 2013 at 09:02 AM
Well so say I have two columns Col A and Col B containing alphanumeric values.
A B
12345abcd 56789reft
45678defghijk 78902tery
67890sdf 12345abc

So in the above case the third value in column B partially matches the first value in col A . The first 5 digts in both the cells (A1 and B3) are same which is the first condition to be satisfied. Only if that is true should the remaining values be checked and if all the remaining characters are same or all characters except any one are same it should give me true. The problem is compounded by the fact that the length of the strings in col A are not equal. However the no of numeric characters in the string are always 5 irrespective of the total length of the string. Hope I have been able to explain my self more clearly this time. If you can provide me a formula or a macro it will be simply awesome.Also if you could guide me on how to deploy macros in excel.
Murtuza 6 Posts Wednesday September 4, 2013Registration date September 7, 2013 Last seen - Sep 7, 2013 at 09:04 AM
Hey the data is like this
A B
12345abcd 56789reft
45678defghijk 78902tery
67890sdf 12345abc
You just saved my life with this one ac3mark!!!!
ac3mark 9942 Posts Monday June 3, 2013Registration dateModeratorStatus July 17, 2018 Last seen - Sep 25, 2015 at 05:19 PM
@Batman....I try to help! If you need any other help, just let us know! I hope this wasn't school work!
I Like this formula =if(f2=g2,true,false) but how do I ask the formula if one of the cells in blank then leave blank and not come back false?

Thanks
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Sep 27, 2015 at 10:47 AM
1
Thank you
Davcorp10, Good morning.

Try to use:

=IF(OR(F2="",G2=""),"",IF(F2=G2,TRUE,FALSE))

Is it what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
That is exactly what I'm looking for. Thanks so much for your help I would never have worked that out.

Regards

David
ac3mark 9942 Posts Monday June 3, 2013Registration dateModeratorStatus July 17, 2018 Last seen - Sep 27, 2015 at 06:39 PM
Wow, the ",". Who would have thought?