Filtering Records

[ms10]

i get quotes for many items (which are listed in rows) and many
suppliers (which are listed in columns).. what is the easiest way to
automate which vendors have the best price

Thanks in advance
Mike

 

[Arvi Laanemets]

Hi

With the matrix in range A1:E6, and searched item in cell G1
=INDEX($B$1:$E$1,1,MATCH(LARGE(INDEX($B$2:$E$6,MATCH(G1,$A$2:$A$6,0),0),1),I
NDEX($B$2:$E$6,MATCH(G1,$A$2:$A$6,0),0),0))

(When there are several vendors whose prices do qualify, the leftmost is
selected)

 

[Ale]

Suposing you have 3 suppliers... in colums B, C and D... and 3 items, in lines 2, 3 and 4.

you could use a conditional formatting to format each cell with best price in some color for example.
then you would have to go to cell B2, enter conditional formatting and use condition :

Cell value is ; equal to ; =MIN($B2:$D2) (format the cell as you want for the best price)

now you just copy this cell's format and paste to all the other cells with prices in it.

 

[Jack Sons]

Arvi,

When I try your solution it appears that the result is the name of the
person corresponding with the highest price. Mike spoke of "suppliers" in
which case I think the best price is the lowest (not the highest) price. Or
did I understand it all wrong?

Jack Sons
The Netherlands

Hi

With the matrix in range A1:E6, and searched item in cell G1
=INDEX($B$1:$E$1,1,MATCH(LARGE(INDEX($B$2:$E$6,MATCH(G1,$A$2:$A$6,0),0),1),I
NDEX($B$2:$E$6,MATCH(G1,$A$2:$A$6,0),0),0))

(When there are several vendors whose prices do qualify, the leftmost is
selected)

 

[Arvi Laanemets]

Hi

It seems you are right! But it's easy to correct - replace LARGE with SMALL


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets

Arvi,

When I try your solution it appears that the result is the name of the
person corresponding with the highest price. Mike spoke of "suppliers" in
which case I think the best price is the lowest (not the highest) price. Or
did I understand it all wrong?

Jack Sons
The Netherlands

Hi

With the matrix in range A1:E6, and searched item in cell G1

=INDEX($B$1:$E$1,1,MATCH(LARGE(INDEX($B$2:$E$6,MATCH(G1,$A$2:$A$6,0),0),1),I

 

[Jack Sons]

Arvi,

Thanks.

Jack.

Hi

It seems you are right! But it's easy to correct - replace LARGE with SMALL


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets



=INDEX($B$1:$E$1,1,MATCH(LARGE(INDEX($B$2:$E$6,MATCH(G1,$A$2:$A$6,0),0),1),I