How to calculate a min price out of a long list

[Ahmed Fawad]

hi,

I got 4 rows
1. Product ID: 123456 (6 digit code)
2. Unite Pice
3. Order Ref. #
4. Order date

I need is to have if i enter Product ID in a cell it shows min price, order ref. and date in same row.

Please help

Ahmed

 

[BGY23]

Hi,
Try:<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2629427 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:str="'=MIN(IF($J$10:$J$12=J13,$K$10:$K$12))">'=MINIF($J$10:$J$12=J13,$K$10:$K$12))</TD></TR></TBODY></TABLE>

Where J10 to J12 have the item numbers and J3 is where you have the item number you want the minimum price for and k10 to k12 have the prices by item number.

You have to press Ctrl shift and enter at the end when entering the formula.

 

[Ahmed Fawad]

Hi,
Try:<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2629427 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:str="'=MIN(IF($J$10:$J$12=J13,$K$10:$K$12))">'=MINIF($J$10:$J$12=J13,$K$10:$K$12))</TD></TR></TBODY></TABLE>

Where J10 to J12 have the item numbers and J3 is where you have the item number you want the minimum price for and k10 to k12 have the prices by item number.

You have to press Ctrl shift and enter at the end when entering the formula.

let me more clear the picture....

 

[Trevor G]

Welcome to the Board.

What about looking at using SubTotaling from the Data Tab/Menu depending on which verison you are using.

See sample below

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 73px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>People</TD><TD>Amount</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">56</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">118.7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">180.3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">241.9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-WEIGHT: bold">Bill Min</TD><TD style="TEXT-ALIGN: right">56</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Jack</TD><TD style="TEXT-ALIGN: right">45</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Jack</TD><TD style="TEXT-ALIGN: right">103.3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Jack</TD><TD style="TEXT-ALIGN: right">164.9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Jack</TD><TD style="TEXT-ALIGN: right">226.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-WEIGHT: bold">Jack Min</TD><TD style="TEXT-ALIGN: right">45</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>John</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>John</TD><TD style="TEXT-ALIGN: right">72.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>John</TD><TD style="TEXT-ALIGN: right">134.1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>John</TD><TD style="TEXT-ALIGN: right">195.7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>John</TD><TD style="TEXT-ALIGN: right">257.3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="FONT-WEIGHT: bold">John Min</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">87.9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">149.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">211.1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>Sarah</TD><TD style="TEXT-ALIGN: right">272.7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="FONT-WEIGHT: bold">Sarah Min</TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="FONT-WEIGHT: bold">Grand Min</TD><TD style="TEXT-ALIGN: right">12</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F8</TD><TD>=SUBTOTAL(5,F4:F7)</TD></TR><TR><TD>F13</TD><TD>=SUBTOTAL(5,F9:F12)</TD></TR><TR><TD>F19</TD><TD>=SUBTOTAL(5,F14:F18)</TD></TR><TR><TD>F25</TD><TD>=SUBTOTAL(5,F20:F24)</TD></TR><TR><TD>F26</TD><TD>=SUBTOTAL(5,F4:F24)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Ahmed Fawad]

Hi Trevor,

Thanks for your kind reply but i got more than 400 products to sort out their minimum prices while supplier is same.. and there will be more added too... so I need to have a formula for that

Thanx

Ahmed

 

[Trevor G]

If you don't like the idea of Subtotal Ahmed (which does work), then why not place your table in a Pivot Table and then just change the formula feature from Sum to Min, that also works, and you can show the Pivot on the actual sheet or in another sheet.

 

[VoG]

With a product code in E6

=MIN(IF(B5:B400=E5,C5:C400))

confirmed with CTRL + Shift + Enter.

 

[Ahmed Fawad]

With a product code in E6

=MIN(IF(B5:B400=E5,C5:C400))

confirmed with CTRL + Shift + Enter.

ok let me more clear it ...

1st row: products is repeating in different orders i got 917 entries there
2nd row: unit price is there again 917 enteries
3rd row: different order ref. (coz its sorted by product ID)
4th row: dates are there order wise

now i need to have is ..... if i enter the product id: it'll search the product (which is repeating in 1st row) then give back the min price out of it....

Hope it clears better

Ahmed

 

[VoG]

OK, here is an example

Excel Workbook
	B	C	D	E	F
5	1	6		3	0.5
6	1	4
7	1	4
8	1	4
9	1	2
10	2	1
11	2	7
12	2	8
13	2	5
14	3	1
15	3	0.5
16	3	2
17	3	7
18	4	4
19	5	4
20	5	2
21	5	3
22	5	4
Sheet2

 

[Ahmed Fawad]

OK, here is an example

Excel Workbook
	B	C	D	E	F
5	1	6	*	3	0.5
6	1	4	*	*	*
7	1	4	*	*	*
8	1	4	*	*	*
9	1	2	*	*	*
10	2	1	*	*	*
11	2	7	*	*	*
12	2	8	*	*	*
13	2	5	*	*	*
14	3	1	*	*	*
15	3	0.5	*	*	*
16	3	2	*	*	*
17	3	7	*	*	*
18	4	4	*	*	*
19	5	4	*	*	*
20	5	2	*	*	*
21	5	3	*	*	*
22	5	4	*	*	*
Sheet2

cool mate thanx
it works but some places it doesnt wrok and in every cell it showin error that formula omits adjacent cell
Part 1 is near to done now