To find the Cheapest Price

[Guest]

I have a list as follows:
Col A -- Vendors Name
Col B -- Part Numbers
Col C -- Condition of Item
Col D -- Price

My purpose to find out the cheapest price: let we say, i entry at E1 the Part Number, i want to buy...I wish in Column F - Col I give me the list of Vendors on specific Part Number, condition, ascending in the price...

I wish to use "Formula", i do not intend to use Criteria...

Thank you guys, in helping me...

brgds,andri

 

[Frank Kabel]

Hi Andri
one way: enter the following array formulas (entered with
CTRL+SHIFT+ENTER)
F1: (Vendor name)
=INDEX($A$1:$A$1,MATCH(1,($B$1:$B$100=$E$1)*($D$1:$D$100=H1),0))

G1: Part number:
=$E$1

H1: (Condition):
=INDEX($A$1:$A$1,MATCH(1,($C$1:$C$100=$E$1)*($D$1:$D$100=H1),0))

I1: (price)
=SMALL(IF($B$1:$B$100=$E$1,$D$1:$D$100),ROW(1:1))

copy all formulas down as far as needed. (Note: This will NOT work if
two vendors offer the product for the same price. But in this case why
not simply sort the list by the price column and use 'Data - Filter' to
filter the part number?)

 

[Guest]

Dear Frank,

Thank you for your formula. It works very well.
the reason, i do not use Filter...cause i would like to get the cheapest price for more than one part number at the same time.

highly appreciate that.

Brgds,andri

 

[Domenic]

Another way,

Assuming your column headers are in Row 1, and your data starts in Row 2:

E1 contains the part number of interest

F1 must contain a 0

F2, copied down:

=IF((A2<>"")*(B2=$E$1),LOOKUP(9.99999999999999E+307,$F$1:F1)+1,"")

G1:

=LOOKUP(9.99999999999999E+307,F:F)

H2, copied across and down:

=IF(ROW()-1<=$G$1,INDEX(A$2:A$100,MATCH(SMALL(IF($B$2:$B$100=$E$1,$D$2:$D
$100),ROW(1:1)),$D$2:$D$100,0)),"")

entered using CTRL+SHIFT+ENTER

As in Frank's approach, this will not work if there are two vendors with
the same price.

 

[AlfD]

Hi!

I don't know how your space in the workbook is used, but the followin
approach works for me.

Aim: to put a part number in E1, "press a button" and have a list o
the items which have that part number listed in ascending order o
price.

Method: Actually do this and record a macro while doing it. It is ver
simple and only needs a bit of tidying up at the end.
I will assume your worksheet is called Sheet1.

Record these steps:
1. Put a part number in E1
2. Copy Sheet1 (use edit > move or copy sheet)
3. Click on the tab of this sheet (should have name Sheet1 (1))
Rename it as Temp. (Use right-click on its tab > rename)
4. In F1 put =if($E$1=B1,"#","") (in other words, if the part numbe
in col B matches the part number in E1 (absolute reference) then show
#.
5. Copy this formula down to the end of your data.
6. Now apply Autofilter to this table. (Data > Filter > Autofilter)
7. Select column F and show only items with #.
8. Now copy the visible cells in the table (F5 > Goto Special
visible cells only followed by Ctrl-C)
9. Select Sheet1 and Paste Special > Values in F1.
If you want to tidy the display (e.g. get rid of unwanted columns, d
it now: any formatting you want?)
10. Now sort this (still selected) data by the price column.
11. Select the sheet labelled Temp: delete it.
12. Return to Sheet1.
13. Stop recording.

If I've transcribed this properly, you will have your list for tha
part number. Test it: run the macro with the same part number: the
another. It will be a bit clunky, but two tweaks will smooth it an
speed it up.

Open the macro (Alt+F8) and select your macro for editing.
Add 2 lines:

Application.ScreenUpdating = False
and
Application.DisplayAlerts=False

as the first actions in the macro.

When you have finished with the list, simply delete it

 

[Domenic]

After taking another look at the solution I offered, I would make the
following changes in order to achieve greater efficiency:

First part is unchanged...

Assuming your column headers are in Row 1, and your data starts in Row 2:

E1 contains the part number of interest

F1 must contain a 0

F2, copied down:

=IF((A2<>"")*(B2=$E$1),LOOKUP(9.99999999999999E+307,$F$1:F1)+1,"")

G1:

=LOOKUP(9.99999999999999E+307,F:F)

Next part would change to...

H2, copied down:

=IF(ROW()-ROW($H$2)+1<=$G$1,MATCH(SMALL(IF($B$2:$B$100=$E$1,$D$2:$D$100),
ROW()-ROW($H$2)+1),$D$2:$D$100,0),"")

entered using CTRL+SHIFT+ENTER

I2, copied across and down:

=IF(N($H2),INDEX(A$2:A$100,$H2),"")

*Adjust the ranges for these last two formulas according to your table

*Again, if there are two or more vendors with the same price, it will
only list the first one it finds.