Posted to microsoft.public.excel.programming
|
|
Minimum function in a list
Thanks. Will try on the weekend
"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
.
|