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

Report
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 7, 2013
-
 Blocked Profile -
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

2 replies


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
5
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2841 users have said thank you to us this month

Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 7, 2013

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.
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 7, 2013

Hey the data is like this
A B
12345abcd 56789reft
45678defghijk 78902tery
67890sdf 12345abc
You just saved my life with this one ac3mark!!!!
Blocked Profile
@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
Posts
1829
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
135
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
Blocked Profile
Wow, the ",". Who would have thought?