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]

ok - so the MACRO and FORMULA I believe are working. However, I am not sure how to get the results? I am looking for some type of report that list all the part numbers that are duplicative (contain the same characters and/or numbers) but are transposed. Just a report showing me the results whereas I can see the list of parts that meet this condition?

 

[shg]

=IF(COUNTIF($A$6:$A$10, A1), "Present", IF(SUMPRODUCT(COUNTIF($A$6:$A$10, Transpositions(A1))), "Transposed", "N/A"))

 

[RRatajczyk]

ok - I don't know if it's operator error or what 's happening but the VBA Macro does not appear to be working? I am not even sure if there is something I need to do to run it or if it happens automatically when I add new part numbers? In any case mothing seems to be working? thanks

 

[shg]

See that $A$6:$A$10 in the formula?

 

[RRatajczyk]

I just don't know that I loaded the VBA Macro Code correctly - not sure if I know what I'm doing here... How do you load the code you'd provided? And do I need to do something each time to run it or does it always run/work? thanks

 

[shg]

I thought you already had that working back in post #18 ?

 

[RRatajczyk]

ok I got it to work again. However, there still may be an issue. i.e. ABC123456 should determine that there are 2 part numbers in the data set that match the criteria ABC123456 & BCA123456. The results are 1 which I assume is the first instance (ABC123456) and it's not recognizing a match (transposed) with BCA123456?

 

[RRatajczyk]

As I continue on my quest to find a solution I am discovering more about what I am looking for. Problem Statement: I have a list of 30,000 part numbers and I would like to determine are either duplicates or have the same set of characters and numbers but in some transposed arrangement. I would like to be able to sort the part numbers that meet one or both of these conditions and still show the whole list. Or be able to filter on one or both of the met conditions. thank you

 

[RRatajczyk]

As I continue on my quest to find a solution I am discovering more about what I am looking for. Problem Statement: I have a list of 30,000 part numbers and what I would like to determine is if there are any duplicate part numbers or part numbers that have the same set of characters and numbers but in some transposed arrangement. I would like to be able to sort the part numbers that meet one or both of these conditions and still show the whole list. Or be able to filter on one or both of the met conditions. thank you

 

[shg]

BCA123456 is not a transposition of a pair of adjacent characters from the string ABC123456. The function could readily be modified to return all transpositions (which would be 36 for a 9-character string instead of the current 9), but at some point you will bring your computer to its knees.

I think you need to come up with Plan B.