minimum of an array

[charijit]

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.

 

[charijit]

Ken,
Now it is working. I have so much to learn.

Dear Dear Sixth Sense,
Can you tell me what is "array entered" because I am not clear about it after reading the Excel help file on this. Also kindly help me know if the above excersize can be done with DMIN and DGET function.
Lastly, I am not able to understand the logic for vendor name. Can you please explain the logic for the INDEX argument used therein.

 

[ken2step]

http://www.cpearson.com/excel/array.htm

Check this link for info on Array Enter CSE

Hope this helps charijit!

 

[SIXTH SENSE]

I think the sumproduct of this formula is a little bit tricky.

=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)))

This argument refers to the range where you want to select from(the vendors name)

Sheet1!$C$1:$C$15

This part is tricky!

SUMPRODUCT(--(Sheet1!$A$1:$A$15=Sheet2!A1),--(Sheet1!$B$1:$B$15=Sheet2!B1),ROW(Sheet1!$A$1:$A$15))

The first argument create an array of size 1X15 with
values of 1 for those equal the criteria Sheet2!A1 and 0 for those that are not
This reutrns something like
{1;0;0;1;0..}

--(Sheet1!$A$1:$A$15=Sheet2!A1)

The second argument is simmilar to the first. But this time
it is looking at column B.

--(Sheet1!$B$1:$B$15=Sheet2!A1)

The 3rd argument returns an array containing the row numbers of the argument. literally, this returns {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}

ROW(Sheet1!$A$1:$A$15)

So the sumproduct will return the row number that complies
the first two arguments.

but one problem with this is when there are more than one matches, ie two vendors selling the same item and both the cheapest price in the array. this will return 0.
maybe aladin can shed ligh on this.

 

[Aladin Akyurek]

I think the sumproduct of this formula is a little bit tricky...

but one problem with this is when there are more than one matches, ie two vendors selling the same item and both the cheapest price in the array. this will return 0.
maybe aladin can shed ligh on this.

That's right. Just change the triple

< a, 4, ww >

to

< a, 1, xx >

which shows that the system of formulas acw posted cannot reflect the reality. However, I'll post one that will. That's a system which I have been proposing last few months here and elsewhere.

 

[acw]

Hi

Aladin you are right. Change 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)))

with array entered
=INDEX(Sheet1!$C$1:$C$15,MIN(VALUE(SUBSTITUTE((Sheet1!$A$1:$A$15=Sheet2!A1)*(Sheet1!$B$1:$B$15=Sheet2!B1)*ROW(Sheet1!$A$1:$A$15),0,999))))

Again the 999 will have to be changed to something suitable.

Tony

 

[Yogi Anand]

Hi Charijit:

How about ...

Book3
	A	B	C	D	E	F	G
1	ITEM	PRICE	VENDOR
2	a	1	ww		MinimumPriceTable
3	b	3	ee		ITEM	PRICE	VENDOR
4	c	4	dd		a	1	ww
5	d	5	ff		b	3	ee
6	e	5	ee		c	4	dd
7	r	6	ee		d	5	ff
8	v	3	dd		e	4	ee
9	a	4	ww		r	5	rr
10	b	6	rr		v	3	dd
11	d	7	zz		t	8	ss
12	t	8	ss		h	3	ee
13	h	3	ee
14	e	4	ee
15	r	5	rr
16	v	6	dd
Sheet1

The items in cells E3:E12 are Unique Values that can be obtained by using AdvancedFilter on A1:A16

The formula in cell F4 is ... =MIN(IF(($A$2:$A$16=$E4)*($B$2:$B$16)>0,($A$2:$A$16=$E4)*($B$2:$B$16)))

It is an array formula. It is then copied to cells F5:F12

The formula in cell G4 is ... =INDEX($C$2:$C$16,MATCH(E4&CHAR(1)&F4,$A$2:$A$16&$B$2:$B$16,0))

this is also an array formula. It is then copied to cells G5:G12.

 

[Yogi Anand]

[/i]
....Also kindly help me know if the above excersize can be done with DMIN and DGET function.
....
[/i]

Hi Charijit:

Let us have a look at the following ...

y040226h1a.xls
	A	B	C	D	E	F	G	H	I	J
1	ITEM	PRICE	VENDOR		ITEM					source
2	a	1	ww		a					criteria
3	b	3	ee		DATATABLESolution					DataTable
4	c	4	dd		ITEM	PRICE	VENDOR
5	d	5	ff		a	1	ww
6	e	5	ee		b	3	ee
7	r	6	ee		c	4	dd
8	v	3	dd		d	5	ff
9	a	4	ww		e	4	dd
10	b	6	rr		r	5	ff
11	d	7	zz		v	3	ee
12	t	8	ss		t	8	ss
13	h	3	ee		h	3	ee
14	e	4	ee
15	r	5	rr
16	v	6	dd
Sheet1 (2)

In this one variable DATA TABLE solution, I have used the DMIN function for MINIMUM Price, and the INDEX function for locating the VENDOR.

 

[Aladin Akyurek]

...
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...

Your original data looks like this with changes regarding item "a", made on purpose...

Exhibit 1

aaVendorsLowestPrice charijit v1.xls
	A	B	C	D
1	Item	Price	Vendor
2	a	1	ww
3	b	3	ee
4	c	4	dd
5	d	5	ff
6	e	5	ee
7	r	6	ee
8	v	3	dd
9	a	1	xx
10	b	6	rr
11	d	7	zz
12	t	8	ss
13	h	4	ee
14	e	4	ee
15	r	5	rr
16	a	1	dd
17
VendorsOriginal

Note that you have for item "e" 2 different prices from the same vendor "ee". While this is close to the reality, the system of formulas in what follows requires that this type of data (item and vendor duplicates) is eliminated. The system also needs a different data representation like in:

http://www.mrexcel.com/board2/viewtopic.php?t=72823

See Exhibit 2 in the next post.

 

[Aladin Akyurek]

...

See Exhibit 2...

Exhibit 2 (Partly shown)

aaVendorsLowestPrice charijit v1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		VENDORS
2	TITLES	dd	ee	ff	rr	ss	ww	xx	zz	RANK										CHEAPEST
3	a	1					1	1		1					2	3		2	1	dd	ww	xx
4	b		3		6						1		2					0	1	ee
5	c	4								1								0	1	dd
6	d			5					7			1					2	0	1	ff
7	e		4								1							0	1	ee
Vendors

The above representation can be easily constructed from the first exhibit (Exhibit 1) by means of a formula (but I won't discuss that now).

Formulas...

J3, copied to Q3:

=IF(N(B3),RANK(B3,$B3:$I3,1)+COUNTIF($B3:B3,B3)-1,"")

R3: (determines the number of ties of the cheapest price)

=MAX(IF(INDEX(B3:I3,MATCH($S3,J3:Q3,0))=B3:I3,J3:Q3))-$S3

which must be confirmed with control+shift+enter instead of just enter.

S3 houses 1, meaning Top 1 cheapest.

T3, copied across to AA3:

=IF(COLUMN()-COLUMN($T3)+1<=$S3+$R3,INDEX($B$2:$I$2,MATCH(COLUMN()-COLUMN($T3)+1,$J3:$Q3,0)),"")

Each formula must also be copied down.

As can be seen from above, the cheapest price for an item can be associated with one or more vendors. When more, just giving one is patently wrong.