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

 

[shg]

where it is different in the sequence by one character or number.

You mean they contain exactly the same characters, and an adjacent pair of characters is transposed? That would be two characters that differ.

 

[RRatajczyk]

You mean they contain exactly the same characters, and an adjacent pair of characters is transposed? That would be two characters that differ.

Yes

 

[RRatajczyk]

somehow all the part numbers in a list would have to be compared against each other to determine 1) the characters / numbers match 2) two characters or numbers are transposed

 

[RRatajczyk]

somehow all the part numbers in a list would have to be compared against each other to determine 1) they match. 2) two characters or numbers are transposed

 

[shg]

Function Transpositions(sInp As String) As String()
  Dim i As Long
  ReDim asout(1 To Len(sInp)) As String
  
  asout(1) = sInp
  For i = 2 To Len(sInp) - 1
    asout(i) = Left(sInp, i - 1) & Mid(sInp, i + 1, 1) & Mid(sInp, i, 1) & Mid(sInp, i + 2)
  Next i
  
  Transpositions = asout
End Function

	A​	B​	C​
1​	P/N​	Matches​
2​	ABC123457	1​	B1: {=SUM(COUNTIF($A$6:$A$10, Transpositions(A1)))}
3​	ABC124358	1​
4​	ABC124357	1​
5​	ABC124355	0​
6​
7​	Parts​
8​	ABC123456
9​	ABC123457
10​	ABC123458
11​	ABC123459
12​	ABC123460

Note that the formula is array-entered.

 

[shg]

This could be faster or slower, dunno.

Function Transpositions(sInp As String) As String()
  Dim i As Long
  ReDim asout(1 To Len(sInp)) As String
  
  For i = 1 To Len(sInp) - 1
    asout(i) = sInp
    Mid(asout(i), i) = StrReverse(Mid(sInp, i, 2))
  Next i
  asout(i) = sInp
  
  Transpositions = asout
End Function

 

[RRatajczyk]

ok - trying it now. I'll let you know. HUGE thank you1

 

[RRatajczyk]

what's the trick or function to get the { } around a formula again?

 

[Rick Rothstein]

what's the trick or function to get the { } around a formula again?

Press CTRL+SHIFT+ENTER all at the same time when committing the formula.