Counting Numbers Summary

[maeyks]

Dear All, I have a question. What if I have a particular item. On the left is its history of PRODUCTION DATE. While on the right is the actual ISSUANCE. What i would like to do is, it will follow, in sequence of its production date, the number of item being issued. Based on the image attached, I have 12 bottles produced each production date. If I will issue it to stores 1 to 4, it will return the values of May 1 until store number 3 and at store 4 it will be a combination of may 1 and may 5 following the date of their production. In manufacturing language, it simply means the first in first out only translated into excel language. Thanks.

ITEM:	Caesar Dressing
Production Date:	Quantity	Unit of Measure		Store	Order Quantity	Unit of Measure	Production Date
5/1/2014	12	Bottle		Store 1	3	Bottle
5/5/2014	12	Bottle		Store 2	4	Bottle
5/10/2014	12	Bottle		Store 3	3	Bottle
				Store 4	5	Bottle

<tbody>
</tbody><colgroup><col><col><col><col span="2"><col><col><col></colgroup>

 

[excelliot]

check this attachment, i cant upload attachment hence have uploaded external link..

https://drive.google.com/file/d/0B2TLX4xC8XcFX0NDbkU5OS1BY28/edit?usp=sharing

Cheers!!

 

[maeyks]

hello,i still can't view. and i can't find anythong on the web with similar situation. anybody knows something?

 

[excelliot]

hi

can you PM me your email id so that i can email you file if you are not able to download.

Cheers!!

 

[Aladin Akyurek]

check this attachment, i cant upload attachment hence have uploaded external link..

https://drive.google.com/file/d/0B2TLX4xC8XcFX0NDbkU5OS1BY28/edit?usp=sharing

Cheers!!

hi

can you PM me your email id so that i can email you file if you are not able to download.

Cheers!!

Try to post the solution you propose here (for any interested party to see), not just in the form of an upload or via PM --> email route.

 

[excelliot]

Paste your data begining cell A1 as it is & create macro using below code & run to get desired result.

You can use this code:

Sub FIFO()
    Dim key As Variant
    Dim cell As Range, cell2 As Range
    Dim s As Variant, b As Variant
    'Early binding: add reference to MS Scripting Runtime
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")


    'Use this for late binding instead:
    'Dim dic As Object
    'Set dic = CreateObject("Scripting.Dictionary")
    
    For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    dict.Add cell, cell.Offset(0, 1)
    Next
    
    For Each key In dict.keys
        Debug.Print "Key: " & key & " Value: " & dict(key)
    Next key
    s = 0
    i = 1
    b = 0
    
    For Each key In dict.keys
    b = dict(key)(i)
    
    For Each cell2 In Range("E2:E" & Range("e" & Rows.Count).End(xlUp).Row)
    s = s + cell2.Value
    
    'Debug.Print dict.keys()(i)
    'Debug.Print dict(key)(i)
    
    If s < b Then
        Range("G" & cell2.Row).Value = key(i)
        s = s - WorksheetFunction.Min(s, b)
        b = b - cell2.Value
        Else
        
        Range("G" & cell2.Row).Value = key(i)
        i = i + 1
        Range("G" & cell2.Row).Value = Range("G" & cell2.Row).Value & "-" & key(i)
        b = b + dict(key)(i)
        s = s - WorksheetFunction.Min(s, b)
        b = b - cell2.Value
    End If
    'i = i + 1
    Next
    Exit For
    Next
End Sub

Cheers!!

 

[maeyks]

Paste your data begining cell A1 as it is & create macro using below code & run to get desired result.

You can use this code:

Sub FIFO()
    Dim key As Variant
    Dim cell As Range, cell2 As Range
    Dim s As Variant, b As Variant
    'Early binding: add reference to MS Scripting Runtime
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")


    'Use this for late binding instead:
    'Dim dic As Object
    'Set dic = CreateObject("Scripting.Dictionary")
    
    For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    dict.Add cell, cell.Offset(0, 1)
    Next
    
    For Each key In dict.keys
        Debug.Print "Key: " & key & " Value: " & dict(key)
    Next key
    s = 0
    i = 1
    b = 0
    
    For Each key In dict.keys
    b = dict(key)(i)
    
    For Each cell2 In Range("E2:E" & Range("e" & Rows.Count).End(xlUp).Row)
    s = s + cell2.Value
    
    'Debug.Print dict.keys()(i)
    'Debug.Print dict(key)(i)
    
    If s < b Then
        Range("G" & cell2.Row).Value = key(i)
        s = s - WorksheetFunction.Min(s, b)
        b = b - cell2.Value
        Else
        
        Range("G" & cell2.Row).Value = key(i)
        i = i + 1
        Range("G" & cell2.Row).Value = Range("G" & cell2.Row).Value & "-" & key(i)
        b = b + dict(key)(i)
        s = s - WorksheetFunction.Min(s, b)
        b = b - cell2.Value
    End If
    'i = i + 1
    Next
    Exit For
    Next
End Sub

Cheers!!

Email address is maeyks at yahoo dot com. Here is also the rest of the things i wanna do: Is there a way that I would be able to follow the FIRST IN FIRST OUT stocks rotation in Excel? In a nutshell, I would like to follow an issuance patter wherein the first item produced will be the first item to be issued.

For example, if I have a pizza sauce, produced last May 1, 3, 5, 7 with 20 packs each. The value to be returned is its production date until such time that it will realize that it already served all packs on a given date. on the 21st pack, it will return the value May 3 since May 1 only had 20 packs, and so forth.

Is there a way that it can be done on Excel without using VBA program?

 

[excelliot]

Hi

Do as mentioned in mine previous thread..

Cheers!!