Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimum function in a list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimum function in a list
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. -- Gary''s Student - gsnu201001 "Albert" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimum function in a list
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 |
#4
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimum function in a list
This works great. Thanks
But how do I adapt the formula to ignore zero/blank cells in the 3rd/price column? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu201001 "Albert" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimum function in a list
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the minimum and maximum date in a list | Excel Programming | |||
Minimum Ifs Function | Excel Worksheet Functions | |||
Minimum function | Excel Discussion (Misc queries) | |||
minimum function | Excel Worksheet Functions | |||
How to subtract two lowest minimum scores from list | Excel Programming |