Minimum function in a list

  • Thread starter Thread starter Albert
  • Start date Start date
A

Albert

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
 
This is only the first part of the first question. Say we have in A1 thru D9:

cat 36 $50.00 vendor 5
dog 15 $10.00 vendor 1
cat 42 $40.00 vendor 4
dog 23 $30.00 vendor 3
cat 12 $60.00 vendor 6
dog 47 $20.00 vendor 2
turtle 12 $70.00 vendor 7
turtle 29 $90.00 vendor 9
turtle 41 $80.00 vendor 8

Item, Quantity, Price, and Vendor

the array formula:

=MIN(IF(A1:A9="dog",C1:C9,"")) will display 10

This formula must be entered with CNTRL-SHFT-ENTER rather than just the
ENTER key.

Good Luck with the next parts.
 
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+shitf+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
 
Thanks. Will try on the weekend

Bernard Liengme said:
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+shitf+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



.
Click to expand...
 
This works great. Thanks

But how do I adapt the formula to ignore zero/blank cells in the 3rd/price
column?
 
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
 
Back
Top