How can I create an excel formula/sheet to figure out how many of something I need based on length?

Question

I'm a project manager in a custom metal fabrication company. When I calculate how much structural tubing, channel, angle, etc. that we need, here's how I do it:

• Write out how many of each part and the length. For example:
  • 1x 110"
  • 4x 107"
  • 3x 50"
  • 18x 9"
• Start (usually) from 288" and subtract the lengths, starting with the longest. So I'd start at 288, then subtract 110 until I can't anymore, then subtract smaller values until I absolutely can't fit anymore onto the stick of metal. With the example above, it would go like 288 > 178 > 71 > 21 > 12 > 3. That would be the first stick. I keep doing that until I'm out of parts, and that tells me how many sticks I need to order.

I'd love to be able to do this in excel. I've tried to figure it out but to no avail.

So basically what kind of formula(s) do I need to be able to do this? Basically so that I can enter the lengths and quantities of the parts and the length of the stick and calculate the number of sticks needed.

Note: It absolutely will not work just by adding all the lengths together and dividing by stick length. It has to recursively (is that the right word?) subtract the part lengths from the stick length.

Answer 1

I had a think about your problem and I liked the task, so I decided to write a quick macro to help you with your issue. Your input for the macro below should look this:

The code for the macro looks like this:

Sub CalcPipe()

Dim StartPipeLng As Long, LastLng As Long, TotalLng As Long, PipeName As Long

StartPipeLng = Cells(2, 4).Value ' Picks start value from cell D2

LastLng = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row 'Find last row of loop

TotalLng = Application.WorksheetFunction.SumProduct(Range("A2:A" & LastLng), Range("B2:B" & LastLng))
TotalLng = Application.WorksheetFunction.RoundUp(TotalLng / StartPipeLng, 0)

Cells(5, 4).Value = TotalLng 'Outputs the number of pipes in cell D5

PipeName = 1

    For y = 6 To TotalLng + 5

        Cells(1, y).Value = "Pipe " & PipeName
        PipeName = PipeName + 1
        StartPipeLng = Cells(2, 4).Value

        For x = 2 To LastLng
            If StartPipeLng - Cells(x, 1).Value >= 0 And Cells(x, 2).Value <> Cells(x, 3).Value Then
                StartPipeLng = StartPipeLng - Cells(x, 1).Value
                Cells(x, 3).Value = Cells(x, 3).Value + 1
                Cells(x, y).Value = Cells(x, y).Value + 1
                x = x - 1
            End If
        Next x
    Next y

Columns(3).Clear

End Sub

The output after you ran the macro will look like this:

Let me know if the macro works for you. Note that Stack Overflow is not a coding service and I wrote this for you as a one-off.