find the best match with sizing based on a certain sum VBA

S

Sotos13

New Member
Joined
Mar 8, 2019
Messages
42
Hello everyone,

i have this problem i have to solve. It's not that easy but here it is .... In column A i have a list of sizes maybe Hundreds Thousands.. and they might be from 10 to 100 in column B. i also have a certain target range for example 95 to 100.
i want to add all numbers in column B in order to result from 90 to 100(my target range) for example A1 with A10 = 100, A2+A3+ A11 =90, A4+A6 =90, then A5+A12 = 95 and so on.. The numbers which are used cannot be used again. The result will be a table with this info as the table below SIZE 1 and SIZE 10 100 etc or A1 and A10 something like that. I know it's hard but if anyone has an idea please be free to share

[TABLE="width: 695"]
<tbody>[TR]
[TD]SIZE 1[/TD]
[TD="align: right"] 90[/TD]
[TD] A1+A10[/TD]
[TD="align: right"] 100[/TD]
[TD] Range 90- 100[/TD]
[TD][/TD]
[TD="colspan: 2"]RESULTS[/TD]
[/TR]
[TR]
[TD]SIZE 2[/TD]
[TD="align: right"] 50[/TD]
[TD] A2+A3+A11[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 1 and SIZE 10[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 3[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 2 and SIZE 3 and SIZE 11[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]SIZE 4[/TD]
[TD="align: right"]60[/TD]
[TD] A4+A6[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 4 and SIZE 6[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]SIZE 5[/TD]
[TD="align: right"]70[/TD]
[TD] A5+A12[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 5 and SIZE 12[/TD]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD]SIZE 6[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 7 and SIZE 16[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 7[/TD]
[TD="align: right"]80[/TD]
[TD] A7+A16[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 8[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 9[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 10[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 11[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 12[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 13[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 14[/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 15[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 16[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 695"]
<tbody>[TR]
[TD]Thanks[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
I give you an approach only with the sum of 2 numbers.
Put your data as shown in the following figure.
In A your size
In B your numbers
In D2 from
In E2 To


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:46.57px;" /><col style="width:64.63px;" /><col style="width:23.76px;" /><col style="width:27.56px;" /><col style="width:32.32px;" /><col style="width:25.66px;" /><col style="width:115.01px;" /><col style="width:26.61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">SIZE</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">NUMBERS</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">RANGE INI</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">RANGE END</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">RESULTS</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:11pt; ">SIZE 1</td><td style="font-size:11pt; text-align:right; ">90</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; text-align:right; ">90</td><td style="font-size:11pt; text-align:right; ">100</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 1 and SIZE 10</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:11pt; ">SIZE 2</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 2 and SIZE 8</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:11pt; ">SIZE 3</td><td style="font-size:11pt; text-align:right; ">30</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 3 and SIZE 5</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:11pt; ">SIZE 4</td><td style="font-size:11pt; text-align:right; ">60</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 4 and SIZE 6</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:11pt; ">SIZE 5</td><td style="font-size:11pt; text-align:right; ">70</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 7 and SIZE 16</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:11pt; ">SIZE 6</td><td style="font-size:11pt; text-align:right; ">30</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 9 and SIZE 11</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:11pt; ">SIZE 7</td><td style="font-size:11pt; text-align:right; ">80</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 12 and SIZE 14</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:11pt; ">SIZE 8</td><td style="font-size:11pt; text-align:right; ">40</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 15</td><td style="font-size:11pt; text-align:right; ">95</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:11pt; ">SIZE 9</td><td style="font-size:11pt; text-align:right; ">80</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:11pt; ">SIZE 10</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:11pt; ">SIZE 11</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:11pt; ">SIZE 12</td><td style="font-size:11pt; text-align:right; ">25</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:11pt; ">SIZE 13</td><td style="font-size:11pt; text-align:right; ">35</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:11pt; ">SIZE 14</td><td style="font-size:11pt; text-align:right; ">75</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:11pt; ">SIZE 15</td><td style="font-size:11pt; text-align:right; ">95</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:11pt; ">SIZE 16</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr></table>
 
Upvote 0
Run this code:

Code: 
Sub find_best_match()
    Dim lr As Long, t_ini As Long, t_end As Long, goal As Long, cad As String
    Dim i As Long, j As Long, k As Long, r As Range, b As Range, cell As String
    
    Application.ScreenUpdating = False
    
    Range("C:C").ClearContents
    Range("G2:H" & Rows.Count).ClearContents
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    t_ini = Range("D2")
    t_end = Range("E2")
    k = 2
    
    For i = 2 To lr
        cad = Cells(i, "A")
        goal = t_end - Cells(i, "B")
        If Cells(i, "C").Value = "" Then
            If goal > 0 Then
                For j = 1 To 3
                    Set r = Range(Cells(i + 1, "B"), Cells(lr, "B"))
                    Set b = r.Find(goal, LookAt:=xlWhole, LookIn:=xlValues)
                    If Not b Is Nothing Then
                        cell = b.Address
                        Do
                            If b.Offset(0, 1).Value = "" Then
                                cad = cad & " and " & b.Offset(0, -1)
                                Cells(k, "G").Value = cad
                                Cells(k, "H").Value = Cells(i, "B").Value + Cells(b.Row, "B").Value
                                Cells(i, "C").Value = "x"
                                Cells(b.Row, "C").Value = "x"
                                k = k + 1
                                Exit For
                            End If
                            Set b = r.FindNext(b)
                        Loop While Not b Is Nothing And b.Address <> cell
                        
                    End If
                    If j = 1 Then
                        goal = t_ini - Cells(i, "B")
                    Else
                        goal = ((t_ini + t_end) / 2) - Cells(i, "B")
                    End If
                Next
            Else
                Cells(k, "G").Value = cad
                Cells(k, "H").Value = Cells(i, "B").Value
                Cells(i, "C").Value = "x"
            End If
            If Cells(i, "B").Value >= t_ini And Cells(i, "B").Value <= t_end Then
                Cells(k, "G").Value = cad
                Cells(k, "H").Value = Cells(i, "B").Value
                Cells(i, "C").Value = "x"
            End If
        End If
    Next
    MsgBox "Done"
End Sub
 
Upvote 0
Hi Dante

Really impressive and it works , but as you said only for two numbers. For example i changed the numbers and it leaves alot of them outide the calculation.See table. What should we change in order to add more numbers?

[TABLE="width: 592"]
<colgroup><col span="3"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]size[/TD]
[TD]numbers[/TD]
[TD][/TD]
[TD]Range in[/TD]
[TD]Range to[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 1[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD]SIZE 3 and SIZE 8[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]SIZE 2[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 4 and SIZE 7[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 3[/TD]
[TD="align: right"]30[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 9 and SIZE 20[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 4[/TD]
[TD="align: right"]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 10 and SIZE 16[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 5[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SIZE 19 and SIZE 22[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 6[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 7[/TD]
[TD="align: right"]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 8[/TD]
[TD="align: right"]60[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 9[/TD]
[TD="align: right"]40[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 10[/TD]
[TD="align: right"]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 11[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 12[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 13[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 14[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 15[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 16[/TD]
[TD="align: right"]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 17[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 18[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 19[/TD]
[TD="align: right"]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 20[/TD]
[TD="align: right"]60[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 21[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 22[/TD]
[TD="align: right"]50[/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Thanks again[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
also Dante, the numbers that are over a limit are nit presented in the results ..see the table for size 6 120


[TABLE="width: 611"]
<colgroup><col span="3"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]size[/TD]
[TD]numbers[/TD]
[TD][/TD]
[TD]Range in[/TD]
[TD]Range to[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]80[/TD]
[TD]x[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]125[/TD]
[TD][/TD]
[TD]1 and 21[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]110[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2 and 29[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]70[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3 and 9[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]115[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4 and 75[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]60[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5 and 25[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]120[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7 and 83[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]105[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8 and 26[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]80[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11 and 42[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]55[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12 and 19[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]120[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13 and 67[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]70[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14 and 78[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15 and 34[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]60[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16 and 45[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]60[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17 and 44[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]40[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18 and 30[/TD]
[TD="align: right"]125



Regards and thanks for all your help[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I updated the macro

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:46.57px;" /><col style="width:66.53px;" /><col style="width:13.31px;" /><col style="width:23.76px;" /><col style="width:31.37px;" /><col style="width:25.66px;" /><col style="width:280.4px;" /><col style="width:35.17px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">SIZE</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">NUMBERS</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">INI</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">END</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; "> </td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">RESULTS</td><td style="background-color:#ffff00; font-weight:bold; font-size:11pt; text-align:center; ">SUM</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:11pt; ">SIZE 1</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; text-align:right; ">90</td><td style="font-size:11pt; text-align:right; ">100</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 1 and SIZE 2 and SIZE 3 and SIZE 9</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:11pt; ">SIZE 2</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 4 and SIZE 7</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:11pt; ">SIZE 3</td><td style="font-size:11pt; text-align:right; ">30</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 5 and SIZE 6 and SIZE 8 and SIZE 13</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:11pt; ">SIZE 4</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 10 and SIZE 16</td><td style="font-size:11pt; text-align:right; ">100</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:11pt; ">SIZE 5</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 11 and SIZE 12 and SIZE 19</td><td style="font-size:11pt; text-align:right; ">95</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:11pt; ">SIZE 6</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 14 and SIZE 15 and SIZE 21</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:11pt; ">SIZE 7</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; ">SIZE 17 and SIZE 18 and SIZE 20</td><td style="font-size:11pt; text-align:right; ">90</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:11pt; ">SIZE 8</td><td style="font-size:11pt; text-align:right; ">60</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:11pt; ">SIZE 9</td><td style="font-size:11pt; text-align:right; ">40</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:11pt; ">SIZE 10</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:11pt; ">SIZE 11</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:11pt; ">SIZE 12</td><td style="font-size:11pt; text-align:right; ">25</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:11pt; ">SIZE 13</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:11pt; ">SIZE 14</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:11pt; ">SIZE 15</td><td style="font-size:11pt; text-align:right; ">30</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:11pt; ">SIZE 16</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:11pt; ">SIZE 17</td><td style="font-size:11pt; text-align:right; ">20</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:11pt; ">SIZE 18</td><td style="font-size:11pt; text-align:right; ">10</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:11pt; ">SIZE 19</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:11pt; ">SIZE 20</td><td style="font-size:11pt; text-align:right; ">60</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:11pt; ">SIZE 21</td><td style="font-size:11pt; text-align:right; ">40</td><td style="font-size:11pt; ">x</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:11pt; ">SIZE 22</td><td style="font-size:11pt; text-align:right; ">50</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr></table>

Try:

Code: 
Option Explicit
Sub find_best_match()
    Dim lr As Long, t_ini As Long, t_end As Long, contador As Long, n As Long
    Dim i As Long, k As Long, m As Long, fut As Long, res As Long, f As Long
    Dim continue As Boolean, cad As String, yaesta As Boolean
    Dim filas As New Collection
    
    Application.ScreenUpdating = False
    
    Range("C:C").ClearContents
    Range("G2:H" & Rows.Count).ClearContents
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    t_ini = Range("E2")
    t_end = Range("E2")
    k = 2
    
    For i = 2 To lr
        cad = ""
        If Cells(i, "C").Value = "" Then
            filas.Add i
            res = Cells(i, "B").Value
            continue = True
            contador = i
            n = 1
            Do While contador < lr
                For m = contador + 1 To lr
                    yaesta = False
                    For f = 1 To filas.Count
                        If m = filas(f) Then
                            yaesta = True
                            Exit For
                        End If
                    Next
                    
                    If yaesta = False Then
                        If Cells(m, "C").Value = "" Then
                            fut = res + Cells(m, "B").Value
                            If fut >= t_ini And fut <= t_end Then
                                filas.Add m
                                For f = 1 To filas.Count
                                    cad = cad & " and " & Cells(filas(f), "A").Value
                                    Cells(filas(f), "C").Value = "x"
                                Next
                                Cells(k, "G").Value = Mid(cad, 6)
                                Cells(k, "H").Value = fut
                                k = k + 1
                                continue = False
                                Set filas = Nothing
                                Exit For
                            End If
                        End If
                    End If
                Next m
                If continue = False Then Exit Do
                contador = contador + 1
                If Cells(contador, "C").Value = "" Then
                    res = res + Cells(contador, "B")
                    filas.Add contador
                End If
                n = n + 1
                If n > 4 Then
                    t_ini = Range("D2")
                    Set filas = Nothing
                    i = i - 1
                    Exit Do
                End If
            Loop
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub
 
Upvote 0
Hi Dante
For some reason it not responding.
I thought it might had to do with the t_ini = Range("E2") t_end = Range("E2") which is the same so i change it to G2 but again nothing changed.
it'like it doesnt stop looping i think.

Thanks
Sotos
 
Upvote 0
Sotos13 said:
Hi Dante
For some reason it not responding.
I thought it might had to do with the t_ini = Range("E2") t_end = Range("E2") which is the same so i change it to G2 but again nothing changed.
it'like it doesnt stop looping i think.

Thanks
Sotos
Click to expand...


Perform a test with the data that is in the example.
From 90 To 100
This is correct:
Code: 
    t_ini = Range ("E2")
    t_end = Range ("E2")

It is to find the best. Later, if it does not find, the code changes to:


Code: 
                   t_ini = Range ("D2")

-----------------
Or write the data you are using to review them.
 
Upvote 0
Hi Dante

i tried with your data and it worked fine. then i added about 30 more lines.
Again it started not responding. I closed the window to restart excel and the result appeared. But it left the last numbers....Is there a chance that it cant find more than 4 sums?


[TABLE="width: 914"]
<colgroup><col span="6"><col><col></colgroup><tbody>[TR]
[TD]SIZE[/TD]
[TD]NUMBERS[/TD]
[TD] [/TD]
[TD]INI[/TD]
[TD]END[/TD]
[TD] [/TD]
[TD]RESULTS[/TD]
[TD]SUM[/TD]
[/TR]
[TR]
[TD]SIZE 1[/TD]
[TD]10[/TD]
[TD]x[/TD]
[TD]90[/TD]
[TD]100[/TD]
[TD] [/TD]
[TD]SIZE 1 and SIZE 2 and SIZE 3 and SIZE 9[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]SIZE 2[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 4 and SIZE 7[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]SIZE 3[/TD]
[TD]30[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 5 and SIZE 6 and SIZE 8 and SIZE 13[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]SIZE 4[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 10 and SIZE 16[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]SIZE 5[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 11 and SIZE 12 and SIZE 19[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]SIZE 6[/TD]
[TD]10[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 14 and SIZE 15 and SIZE 21[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]SIZE 7[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 17 and SIZE 18 and SIZE 20[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]SIZE 8[/TD]
[TD]60[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 22 and SIZE 26[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 9[/TD]
[TD]40[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 23 and SIZE 24 and SIZE 25 and SIZE 29[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 10[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 27 and SIZE 28 and SIZE 30 and SIZE 31[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]SIZE 11[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]SIZE 32 and SIZE 33 and SIZE 34[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]SIZE 12[/TD]
[TD]25[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 13[/TD]
[TD]10[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 14[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 15[/TD]
[TD]30[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 16[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 17[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 18[/TD]
[TD]10[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 19[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 20[/TD]
[TD]60[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 21[/TD]
[TD]40[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 22[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 23[/TD]
[TD]10[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 24[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SIZE 25[/TD]
[TD]30[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 26[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 27[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 28[/TD]
[TD]10[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 29[/TD]
[TD]40[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 30[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 31[/TD]
[TD]10[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 32[/TD]
[TD]20[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 33[/TD]
[TD]30[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 34[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 35[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 36[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 37[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 38[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 39[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 40[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 41[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 42[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 43[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 44[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 45[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 46[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 47[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 48[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SIZE 49[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Done, I already solved it

Code: 
Option Explicit
Sub find_best_match()
    Dim lr As Long, t_ini As Long, t_end As Long, contador As Long, n As Long
    Dim i As Long, k As Long, m As Long, fut As Long, res As Long, f As Long
    Dim continue As Boolean, cad As String, yaesta As Boolean
    Dim filas As New Collection
    
    Application.ScreenUpdating = False
    
    Range("C:C").ClearContents
    Range("G2:H" & Rows.Count).ClearContents
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    t_ini = Range("D2")
    t_end = Range("E2")
    k = 2
    
    For i = 2 To lr
        cad = ""
        If Cells(i, "C").Value = "" Then
            filas.Add i
            res = Cells(i, "B").Value
            continue = True
            contador = i
            n = 1
            Do While contador < lr
                For m = contador + 1 To lr
                    yaesta = False
                    For f = 1 To filas.Count
                        If m = filas(f) Then
                            yaesta = True
                            Exit For
                        End If
                    Next
                    
                    If yaesta = False Then
                        If Cells(m, "C").Value = "" Then
                            fut = res + Cells(m, "B").Value
                            If fut >= t_ini And fut <= t_end Then
                                filas.Add m
                                For f = 1 To filas.Count
                                    cad = cad & " and " & Cells(filas(f), "A").Value
                                    Cells(filas(f), "C").Value = "x"
                                Next
                                Cells(k, "G").Value = Mid(cad, 6)
                                Cells(k, "H").Value = fut
                                k = k + 1
                                continue = False
                                Set filas = Nothing
                                Exit For
                            End If
                        End If
                    End If
                Next m
                If continue = False Then Exit Do
                contador = contador + 1
                If Cells(contador, "C").Value = "" Then
                    fut = res + Cells(contador, "B").Value
                    If fut < t_end Then
                        res = res + Cells(contador, "B")
                        filas.Add contador
                    End If
                End If
                'n = n + 1
                'If n > 10 Then
                '    a = a
                '    t_ini = Range("D2")
                '    Set filas = Nothing
                '    i = i - 1
                '    Exit Do
                'End If
            Loop
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Diagonal Sum in excel table via VBA code
2
Replies
11
Views
507
myall_blues
myall_blues
J
  • Solved
IF and AND formulas for multiple criteria
Replies
6
Views
676
Peter_SSs
Peter_SSs
MarkSELA
  • Question Question
Multiple choice question generator using randomly selected Excel ranges
Replies
3
Views
365
MarkSELA
MarkSELA
V
  • Solved
SUBSTITUTE function
Replies
3
Views
298
Fluff
Fluff
gtd526
  • Question Question
Concatenate cells Before a certain value
2
Replies
10
Views
621
etaf
etaf

Forum statistics

Threads
1,224,747
Messages
6,180,716
Members
452,995
Latest member
isldboy

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