minimum of an array

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89
Dear All,
In column-A I have various items : a,b,c,d,e,r,v,a,b,d,t,h,e,r,v.
In column-B I have prices of items in Column-A : 1,3,4,5,5,6,3,4,6,7,8,3,4,5,6.
In column-C I have vendor names, who have supplied the items in Column-A : ww,ee,dd,ff,ee,ee,dd,ww,rr,zz,ss,ee,ee,rr,dd.

In a separate sheet I have listed the items nos. appearing in Column-A mentioned above. In the second column I wish the minimun price of the item to appear (since item is repeating and vendors may vary and thus their rates). In the third column I wish the vendor name corresponding to the minimun price to appear.
I seek all of your help.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Assuming that your data is in sheet 1 in the range A1:C15 and the output data is in sheet 2, with the unique list of items starting in A1.

In B1 array enter the formula
=MIN(IF(Sheet1!$A$1:$A$15=Sheet2!A1,Sheet1!$B$1:$B$15,999))

In C1 enter the formula
=INDEX(Sheet1!$C$1:$C$15,SUMPRODUCT(--(Sheet1!$A$1:$A$15=Sheet2!A1),--(Sheet1!$B$1:$B$15=Sheet2!B1),ROW(Sheet1!$A$1:$A$15)))

Copy down as required.

The formula in B1 has as a default maximum 999. You will have to change this to reflect the range of your data.


Tony
 

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89
Tony,
Kindly check your logic for sorting. Against b the lowest value is 3 whereas your logic for Column-B will invariably show value as 1. Thank you.
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
638
I ran his formulas in simulation and they worked perfectly returning the b value as 3 and vendor ee. You must of entered something wrong.
Ken

btw Tony...nice formula. I needed to ad the 3 for the column argument for Indexing though.
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

charijit said:
Tony,
Kindly check your logic for sorting. Against b the lowest value is 3 whereas your logic for Column-B will invariably show value as 1. Thank you.
Note:

The formula is array entered
Enter them by pressing Cltrl+****+Enter
They should work
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
638
Hi Sixth Sense...doesn't this need something...

In C1 enter the formula
=INDEX(Sheet1!$C$1:$C$15,SUMPRODUCT(--(Sheet1!$A$1:$A$15=Sheet2!A1),--(Sheet1!$B$1:$B$15=Sheet2!B1),ROW(Sheet1!$A$1:$A$15)),3)

Ken
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

Hi ken!
The 3 is not needed because
your array is just single column
Code:
Sheet1!$C$1:$C$15
You only refer to column C.
If suppose you have this one
Code:
Sheet1!$A$1:$C$15
then you need the 3 to reffer to the third column
in the range you specified.
 

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89
Ken,
I may have done mistakes, but its not working after several attempts.
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
638
Thanks Sixth Sense....I see now....when I was appling it my array used was A:C....I did not read his formula correctly....as usual! Those little details do make a difference huh! o_O
Thanks for correcting me.... I should just read!
Ken
 

ken2step

Well-known Member
Joined
Jan 9, 2003
Messages
638
Hi charijit....try what Sixth Sense was eluding to.....make sure you CSE the formula in B1...works for me perfectly!
ken
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,871
Messages
5,766,855
Members
425,380
Latest member
CubeGirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top