Match cells in excel with all characters same except one

Solved/Closed
Murtuza
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 7, 2013
- Sep 4, 2013 at 01:19 PM
 Blocked Profile - 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

2 replies

Blocked Profile
Sep 4, 2013 at 02:26 PM
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
Murtuza
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 7, 2013

Sep 5, 2013 at 01:28 AM
Thanks for your reply but I guess I was' nt able to explain my self properly. I require the formulae to check values in col A in the whole of col B .if any cell in col B has a value where the first 5 characters are same as in col A it should check the remaining values to see if all characters are same or all characters except any one is same.in either case it should give me .
Hope this helps
0
Blocked Profile
Sep 5, 2013 at 08:24 AM
I guess I was really just trying to point you in the right direction

So...you really need a macro, not a formula, right?
0
Murtuza
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 7, 2013

Sep 5, 2013 at 01:55 PM
Hey thanks once again for your reply. I am absolutely clueless about macros and would prefer an excel formulae.But if it can be done only with a macro then request you to provide ne one.I shall be immensely grateful to you.
0
Blocked Profile
Sep 5, 2013 at 02:03 PM
Ok, please upload a data structure so that I can look at it, Please.

A macro can be written as dynamic and not have static "calls", so if you are looking to change the "selection" of cells down a row, then you will need a macro. I willlook into it after a see an example of data. Thanks.
0
Murtuza
Posts
6
Registration date
Wednesday September 4, 2013
Status
Member
Last seen
September 7, 2013

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.
0
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Sep 27, 2015 at 10:47 AM
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
1
That is exactly what I'm looking for. Thanks so much for your help I would never have worked that out.

Regards

David
0
Blocked Profile
Sep 27, 2015 at 06:39 PM
Wow, the ",". Who would have thought?
0