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 - is it possible to check to character positions away? where the letters/numbers are transposed. Or would this too bring my computer to its knees? Really do appreciate your expertise!

 

[shg]

Final suggestion.

Duplicate the list of part numbers. Sort the first list (col A) by part number. If some p/n look like numbers, ENSURE THEY ARE STORED AS STRINGS.

For the second list, use a UDF to sort the string, and sort that pair of columns (C:D) by the sort value.

Then,

	A​	B​	C​	D​
1​	4716	Match
2​	5107	AnaMatch
3​	1234	No Match
4​	p/n​	​	p/n​	sorted​
5​	0140		0140	0014
6​	0260		1400	0014
7​	0285		0510	0015
8​	0351		0260	0026
9​	0510		6020	0026
10​	0547		3600	0036
11​	0632		7003	0037
12​	0653		6700	0067
13​	0863		2014	0124
14​	1039		2016	0126
15​	1400		4031	0134
16​	1492		0351	0135
17​	1504		3108	0138
18​	1582		1039	0139
19​	1584		3190	0139
20​	2014		3910	0139
21​	2016		1504	0145
22​	2680		4150	0145
23​	2840		8410	0148
24​	2961		9140	0149
25​	2975		7105	0157
26​	3076		5108	0158
27​	3108		9105	0159
28​	3190		6810	0168
29​	3295		6190	0169
30​	3419		9710	0179
31​	3492		4320	0234
32​	3498		0632	0236
33​	3527		8320	0238
34​	3600		9302	0239
35​	3621		4520	0245
36​	3652		5042	0245
37​	3740		5420	0245
38​	3850		2840	0248
39​	3910		6250	0256
40​	3918		0285	0258
41​	3974		5029	0259
42​	4031		2680	0268
43​	4150		9260	0269
44​	4168		7920	0279
45​	4320		3740	0347
46​	4520		0653	0356
47​	4716		5703	0357
48​	4736		3850	0358
49​	4983		5308	0358

In B1, =IF(VLOOKUP(A1, $A$5:$A$49, 1) = A1, "Match", IF(VLOOKUP(StrSort(A1), $D$5:$D$49, 1) = StrSort(A1), "AnaMatch", "No Match"))

In D5, =StrSort(C5)

Function StrSort(sInp As String, _
                 Optional bAsc As Boolean = True, _
                 Optional bCaseSens As Boolean = False) As String
  ' shg 2008, 2011
  ' UDF or VBA
  ' Insertion-sorts sInp

  Dim i             As Long
  Dim j             As Long
  Dim s             As String
  Dim iComp         As vbCompareMethod
  Dim iSign         As Long

  StrSort = sInp
  iComp = IIf(bCaseSens, vbBinaryCompare, vbTextCompare)
  iSign = IIf(bAsc, 1, -1)

  For i = 2 To Len(sInp)
    s = Mid$(StrSort, i, 1)

    For j = i - 1 To 1 Step -1
      If StrComp(Mid$(StrSort, j, 1), s, iComp) <> iSign Then Exit For
      Mid(StrSort, j + 1) = Mid$(StrSort, j, 1)
    Next j

    Mid(StrSort, j + 1) = s
  Next i
End Function

 

[RRatajczyk]

man I know we are close but I'm afraid this is just a little beyond my skillset

 

[RRatajczyk]

I really am trying...

 

[shg]

If you put a workbook on box.net and post a link, I'll add the code.