matching numbers with different digits orders

Try...

D1, copied down:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)))=LEN(B1),C1,#N/A)

Hope this helps!

this is a mess, but here goes

part numbers in cells a3 and b3

d3 =VALUE(LEFT(A3,1))
e3 =VALUE(MID(A3,2,1))
f3 =VALUE(RIGHT(A3,1))
g3 =VALUE(LEFT(B3,1))
h3 =VALUE(MID(B3,2,1))
i3 =VALUE(RIGHT(B3,1))
j3 =IF(AND(LARGE(D3:F3,1)=LARGE(G3:I3,1),LARGE(D3:F3,2)=LARGE(G3:I3,2),LARGE(D3:F3,3)=LARGE(G3:I3,3)),"good","bad")

bascally this forces a comparison of the descending order of the digits of the part number

just a shot at it

hi again, sorry to miss out one important point:
part numbers to be matched to the range of numbers in column B

\1


Explanations:

i named the range B2:B11 = MASTER3

D3 returns a result of "Tokyo" because:
A3 "matches" to B4 (regardless of the digits orders), then take C4 value which is "Tokyo"

another example:
D8 returns result of "200" because:
A8 "matches" to B6 (regardless of the digits orders), then take C6 value which is "200"

another example:
D11 returns "no match" because:
A11 has no match in any of the numbers (regardless of the digits orders) in MASTER3 (B2:B11)

and the most complicated example:
D10 returns "525 & Tokyo" because:
A10 "matches" to B3 (regardless of the digits orders), then take C3 value which is "525"
and also
"matches" to B8 (regardless of the digits orders), then take C8 value which is "Tokyo"



column A & B are formatted as TEXT because may have leading zeros.
A & B also has fixed number of digits, 4 (including leading zeros if any).

my list runs to the hundreds

your help/suggestions greatly appreciated.

thanks,

given that i think a vba solution is in order. Read in all the part #'s in column b into an array:

colb(1,1) = 1st entry, max digit
colb(1,2) = 1st entry, next highest digit
.....
colb(2,1) = 2nd entry, max digit
......

then go down column a parts and start a comparison of the max digit, next highest, etc and if a match is found make appropriate entry in column d (copy column c)

I'm hardly a vba expert but I'm working it as we speak

This seemed to work - I tested it on two rows of data (rows 3 and 4) For more rows of data all of the dim statements array sizes need to be increased (ie 5 covered my two rows) all the for i or j = 3 to 4 statements need to be expanded as well. it also only handles 3 or 4 digit part #'s

Also note the macro writes to columns e thru l on every row of data.

I'm sure someone better at vba could improve this.

Good Luck!


Sub Macro1()
Dim colbdigit1(5) As Integer
Dim colbdigit2(5) As Integer
Dim colbdigit3(5) As Integer
Dim colbdigit4(5) As Integer
Dim coladigit1(5) As Integer
Dim coladigit2(5) As Integer
Dim coladigit3(5) As Integer
Dim coladigit4(5) As Integer
Dim colc(5) As String
'Run thru column B entries and break out digits into columns H thru L
For i = 3 To 4
If Len(Cells(i, 2).Text) = 4 Then GoTo fourb
Cells(i, 9).Value = Left(Cells(i, 2).Text, 1)
Cells(i, 10).Value = Mid(Cells(i, 2).Text, 2, 1)
Cells(i, 11).Value = Right(Cells(i, 2).Text, 1)
'Figure out descending values of the digits
colbdigit1(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 11)), 1)
colbdigit2(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 11)), 2)
colbdigit3(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 11)), 3)
GoTo nextib
fourb:
Cells(i, 9).Value = Left(Cells(i, 2).Text, 1)
Cells(i, 10).Value = Mid(Cells(i, 2).Text, 2, 1)
Cells(i, 11).Value = Mid(Cells(i, 2).Text, 3, 1)
Cells(i, 12).Value = Right(Cells(i, 2).Text, 1)
'Figure out descending values of the digits
colbdigit1(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 12)), 1)
colbdigit2(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 12)), 2)
colbdigit3(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 12)), 3)
colbdigit4(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 12)), 4)
nextib:
Next i
'Run thru column A entries and break out digits into columns E thru H
For i = 3 To 4
If Len(Cells(i, 1).Text) = 4 Then GoTo foura
Cells(i, 5).Value = Left(Cells(i, 1).Text, 1)
Cells(i, 6).Value = Mid(Cells(i, 1).Text, 2, 1)
Cells(i, 7).Value = Right(Cells(i, 1).Text, 1)
'Figure out descending values of the digits
coladigit1(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 7)), 1)
coladigit2(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 7)), 2)
coladigit3(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 7)), 3)
GoTo nextia
foura:
Cells(i, 5).Value = Left(Cells(i, 1).Text, 1)
Cells(i, 6).Value = Mid(Cells(i, 1).Text, 2, 1)
Cells(i, 7).Value = Mid(Cells(i, 1).Text, 3, 1)
Cells(i, 8).Value = Right(Cells(i, 1).Text, 1)
'Figure out descending values of the digits
coladigit1(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 8)), 1)
coladigit2(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 8)), 2)
coladigit3(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 8)), 3)
coladigit4(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 8)), 4)
nextia:
Next i
'Read in column C entries
For i = 3 To 4
colc(i) = Cells(i, 3).Text
Next i
'
'For each entry in column A try to find a "match" in column B
'If a "match" is found, take column C entry for the "match"
'in column B and place into column D of row of column A entry
'
'Index for column A entries
For i = 3 To 4
'Index for Column B search
For j = 3 To 4
If coladigit1(i) = colbdigit1(j) And coladigit2(i) = colbdigit2(j) And _
coladigit3(i) = colbdigit3(j) And coladigit4(i) = colbdigit4(j) _
Then GoTo found
GoTo nextjsearch
found:
Cells(i, 4).Value = colc(j)
GoTo nextisearch
nextjsearch:
Next j
Cells(i, 4).Value = "No Match"
nextisearch:
Next i
End Sub

Hi, I worked up a version which allows concatenation of two matches - this uses column m and n on top of previous e thru l. If you need to allow for several more matches and concatenation that could be done.

Sub Macro1()
Dim colbdigit1(50) As Integer
Dim colbdigit2(50) As Integer
Dim colbdigit3(50) As Integer
Dim colbdigit4(50) As Integer
Dim coladigit1(50) As Integer
Dim coladigit2(50) As Integer
Dim coladigit3(50) As Integer
Dim coladigit4(50) As Integer
Dim colc(50) As String
Dim endrow As Integer
endrow = Cells(10000, 1).End(xlUp).Row
'Run thru column B entries and break out digits into columns H thru L
For i = 3 To endrow
If Len(Cells(i, 2).Text) = 4 Then GoTo fourb
Cells(i, 9).Value = Left(Cells(i, 2).Text, 1)
Cells(i, 10).Value = Mid(Cells(i, 2).Text, 2, 1)
Cells(i, 11).Value = Right(Cells(i, 2).Text, 1)
'Figure out descending values of the digits
colbdigit1(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 11)), 1)
colbdigit2(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 11)), 2)
colbdigit3(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 11)), 3)
GoTo nextib
fourb:
Cells(i, 9).Value = Left(Cells(i, 2).Text, 1)
Cells(i, 10).Value = Mid(Cells(i, 2).Text, 2, 1)
Cells(i, 11).Value = Mid(Cells(i, 2).Text, 3, 1)
Cells(i, 12).Value = Right(Cells(i, 2).Text, 1)
'Figure out descending values of the digits
colbdigit1(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 12)), 1)
colbdigit2(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 12)), 2)
colbdigit3(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 12)), 3)
colbdigit4(i) = Application.WorksheetFunction.Large(Range(Cells(i, 9), Cells(i, 12)), 4)
nextib:
Next i
'Run thru column A entries and break out digits into columns E thru H
For i = 3 To endrow
If Len(Cells(i, 1).Text) = 4 Then GoTo foura
Cells(i, 5).Value = Left(Cells(i, 1).Text, 1)
Cells(i, 6).Value = Mid(Cells(i, 1).Text, 2, 1)
Cells(i, 7).Value = Right(Cells(i, 1).Text, 1)
'Figure out descending values of the digits
coladigit1(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 7)), 1)
coladigit2(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 7)), 2)
coladigit3(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 7)), 3)
GoTo nextia
foura:
Cells(i, 5).Value = Left(Cells(i, 1).Text, 1)
Cells(i, 6).Value = Mid(Cells(i, 1).Text, 2, 1)
Cells(i, 7).Value = Mid(Cells(i, 1).Text, 3, 1)
Cells(i, 8).Value = Right(Cells(i, 1).Text, 1)
'Figure out descending values of the digits
coladigit1(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 8)), 1)
coladigit2(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 8)), 2)
coladigit3(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 8)), 3)
coladigit4(i) = Application.WorksheetFunction.Large(Range(Cells(i, 5), Cells(i, 8)), 4)
nextia:
Next i
'Read in column C entries
For i = 3 To endrow
colc(i) = Cells(i, 3).Text
Next i
'
'For each entry in column A try to find a "match" in column B
'If a "match" is found, take column C entry for the "match"
'in column B and place into column D of row of column A entry
'
'Index for column A entries
For i = 3 To endrow
'k is counter for found "matches" - reset
'to zero for next column A entry
k = 0
'Index for Column B search
For j = 3 To endrow
If coladigit1(i) = colbdigit1(j) And coladigit2(i) = colbdigit2(j) And _
coladigit3(i) = colbdigit3(j) And coladigit4(i) = colbdigit4(j) _
Then GoTo found
GoTo nextjsearch
found:
k = k + 1
Cells(i, 12 + k).Value = colc(j)
nextjsearch:
Next j
If k = 0 Then Cells(i, 13).Value = "No Match"
Next i
'This formula allows concatenation of two matches into column D
Cells(3, 4).Formula = "=IF(ISBLANK(n3),m3,CONCATENATE(m3,"" + "",n3))"
For i = 3 To endrow
Cells(3, 4).Copy Cells(i, 4)
Next i
End Sub

Good Luck! I should add this version is set up to handle 50 rows of data. To change that just change all the 50's to whatever you need.