Character / Number Sequence

[RRatajczyk]

Please Help!

I am looking for a formula that will identify/detect when a part number is identical (a match) in a long list of part numbers and where it is different in the sequence by one character or number. i.e. ABC123456 vs ABC123465

the result would be "True" where this occurs

In other words the part numbers are identical with the exception of one of the characters or numbers is out of sequence (= TRUE)

and the whole long list of part numbers is being evaluated / compared

 

[RRatajczyk]

My sincere apology. I'm a bit rusty on how to do this. Do I enter B1: {=SUM(COUNTIF($A$6:$A$10, Transpositions(A1)))} into cell B2 in your above example? I have copied and pasted the above into a new Excel worksheet, but am a little confused about the reference to B1 and A1 in the formula you'd provided. I feel bad, because I know your helping me.

 

[RRatajczyk]

is it possible to attach/send your Excel file example?

 

[Rick Rothstein]

My sincere apology. I'm a bit rusty on how to do this. Do I enter B1: {=SUM(COUNTIF($A$6:$A$10, Transpositions(A1)))} into cell B2 in your above example? I have copied and pasted the above into a new Excel worksheet, but am a little confused about the reference to B1 and A1 in the formula you'd provided. I feel bad, because I know your helping me.

You put this in the Formula Bar...

=SUM(COUNTIF($A$6:$A$10, Transpositions(A1)))

and then commit it using CTRL+SHIFT+ENTER... Excel will add the curly braces afterward.

 

[shg]

Or =SUMPRODUCT(COUNTIF($A$6:$A$10, Transpositions(A1))) entered the usual way -- should have suggested that initially.

 

[RRatajczyk]

ok. I am not sure if this is the expected result I was looking for. I was trying to identify the part numbers in a list of parts that were identical with the exception of a couple of characters or numbers being transposed. i.e. ABC123456, ABC123465. In this example they match however, the last two characters/numbers are transposed.

 

[RRatajczyk]

it may even be something like this ABC123456; ABC623451. In this example the characters/numbers are all the same however, the 1 and 6 are in a different sequence.

 

[RRatajczyk]

the result here would be "True" or 1 I suppose

 

[RRatajczyk]

My apologies. IT WORKS MASTERFULLY! thank you so much...

 

[shg]

You're welcome.

BTW, the second version is slightly faster (5%) than the first.

 

[RRatajczyk]

x

You're welcome.

BTW, the second version is slightly faster (5%) than the first.