Counting Numbers Summary

M

maeyks

Board Regular
Joined
Aug 20, 2010
Messages
127
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.
[TABLE="width: 768"]
<tbody>[TR]
[TD]ITEM:[/TD]
[TD]Caesar Dressing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Production Date:[/TD]
[TD]Quantity[/TD]
[TD]Unit of Measure[/TD]
[TD][/TD]
[TD]Store[/TD]
[TD]Order Quantity[/TD]
[TD]Unit of Measure[/TD]
[TD]Production Date[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2014[/TD]
[TD="align: right"]12[/TD]
[TD]Bottle[/TD]
[TD][/TD]
[TD]Store 1[/TD]
[TD="align: right"]3[/TD]
[TD]Bottle[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5/5/2014[/TD]
[TD="align: right"]12[/TD]
[TD]Bottle[/TD]
[TD][/TD]
[TD]Store 2[/TD]
[TD="align: right"]4[/TD]
[TD]Bottle[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5/10/2014[/TD]
[TD="align: right"]12[/TD]
[TD]Bottle[/TD]
[TD][/TD]
[TD]Store 3[/TD]
[TD="align: right"]3[/TD]
[TD]Bottle[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Store 4[/TD]
[TD="align: right"]5[/TD]
[TD]Bottle[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
hello,i still can't view. and i can't find anythong on the web with similar situation. anybody knows something?
 
Upvote 0
Upvote 0
Paste your data begining cell A1 as it is & create macro using below code & run to get desired result.

You can use this code:

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!!
 
Upvote 0
excelliot said:
Paste your data begining cell A1 as it is & create macro using below code & run to get desired result.

You can use this code:

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!!
Click to expand...
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Cubist
  • Solved
Expand each row n number of times.
2
Replies
16
Views
644
Cubist
Cubist
B
  • Solved
Average of last X transactions by customer
Replies
5
Views
312
Dave Patton
D
T
  • Question Question
Returning all data despite duplicate values
Replies
0
Views
479
Tommy1115
T
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
900
Alex Blakenburg
A
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
544
Alex Blakenburg
A

Forum statistics

Threads
1,223,264
Messages
6,171,085
Members
452,378
Latest member
Hoodzy01

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