I have an excel file, merged from two files...one with individua
receipt date and quantity and another with total physical quantity o
hand as follows:

sr.no Id Date Rect qty Phy qty
107140 TS68A 08/16/2003 8 32
107140 TS68A 08/16/2003 8 32
129964 TS68A 02/23/2004 12 32
129964 TS68A 02/23/2004 12 32
142133 TS68A 05/26/2004 12 32
142133 TS68A 05/26/2004 12 32
145389 TS68A 06/20/2004 14 32

Phy. qty is the quantity on hand as of TODAY hence it is not changin
with each receipt. There are around 5000 Ids.

I need to age the 32 quantity that is lying in stock as follows
<2 month <4 months 4 months+
TS68A 14 18 0

regards,
edward

--
Message posted from http://www.ExcelForum.com

here is a do loop i use to drop future POs into 8 weekly
buckets from today. we use this file daily and it works
guite well. this sounds like what your are after. you will
have to do some heavy rewrite to make it fit your data.

Range("A3").value = Date
Set Date1 = Range("A3")
Set Item1 = Range("A12")
Set Item2 = Sheets("POMSQ").Range("A2")

Do While Not IsEmpty(Item1)
Set Item1d = Item1.Offset(1, 0)
Set Item2d = Item2.Offset(1, 0)
Set ReqD = Item2.Offset(0, 4)
Set ReqQ = Item2.Offset(0, 6)
Set ODPO = Item1.Offset(0, 18)
Set Wk1 = Item1.Offset(0, 19)
Set Wk2 = Item1.Offset(0, 21)
Set Wk3 = Item1.Offset(0, 23)
Set Wk4 = Item1.Offset(0, 24)
Set Wk5 = Item1.Offset(0, 27)
Set Wk6 = Item1.Offset(0, 29)
Set Wk7 = Item1.Offset(0, 31)
Set Byon = Item1.Offset(0, 33)
Wk1.Select
If Item1.Value = Item2.Value And ReqD < Date1
Then
ODPO.Value = ODPO.Value + ReqQ.Value
Set Item2 = Item2d
Else
If Item1.Value = Item2.Value And ReqD =
Date1 And ReqD < Date1 + 7 Then
Wk1.Value = Wk1.Value + ReqQ.Value
Set Item2 = Item2d
Else
If Item1.Value = Item2.Value And ReqD
= Date1 + 7 And ReqD < Date1 + 14 Then
Wk2.Value = Wk2.Value + ReqQ.Value
Set Item2 = Item2d
Else
If Item1.Value = Item2.Value And _
ReqD = Date1 + 14 And ReqD <
Date1 + 21 Then
Wk3.Value = Wk3.Value +
ReqQ.Value
Set Item2 = Item2d
Else
If Item1.Value = Item2.Value
And _
ReqD = Date1 + 21 And
ReqD < Date1 + 28 Then
Wk4.Value = Wk4.Value +
ReqQ.Value
Set Item2 = Item2d
Else
If Item1.Value =
Item2.Value And _
ReqD = Date1 + 28 And
ReqD < Date1 + 35 Then
Wk5.Value = Wk5.Value
+ ReqQ.Value
Set Item2 = Item2d
Else
If Item1.Value =
Item2.Value And _
ReqD = Date1 + 35
And ReqD < Date1 + 42 Then
Wk6.Value =
Wk6.Value + ReqQ.Value
Set Item2 = Item2d
Else
If Item1.Value =
Item2.Value And _
ReqD = Date1
+ 42 And ReqD < Date1 + 49 Then
Wk7.Value =
Wk7.Value + ReqQ.Value
Set Item2 =
Item2d
Else
If Item1.Value
= Item2.Value And _
ReqD =
Date1 + 49 Then
Byon.Value
= Byon.Value + ReqQ.Value
Set Item2
= Item2d
Else
Set
Item1 = Item1d
End If
End If
End If
End If
End If
End If
End If
End If
End If
If IsEmpty(Item1) And Not IsEmpty(Item2) Then
Sheets("POMSQ").Select
Item2.Select
Range(Item2, Item2.End(xlToRight)).Select
seletion.Interior.ColorIndex = 6
MsgBox ("Item " & Item2 & " is not found
on the master table! Add the item to the master table and
re-run the macro.")
Exit Sub
End If
Loop
-----Original Message-----
I have an excel file, merged from two files...one with
individual
receipt date and quantity and another with total physical
quantity on
hand as follows:

sr.no Id Date Rect qty Phy qty
107140 TS68A 08/16/2003 8 32
107140 TS68A 08/16/2003 8 32
129964 TS68A 02/23/2004 12 32
129964 TS68A 02/23/2004 12 32
142133 TS68A 05/26/2004 12 32
142133 TS68A 05/26/2004 12 32
145389 TS68A 06/20/2004 14 32

Phy. qty is the quantity on hand as of TODAY hence it is
not changing
with each receipt. There are around 5000 Ids.

I need to age the 32 quantity that is lying in stock as
follows
<2 month <4 months 4 months+
TS68A 14 18 0

regards,
edward#


---
Message posted from http://www.ExcelForum.com/

.