Match 3 different values to MAX value of cell in list.

Weeble

Weeble

Board Regular
Joined
Nov 30, 2016
Messages
95
Office Version
  1. 365
Hi!

So I have a table with description that look like this.

Description
255-146-130-1600
260-110-130-1600
260-115-130-1600
260-120-130-1600
260-135-130-1600
260-146-130-1600
260-160-150-1800

The digits represents values ' Length , Width, Height and Weight '

In a different sheet I have each value of a product written down in single cells.

LengthWidthHeightWeight
1401155106
12011380100
2561121201680

I this table I would like to add a column that matches the closest max value of the description.

LengthWidthHeightWeightDecription match
140 1155106255-146-130-1600
12011380100255-146-130-1600
2561121201680260-160-150-1800

The first two match with description ' 255-146-130-1600 ' due to all critiera fits in the match and takes the lowest length.
The last one matches with description ' 260-160-150-1800 ' Since length does not fit in '255' and weight is above '1600'.
Writing this is seams very complicated. But maybe somone here has a good idea of what I want to achieve, and might have done it before.
How do i match the closest MAX critieria with my Description list?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
I have assumed you list of description is in Column A of sheet 1.
I have assumed your dimensions are in columns A to D of sheet 2
I have put the description into Column E
I have assumed that "best" match is the one where the sum of the deltas in dimensions is the minimum
VBA Code: 
Sub test()
With Worksheets("sheet1")
 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
 descar = Range(.Cells(1, 1), .Cells(lastrow, 1))
End With
With Worksheets("sheet2")
 lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
 dimar = Range(.Cells(1, 1), .Cells(lastrow2, 4))
 outarr = Range(.Cells(1, 5), .Cells(lastrow2, 5))
For j = 2 To lastrow2
     maxdelta = 9E+16
     imatch = 0
    For i = 2 To lastrow
     dimens = Split(descar(i, 1), "-")
     
       deltal = dimens(1) - dimar(j, 1)
       If deltal < 0 Then Exit For
       deltaw = dimens(1) - dimar(j, 2)
       If deltaw < 0 Then Exit For
       deltah = dimens(1) - dimar(j, 3)
       If deltah < 0 Then Exit For
       deltawt = dimens(1) - dimar(j, 4)
       If deltawt < 0 Then Exit For
       sumdelta = deltal + deltaw + deltah + deltawt
       If sumdelta < maxdelta Then
        imatch = i
       End If
      Next i
        If imatch > 0 Then
        outarr(j, 1) = descar(imatch, 1)
        Else
        outarr(j, 1) = "No match found"
        End If
Next j
 Range(.Cells(1, 5), .Cells(lastrow2, 5)) = outarr
 
End With

End Sub
 
Upvote 0
offthelip said:
I have assumed you list of description is in Column A of sheet 1.
I have assumed your dimensions are in columns A to D of sheet 2
I have put the description into Column E
I have assumed that "best" match is the one where the sum of the deltas in dimensions is the minimum
VBA Code: 
Sub test()
With Worksheets("sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
descar = Range(.Cells(1, 1), .Cells(lastrow, 1))
End With
With Worksheets("sheet2")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
dimar = Range(.Cells(1, 1), .Cells(lastrow2, 4))
outarr = Range(.Cells(1, 5), .Cells(lastrow2, 5))
For j = 2 To lastrow2
     maxdelta = 9E+16
     imatch = 0
    For i = 2 To lastrow
     dimens = Split(descar(i, 1), "-")
    
       deltal = dimens(1) - dimar(j, 1)
       If deltal < 0 Then Exit For
       deltaw = dimens(1) - dimar(j, 2)
       If deltaw < 0 Then Exit For
       deltah = dimens(1) - dimar(j, 3)
       If deltah < 0 Then Exit For
       deltawt = dimens(1) - dimar(j, 4)
       If deltawt < 0 Then Exit For
       sumdelta = deltal + deltaw + deltah + deltawt
       If sumdelta < maxdelta Then
        imatch = i
       End If
      Next i
        If imatch > 0 Then
        outarr(j, 1) = descar(imatch, 1)
        Else
        outarr(j, 1) = "No match found"
        End If
Next j
Range(.Cells(1, 5), .Cells(lastrow2, 5)) = outarr

End With

End Sub
Click to expand...

First of, thank you very much!

When I run the code the majority (8500) rows got "No match found". And about 2500 rows got the match of only 2 dimensions.
255-146-130-1600
260-305-150-1800

My full description list looks like this. If it helps.
Description
255-146-130-1600
260-110-130-1600
260-115-130-1600
260-120-130-1600
260-135-130-1600
260-146-130-1600
260-146-150-1800
260-305-150-1800
260-60-130-1600
260-80-130-1600
260-85-130-1600
260-95-130-1600
#VÄRDEFEL!​
340-105-130-1600
340-146-130-1600
340-146-130-2200
340-85-130-2200
340-87-130-1600
1300-550-300-117000
1450-550-500-97000
1500-600-800-99999
250-550-140-10500
250-550-225-16500
270-335-85-6850
300-550-130-11700
320-550-260-24900
420-450-130-16000
420-550-130-16000
450-550-300-40500
490-550-225-33000
520-550-300-46800
610-550-130-23500
620-550-240-44500
650-550-500-50000
820-550-240-59000
840-550-300-75000
1220-70-126-2000
440-100-126-1500
440-40-126-1500
440-50-126-1500
440-60-126-1500
440-80-126-1500
440-90-126-1500
500-360-130-4500
620-70-126-1500
920-70-126-2000
140-25-75-250
40-16-20-25
50-35-30-250
50-35-45-250
50-50-30-250
50-50-45-250
50-60-30-250
80-15-30-100
80-15-45-100
80-35-30-100
80-35-45-100
80-50-30-100
80-50-45-100
120-40-42-500
130-110-85-1050
130-115-85-1050
130-146-85-1050
130-165-85-1050
130-305-85-800
130-40-42-500
130-40-85-1050
130-60-42-400
130-60-85-800
130-85-85-1050
130-95-85-1050
140-146-85-1050
150-146-85-1050
165-65-85-1050
201-80-85-1050
1050
#VÄRDEFEL!​
1000_Test
2000_Test
3000_Test
XLARGE
130-100-130-1050
130-146-130-1050
130-180-130-1050
130-305-130-1050
130-80-120-1050
130-80-130-1050
140-260-130-1050
MODULTYP
1500-600-800-99999
Owerflow-plats
Virtuell plats
115-30-45-250
115-42-45-250
115-63-52-250
260-18-30-100
PLOCK
#VÄRDEFEL!​
405-53-80-2000
405-68-80-2000
610-450-130-23500
240-220-140-2000
280-880-240-8000

Dimenstions that did not get matches varied, but here are some examples

440​
11​
55​
106,24​
No match found
900​
11,5​
63​
316​
No match found
251​
17​
90​
842,4​
No match found
251​
17​
90​
842,4​
No match found
251​
17​
90​
842,4​
No match found
251​
17​
90​
842,4​
No match found
251​
17​
90​
842,5​
No match found
251​
17​
90​
842,4​
No match found
251​
17​
90​
842,4​
No match found
251​
17​
90​
842,4​
No match found
251​
17​
90​
842,5​
No match found
251​
17​
90​
842,5​
No match found
251​
17​
90​
842,4​
No match found
240​
17​
80​
54,264​
No match found
360​
17​
92​
188,7​
No match found
244​
17,7​
56​
130​
No match found
440​
18​
80​
229,2​
No match found
440​
18​
80​
229,2​
No match found
1200​
18​
112,5​
1178​
No match found

Matching examples:

120​
30​
80​
43​
255-146-130-1600
120​
30​
80​
100​
255-146-130-1600
120​
31​
80​
27​
255-146-130-1600
110​
32​
100​
95​
260-305-150-1800
110​
32​
100​
95​
260-305-150-1800
120​
32​
80​
8,48​
255-146-130-1600



​
​
​
​
 
Upvote 0
How would I go about tying to get the closest MAX value ?

Say it's 120 length and the closest value would be 130. Instead of the highest MAX value.
 
Upvote 0
If you are interested in considering a formula-based approach, here is an idea. It requires the formation of a helper table where the text string is split to obtain the length, width, and height for convenient reference by the main formula. The main formula uses AGGREGATE functions that determine whether all dimensional (length, width, and height) requirements are met, and if they are, the ratio of the product dimension (in the original text string) to the corresponding dimension in the input table is taken. This is done for length, width, and height, and 1 is subtracted from each ratio to determine the relative amount by which the (text string) dimension exceeds the input table dimension. These quantities are summed and the minimum determined...meaning that particular items comes closest to satisfying the match criteria.

You can try this out by clicking on the clipboard icon in the upper left (at intersection of column and row labels) and pasting into a blank worksheet in cell A1. I'm assuming the weight was to be ignored.
MrExcel20200915.xlsx
ABCDEFGHI
1Helper table
2DescriptionLWHWt
3255-146-130-16002551461301600
4260-110-130-16002601101301600
5260-115-130-16002601151301600
6260-120-130-16002601201301600
7260-135-130-16002601351301600
8260-146-130-16002601461301600
9260-160-150-18002601601501800
10
11
12
13LengthWidthHeightWeightDecription match
1425914655106260-146-130-1600
1512011380100260-115-130-1600
162561121201680260-115-130-1600
172601601501850260-160-150-1800
182601101201850260-110-130-1600
Weeble
Cell Formulas
RangeFormula
F3:F9F3=LEFT($A3,FIND("-",$A3)-1)
G3:G9G3=MID($A3,SEARCH("x",SUBSTITUTE($A3,"-","x",1))+1,SEARCH("y",SUBSTITUTE($A3,"-","y",2))-SEARCH("x",SUBSTITUTE($A3,"-","x",1))-1)
H3:H9H3=MID($A3,SEARCH("y",SUBSTITUTE($A3,"-","y",2))+1,SEARCH("z",SUBSTITUTE($A3,"-","z",3))-SEARCH("y",SUBSTITUTE($A3,"-","y",2))-1)
I3:I9I3=RIGHT($A3,LEN($A3)-SEARCH("z",SUBSTITUTE($A3,"-","z",3)))
E14:E18E14=IFERROR(INDEX($A$3:$A$9,AGGREGATE(15,6,(ROW($A$3:$A$9)-ROW($A$2))/(AGGREGATE(15,6,($F$3:$F$9/A14-1)/($F$3:$F$9/A14>=1)+($G$3:$G$9/B14-1)/($G$3:$G$9/B14>=1)+($H$3:$H$9/C14-1)/($H$3:$H$9/C14>=1),1)=(($F$3:$F$9/A14-1)/($F$3:$F$9/A14>=1)+($G$3:$G$9/B14-1)/($G$3:$G$9/B14>=1)+($H$3:$H$9/C14-1)/($H$3:$H$9/C14>=1))),1)),"no match")
 
Upvote 0
This modification to my code will deal with the commas which is causing all of the "No Match Found" that you list
VBA Code: 
Sub test()
With Worksheets("sheet1")
 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
 descar = Range(.Cells(1, 1), .Cells(lastrow, 1))
End With
With Worksheets("sheet2")
 lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
 dimar = Range(.Cells(1, 1), .Cells(lastrow2, 4))
 outarr = Range(.Cells(1, 5), .Cells(lastrow2, 5))
For j = 2 To lastrow2
     maxdelta = 9E+16
     imatch = 0
    For i = 2 To lastrow
     dimens = Split(descar(i, 1), "-")
     
       deltal = dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 1), 0)
       If deltal < 0 Then Exit For
       deltaw = dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 2), 0)
       If deltaw < 0 Then Exit For
       deltah = dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 3), 0)
       If deltah < 0 Then Exit For
       deltawt = dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 4), 0)
       If deltawt < 0 Then Exit For
       sumdelta = deltal + deltaw + deltah + deltawt
       If sumdelta < maxdelta Then
        imatch = i
       End If
      Next i
        If imatch > 0 Then
        outarr(j, 1) = descar(imatch, 1)
        Else
        outarr(j, 1) = "No match found"
        End If
Next j
 Range(.Cells(1, 5), .Cells(lastrow2, 5)) = outarr
 
End With

End Sub

In terms of what to do about your comment "How would I go about tying to get the closest MAX value ?"
this is something only you can answer, you are trying to design an algorithm to select the "best" match from a system with 4 different dimensions. One criteria is easy all the dimensions must be less than the match, however after that; how do you want to select what is the best match? is one dimension more important the any other?
The algorithm I chose, works out the delta between the sheet 1 dimension and the sheet 2 dimensions , sums these 4 values and then take the minimum value of that sum as the "best" the logic that does this is in these lines of code, you can change it to what ever you like:
VBA Code: 
       sumdelta = deltal + deltaw + deltah + deltawt
       If sumdelta < maxdelta Then
        imatch = i
       End If
 
Upvote 0
As an example if you want to use the algorithm that the excellent bit of work by KRICE uses, you can change the code to this:
VBA Code: 
       deltal = (dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 1), 0)) / Application.WorksheetFunction.RoundUp(dimar(j, 1), 0)
       If deltal < 0 Then Exit For
       deltaw = (dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 2), 0)) / Application.WorksheetFunction.RoundUp(dimar(j, 2), 0)
       If deltaw < 0 Then Exit For
       deltah = (dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 3), 0)) / Application.WorksheetFunction.RoundUp(dimar(j, 3), 0)
       If deltah < 0 Then Exit For
       deltawt = (dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 4), 0)) / Application.WorksheetFunction.RoundUp(dimar(j, 4), 0)
       If deltawt < 0 Then Exit For
       sumdelta = deltal + deltaw + deltah + deltawt
       If sumdelta < maxdelta Then
        imatch = i
       End If
 
Upvote 0
offthelip said:
As an example if you want to use the algorithm that the excellent bit of work by KRICE uses, you can change the code to this:
VBA Code: 
       deltal = (dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 1), 0)) / Application.WorksheetFunction.RoundUp(dimar(j, 1), 0)
       If deltal < 0 Then Exit For
       deltaw = (dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 2), 0)) / Application.WorksheetFunction.RoundUp(dimar(j, 2), 0)
       If deltaw < 0 Then Exit For
       deltah = (dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 3), 0)) / Application.WorksheetFunction.RoundUp(dimar(j, 3), 0)
       If deltah < 0 Then Exit For
       deltawt = (dimens(1) - Application.WorksheetFunction.RoundUp(dimar(j, 4), 0)) / Application.WorksheetFunction.RoundUp(dimar(j, 4), 0)
       If deltawt < 0 Then Exit For
       sumdelta = deltal + deltaw + deltah + deltawt
       If sumdelta < maxdelta Then
        imatch = i
       End If
Click to expand...
Amazing.
Thank you kindly for your help ! I am going to play around abit to see if I can find the best fit
 
Upvote 0
To follow up with the comments by @offthelip, the measure for what constitutes the "best" match needs to defined. The approach I described determines the proportion by which each dimensional component is undersized relative to the corresponding dimensions in the original list of product descriptions. A simple sum of those undersized proportions, and choosing the minimum of such, establishes one metric for finding the "best" match. But other metrics could be defined and might be more preferred. As an example of the issue, suppose the original list of product descriptions includes:
260-113-130-1600
259-114-130-1600
If we are given the following and asked to find the best match, which would be the correct answer?
Length​
Width​
Height​
Weight​
Description match​
​
259​
​
113​
​
130​
​
1600​
260-113-130-1600​
You'll see that the sum-of-undersized-ratios approach selects 260-113-130-1600 because it is undersized by one unit on the length dimension, or undersized by 1/260. The other potential choice is also undersized by 1 unit (on the width dimension), but the undersized ratio is 1/114, which is a larger proportion compared to 1/260. The smallest sum of undersized ratios leads to 260-113-130-1600 being selected. If any particular dimension is more important than the others, then the metric for determining the best match would probably change.

Could you also clarify two things:
1. Is weight to be considered in the matching? If so, then the formula approach I described needs to be amended to include that as well.
2. Do all of the dimensions use the same length scale? In other words, are length, width, and height all expressed in the same unit of length (e.g. all in millimeters)?
 
Last edited:
Upvote 0
KRice said:
To follow up with the comments by @offthelip, the measure for what constitutes the "best" match needs to defined. The approach I described determines the proportion by which each dimensional component is undersized relative to the corresponding dimensions in the original list of product descriptions. A simple sum of those undersized proportions, and choosing the minimum of such, establishes one metric for finding the "best" match. But other metrics could be defined and might be more preferred. As an example of the issue, suppose the original list of product descriptions includes:
260-113-130-1600
259-114-130-1600
If we are given the following and asked to find the best match, which would be the correct answer?
Length​
Width​
Height​
Weight​
Description match​
​
259​
​
113​
​
130​
​
1600​
260-113-130-1600​
You'll see that the sum-of-undersized-ratios approach selects 260-113-130-1600 because it is undersized by one unit on the length dimension, or undersized by 1/260. The other potential choice is also undersized by 1 unit (on the width dimension), but the undersized ratio is 1/114, which is a larger proportion compared to 1/260. The smallest sum of undersized ratios leads to 260-113-130-1600 being selected. If any particular dimension is more important than the others, then the metric for determining the best match would probably change.

Could you also clarify two things:
1. Is weight to be considered in the matching? If so, then the formula approach I described needs to be amended to include that as well.
2. Do all of the dimensions use the same length scale? In other words, are length, width, and height all expressed in the same unit of length (e.g. all in millimeters)?
Click to expand...

Giving two examples like this. The best matches would be.
240​
17​
80​
54,264​
360​
17​
92​
188,7​

match 1 : 255-146-130-1600
priority 1 : Length going for the closest max value
priority 2: If Length fits as priority 1, closest max value of width is compared
priority 3: Make sure the location can handle the height of the product aswell.
Weight beeing a factor allthough less of a factor, just make sure that it's <= .

match 2 : 420-450-130-16000


1. Yes to a certein degree weight will factor in.
2. They are all in centimeters, and weight in kilos.

And thank you kindly for the help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
296
madi1004
M
B
  • Solved
Addition to INDEX formula to choose which table to reference
2
Replies
13
Views
720
Peter_SSs
Peter_SSs
M
  • Question
Match combobox name on userform with shee name and fill empty cell
Replies
0
Views
135
Maklil
M
A
  • Solved
INDEX MATCH or similar with multiple criteria?
Replies
3
Views
203
Fluff
Fluff
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
323
awoohaw
awoohaw

Forum statistics

Threads
1,215,335
Messages
6,124,327
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top