Excel HELP with finding data in form and showing location

[robertvito]

Ok here goes I have an excel project that I am working on and what
need to do is to have 3 columns with a one for vendors, one for par
number and one for price. Ideally what I would like to do is to enter
part number and it lists the vendor and price that is the cheapest,
Also if there are more than one as well) I have been able to do a =mi
command and just highlight the price cells and it puts the cheapes
price in the window. THis is not going to work when I have 1000 or mor
listed since I then have to still search for the vendor and the par
associated with that price. Thanks for any insight or input onthi
matter.

Robert

Please attach a file if possible or explain in laymans terms as i a
still a neophyte when it comes to this kind of work.
Thanks

 

[Max]

Try this ..

Assume the sample set below is
In Sheet1, in A1:C7
-----------------------
Vendor Part# Price
ABC 1234 $11.00
DEF 1235 $28.00
XYZ 1234 $10.00
DEF 1234 $10.00
XYZ 1235 $22.00
ABC 1235 $22.00

Put in D2: =IF(C2<>"",C2+ROW()/10^10,"")
Copy down to D7

(Col D will serve as an arbitrary "tie-breaker"
for the prices in col C)

In Sheet2
-------------
Put the labels Part#, Vendor, Price in A1:C1

The Part# will be input in A2

Put in B2 (paste into the formula bar):

=IF(A$2="","",IF(ISERROR(SMALL(IF(Sheet1!$B$1:$B$2000=A$2,Sheet1!$D$1:$D$200
0),ROW(A1))),"",INDEX(Sheet1!$A$1:$A$2000,MATCH(SMALL(IF(Sheet1!$B$1:$B$2000
=A$2,Sheet1!$D$1:$D$2000),ROW(A1)),Sheet1!$D$1:$D$2000,0))))

Array-enter the formula, i.e. Press CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Done correctly, Excel will wrap curly braces { } around the formula
(don't type-in the braces yourself !)

Put in C2 (paste into the formula bar):

=IF(A$2="","",IF(ISERROR(SMALL(IF(Sheet1!$B$1:$B$2000=A$2,Sheet1!$D$1:$D$200
0),ROW(A1))),"",INDEX(Sheet1!$C$1:$C$2000,MATCH(SMALL(IF(Sheet1!$B$1:$B$2000
=A$2,Sheet1!$D$1:$D$2000),ROW(A1)),Sheet1!$D$1:$D$2000,0))))

Array-enter the formula (as before)

Format C2 as currency

Now select B2:C2 and copy down a "safe" number of rows to cater
for the max likely number of multiple ties for the lowest price
for any particular part# (copy down say to C10)

The vendors with the lowest prices for the part# input in A2
will be listed in B2:C10 in ascending order
(lowest prices in row2 up)

For example, for the sample data in Sheet1,
if A2 contains: 1234, the list in cols B and C will be:

Vendor Price
XYZ $10.00
DEF $10.00
ABC $11.00

Adjust the ranges to suit the extent of your data

 

[Max]

Copy down to D7

Typo .. the 2nd line above should read:

Copy down to D2000

(instead of D7)

 

[Max]

Please attach a file if possible ..

If you'd like, post a "readable" version of your email here
and I'll send a sample book via private email

 

[robertvito]

Thanks, Max my email is robert (at) allstarglass (dot) (net)

If you can send over the sample file that would be awesome. Some o
your formulas are confusing to me ( Like I knew that would happen)

Thanks.

Rober

 

[Max]

Sample book on it's way to you ..