Hi Bernard,

Thanks for your input but I was not able to get my spreadsheet to work.

Sheet1 name = Transaction
Date Category Item Trans_type Quantity Price Supplier
21-Jan-10 A Bread Openning stock 3
21-Jan-10 B Cake Openning stock 4
22-Jan-10 A Bread Purchases 1 $40.00 Store 1
23-Jan-10 B Cake Purchases 1 $56.00 Store 2
24-Jan-10 A Bread Closing stock 2
25-Jan-10 B Cake Closing stock 2

Sheet name = inventory list
Category Item Openning stock Purchases Closing stock Useage Reorder
level Order amount Average price Min Price Supplier Date Max Price
Supplier Date
A Bread 3 1 2 2 3 1 #NAME?
B Cake 4 1 2 3 3 1 #VALUE!
Please could you help me with the correct macro? I have inserted my
spreadsheets.

Also how would I change the closing stock to openning stock?

Thanks
Albert

"Bernard Liengme" wrote:

A UDF for part 1.
On Sheet2 I have (beginning in A1)
date item supplier price
01-Jan bread fred 140.30
02-Jan cake mary 146.37
05-Jan cheese jack 118.21
etc
On another sheet I have *beginning in A1)
item Date Supplier Price
bread 14/01/2010 mary 131.44
where B2:D2 has the array formula =Payless(A2) ( select B2:D2, enter
formula, comiit tiwh ctrl+****f+enter)
Theis formula can be copied down the rows by dragging; you will need to
format column B as date otherwise the serail number is displayed.
Here is the VBA

Function payless(myItem)
Dim temp(3)
temp(0) = "no match": temp(1) = "": temp(2) = ""
lowprice = 1000000#
With Worksheets("Sheet2")
mylast = .Cells(Cells.Rows.Count, "A").End(xlUp).Row
For j = 2 To mylast
If .Cells(j, "B") = myItem Then
If .Cells(j, "D") < lowprice Then
lowprice = .Cells(j, "D")
temp(0) = .Cells(j, "A")
temp(1) = .Cells(j, "C")
temp(2) = .Cells(j, "D")
End If
End If
Next
End With
payless = temp
End Function


best wishes

--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Albert" wrote in message
...
Hi Guys,

Please can you help?

I have a transaction sheet where I capture all purchases of different
items
with the quantity, price and supplier of the goods.

I also have created a summary sheet which then uniquely lists the items
purchased and it attempts to summarize these purchases from the
transaction
sheet.

In this summary table I would like to get the min price paid for the
particular list item, the supplier and date of this minimum price
purchase.
Please can you supply me with a formula or the VB code?

While I am asking...

Also in the transaction sheet I have a column for "transaction type"
(Opening stock, closing stock and purchases). My idea is to keep a trck of
stock on hand and useage in the summary table. Currently I have a column
for
each (openning stock + Purchases - Closing stock). Obviously there is a
problem as the closing stock for the one month needs to become the
openning
stock of the next. Can someone point me in the right direction?

Thanks
Albert


.