Formula to pick numbers to add to another number without going over 20,000

[rplohocky]

Hello,
Is there a way for excel to look in a column at a list of numbers and then choose the ones that will add up to 20,000 without going over and then either mark or ignore the ones it has chosen then repeat until all the numbers in the column have been accounted for?

I have a column of numbers that like 600, 1000, 400, 5000 and so on, these numbers represent feet. I can order reels of wire up to 20,000 feet. I would then assign each of the numbers to be pulled from a specific reel.
Example: 600+2000+1500+8000+4000+400+3200=19,700 (this is close enough to 20k)
After this one excel would look at the remaining numbers in the column and repeat until they are all accounted for.

Thanks for any input!

 

[Snakehips]

@rplohocky
What is the scale of this in terms of typical max qty of numbers to allocate?
Can you accept a via solution?

 

[rplohocky]

Snakehips, (cool name BTW)
The column would have maybe 100 rows max. What is a via solution?

 

[Snakehips]

Sorry, via = auto-fill typo! Should read vba. Macro.

Also, how many reels might be typical for 100 rows ?

 

[rplohocky]

Excel, however it can do it, should tell me how many reels of 20k and the left over size. So if it says i need two 20k reels but there is say 15,500 left then i would order 2 20k reels and 1 at 15,500.

 

[Snakehips]

Do you have a preference for trying to allocate numbers in the list on a top down basis or can any number be allocated to any reel?

 

[Snakehips]

@rplohocky Here is my best shot at this. Based on XL365, which you have.
It provides both manual and automatic possibilities for allocating wire lengths.

MrXL_Aug.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3		Job Ref	Wire Length	Allocated To Reel #		Reel #	Max Reel Length	Allocated	Balance			Un-Allocated Jobs	Un-Allocated Length	Find
4		Job 1	600	1		1	20,000	20,000	0			0	0	@
5		Job 2	100	4		2	20,000	19,970	30			Job Ref	Un-Allocated	Row
6		Job 3	150	4		3	20,000	19,995	5
7		Job 4	2,000	3		4	15,000	8,580	6,420
8		Job 5	1,500	4		5	20,000	0	20,000
9		Job 6	350	4		6	20,000	0	20,000
10		Job 7	4,000	3		7	20,000	0	20,000
11		Job 8	370	4		8	20,000	0	20,000
12		Job 9	400	1		9	20,000	0	20,000
13		Job 10	390	4		10	20,000	0	20,000
14		Job 11	8,000	2
15		Job 12	3,200	4
16		Job 13	200	4
17		Job 14	250	1
18		Job 15	250	3
19		Job 16	250	4
20		Job 17	250	4
21		Job 18	250	4
22		Job 19	5,000	3
23		Job 20	400	2
24		Job 21	400	2		Max reel lengths in column G may be edited if required.
25		Job 22	400	4		Populate / edit Job Ref and Wire Length data in columns B and C.
26		Job 23	18,750	1		For a totally fresh allocation, clear the reel numbers from D.
27		Job 24	400	4		Otherwise, you can manually enter / edit Reel Numbers in D either
28		Job 25	400	4		before, after, or instead of, doing an automatic allocation.
29		Job 26	11,170	2		Automatic allocation will only allocate rows not yet allocated.
30		Job 27	3,245	3		Click the green button to auto-allocate.
31		Job 28	5,500	3
32		Job 29	80	4		Best to have the sheet protected.
33		Job 30	290	4
34
35
36
37
38
Reel Calc

Cell Formulas
Range	Formula
H4:H13	H4	=SUMIF($D$4:$D$103,F4,$C$4:$C$103)
I4:I13	I4	=G4-H4
L4	L4	=COUNTIF(M6:M105,">0")
M4	M4	=SUM(M6:M102)
L6	L6	=IFERROR(SORT(FILTER(B4:C103,(C4:C103>0)*(D4:D103=""),),2,-1,),"")
N6:N105	N6	=IFERROR(MATCH(L6:L105,$B$4:$B$103,0)+3,"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:I13	Expression	=$H4 >$G4	text	NO
G4:I13	Expression	=$I4<=($G4*0.015)	text	NO
G4:I13	Expression	=AND($I4<=($G4*0.985),$I4>($G4*0.015))	text	NO

To do the Auto_Allocation, it will need the following code pasting to the code module of your sheet. Then the code will need to be assigned to the button shape.

Sub Reel_Calc()
Dim r As Integer, Reel As Integer


'If nothing to allocate
If Range("L4") = 0 Then Exit Sub
'If there is an over-allocation for any reel
If WorksheetFunction.Min(Range("I4:I13")) < 0 Then
    MsgBox ("Please manually adjust existing allocatons to remove any over-allocation.   Then try again.")
    Exit Sub
End If
'Otherwise starting with reel 1
Reel = 1 '

'If reel cannot accomodate the smallest unallocated job then try next reel
Reel_Check:
If Range("I" & Reel + 3) < WorksheetFunction.Min(Range("M6:M105")) Then
    Reel = Reel + 1
    If Reel = 11 Then Exit Sub
    GoTo Reel_Check
End If

'If current reel will accomodate all remaining un-allocated jobs then allocate them all to current reel
If Range("I" & Reel + 3) >= Range("M4") Then
    Application.Calculation = xlCalculationManual
        For r = 6 To Range("L4") + 5
            Range("D" & Range("N" & r)) = Reel
        Next r
    Application.Calculation = xlCalculationAutomatic
End If

'Otherwise allocate where possible, numbers top down, largest to smallest, from un-allocated list

        For r = 6 To Range("L4") + 5
        If Range("M" & r) <= Range("I" & Reel + 3) Then
            Range("D" & Range("N" & r)) = Reel
            GoTo Reel_Check
        End If
        Next r
       
    Application.Calculation = xlCalculationAutomatic
End Sub

Hope that helps.

EDIT: Of course, the button doesn't render here etc.
I will endeavour to post a link to a working sheet.

 

[Snakehips]

Here is link to file: Reel_Allocation

 

[rplohocky]

Snakehips,
I am blown away! I can't believe that you built something like this! I downloaded the file and found this to exactly what I was looking for. I can't thank you enough! I will incorporate this into my workbook and will always remember the work you did to make this happen!!!! Thanks again!

 

[Snakehips]

Snakehips,
I am blown away! I can't believe that you built something like this! I downloaded the file and found this to exactly what I was looking for. I can't thank you enough! I will incorporate this into my workbook and will always remember the work you did to make this happen!!!! Thanks again!

That's fantastic to hear ! And you are most welcome.
There was never going to be a formula solution as such. So, it was a little challenge that kept me off the streets for a few hours.
Ultimately, very satisfying to know that it will be used and appreciated.
Anyway, if I hadn't been cooped up indoors, hunched over a computer, doing that for you, I'd probably have only been out enjoying myself!