Minimum function in a list

[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

 

[Gary''s Student]

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.

 

[Bernard Liengme]

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 B22 has the array formula =Payless(A2) ( select B22, 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

 

[Albert]

Thanks. Will try on the weekend

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 B22 has the array formula =Payless(A2) ( select B22, 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



.

 

[Albert]

This works great. Thanks

But how do I adapt the formula to ignore zero/blank cells in the 3rd/price
column?

 

[Albert]

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