Filtering Records

  • Thread starter Thread starter ms10
  • Start date Start date
M

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


Arvi Laanemets said:
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)
Click to expand...
 
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


Jack Sons said:
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


Arvi Laanemets said:
Hi

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

Thanks.

Jack.

Arvi Laanemets said:
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
Click to expand...
 
Back
Top