stumped on dividing up qty in numeric bin to Alpha bins

[Harley78]

I am trying to figure out best way to divide up a quantity that are in numerical bins to the bins that are letter and need to be refilled.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Material	Material Description	Plnt	Bin	SL	BUn	Unrestr.-use stock	Min	Max	Pull	From Loc	Pull Amount
2	1001	Flex Element		321		EA	5	0	0
3	1001	Flex Element		321		EA	0	0	0
4	1001	Flex Element		1456		EA	0	0	0
5	1001	Flex Element		SM		EA	0	1	2	Refill		2
6	1001	Flex Element		M		EA	0	2	4	Refill		4
7	1001	Flex Element		1320		EA	0	0	0
8	1001	Flex Element		1321		EA	4	0	0
9	1001	Flex Element		1457		EA	0	0	0
10	1001	Flex Element		C		EA	7	3	6
Sheet2

Column K is were I want to place the number that is calculated from a numeric bin location and place in the row with a letter bin location

ie. rows 5 & 6 show refill is required and if you look at row 2 bin 321 shows 5 so i need to divide by the number of refills and place then next to it. in this case 2 and 3 in SM & M.

Is this at all possible?

 

[GlennUK]

Shouldn't you also show where the refills came from? What if refills come from more than one location?

 

[Harley78]

Yes sir, your correct! thanks. Now onto figuring out how..

 

[GlennUK]

Well, a few minutes of my lunchtime produced this VBA macro to do it:

Sub AllocatePulls()

    'initialize pointers
    pullposition = 2
    pullsofar = 0
    Range("K2:K10").ClearContents
    For Each c In Range("L2:L10")
        If c.Value > 0 Then
            ' we have found a pull amount
            'get amount from the current pull position, if possible
            pulledforcurrent = 0
            partpull = 0
            Do Until pulledforcurrent = c.Value
                    partpull = Application.Min((c.Value - pulledforcurrent), (Range("G" & pullposition).Value - pullsofar))
                    c.Offset(0, -1).Value = c.Offset(0, -1).Value & Range("D" & pullposition).Value & "(" & partpull & ")/"
                    pullsofar = pullsofar + partpull
                    If pullsofar = Range("G" & pullposition) Then
                        ' find next pull position and reset pullsofar
                        testamt = 0
                        Do Until testamt > 0
                            pullposition = pullposition + 1
                            If pullposition > 10 Then ' we are past end of data, so end
                                Exit Sub
                            End If
                            testamt = Range("G" & pullposition).Value
                        Loop
                        pullsofar = 0
                    End If
                    pulledforcurrent = pulledforcurrent + partpull
                    partpull = 0
            Loop
            If Len(c.Offset(0, -1).Value) > 0 Then
                If Right(c.Offset(0, -1).Value, 1) = "/" Then
                    c.Offset(0, -1).Value = Mid(c.Offset(0, -1).Value, 1, Len(c.Offset(0, -1).Value) - 1)
                End If
            End If
        End If
    Next
End Sub

... if you are interested. I've only done a little testing, but it's something you could use as a starting point.