Rank Cheapest Product by Location and Supplier

L

ls_thomp

New Member
Joined
Mar 1, 2014
Messages
1
I am new to the forum and have been struggling to figure out how to rank the cheapest price by location and product. Essentially, I am trying to identify which supplier has the cheapest price for a specific location and product and rank that grouping by the cheapest price. I am not good at VB and would like a formula to calculate this. Please help.

SupplierLocationProductPriceDesired Results
Supplier 1Location 1Product 12.81772
Supplier 2Location 1Product 12.82924
Supplier 3Location 1Product 12.8121
Supplier 4Location 1Product 12.823
Supplier 5Location 1Product 12.83645
Supplier 1Location 2Product 12.86753
Supplier 2Location 2Product 12.9724
Supplier 3Location 2Product 12.9724
Supplier 4Location 2Product 13.01851
Supplier 5Location 2Product 13.1722
Supplier 1Location 3Product 13.07854
Supplier 2Location 3Product 13.16855
Supplier 3Location 3Product 12.33592
Supplier 4Location 3Product 12.53
Supplier 5Location 3Product 12.1841
Supplier 1Location 1Product 22.80011
Supplier 2Location 1Product 22.83152
Supplier 3Location 1Product 22.8363
Supplier 4Location 1Product 22.83814
Supplier 5Location 1Product 22.8525
Supplier 1Location 2Product 22.86314
Supplier 2Location 2Product 22.8835
Supplier 3Location 2Product 23.01851
Supplier 4Location 2Product 23.04063
Supplier 5Location 2Product 23.03852
Supplier 1Location 3Product 22.85821
Supplier 2Location 3Product 22.8692
Supplier 3Location 3Product 22.89963
Supplier 4Location 3Product 22.9014
Supplier 5Location 3Product 22.90415
Supplier 1Location 1Product 33.0031
Supplier 2Location 1Product 33.07852
Supplier 3Location 1Product 33.2634
Supplier 4Location 1Product 33.16853
Supplier 5Location 1Product 32.92795
Supplier 1Location 2Product 32.93655
Supplier 2Location 2Product 32.86824
Supplier 3Location 2Product 33.0371
Supplier 4Location 2Product 33.03772
Supplier 5Location 2Product 33.06373
Supplier 1Location 3Product 33.10873
Supplier 2Location 3Product 33.144
Supplier 3Location 3Product 33.2085
Supplier 4Location 3Product 32.7041
Supplier 5Location 3Product 32.70492

<colgroup><col span="4" style="text-align: left; "><col style="text-align: left; "></colgroup><tbody>
</tbody>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
ls_thomp said:
I am new to the forum and have been struggling to figure out how to rank the cheapest price by location and product. Essentially, I am trying to identify which supplier has the cheapest price for a specific location and product and rank that grouping by the cheapest price. I am not good at VB and would like a formula to calculate this. Please help.

Supplier
Location
Product
Price
Desired Results
Supplier 1
Location 1
Product 1
2.8177
2
Supplier 2
Location 1
Product 1
2.8292
4
Supplier 3
Location 1
Product 1
2.812
1
Supplier 4
Location 1
Product 1
2.82
3
Supplier 5
Location 1
Product 1
2.8364
5
Supplier 1
Location 2
Product 1
2.8675
3
Supplier 2
Location 2
Product 1
2.972
4
Supplier 3
Location 2
Product 1
2.972
4
Supplier 4
Location 2
Product 1
3.0185
1
Supplier 5
Location 2
Product 1
3.172
2
Supplier 1
Location 3
Product 1
3.0785
4
Supplier 2
Location 3
Product 1
3.1685
5
Supplier 3
Location 3
Product 1
2.3359
2
Supplier 4
Location 3
Product 1
2.5
3
Supplier 5
Location 3
Product 1
2.184
1
Supplier 1
Location 1
Product 2
2.8001
1
Supplier 2
Location 1
Product 2
2.8315
2
Supplier 3
Location 1
Product 2
2.836
3
Supplier 4
Location 1
Product 2
2.8381
4
Supplier 5
Location 1
Product 2
2.852
5
Supplier 1
Location 2
Product 2
2.8631
4
Supplier 2
Location 2
Product 2
2.883
5
Supplier 3
Location 2
Product 2
3.0185
1
Supplier 4
Location 2
Product 2
3.0406
3
Supplier 5
Location 2
Product 2
3.0385
2
Supplier 1
Location 3
Product 2
2.8582
1
Supplier 2
Location 3
Product 2
2.869
2
Supplier 3
Location 3
Product 2
2.8996
3
Supplier 4
Location 3
Product 2
2.901
4
Supplier 5
Location 3
Product 2
2.9041
5
Supplier 1
Location 1
Product 3
3.003
1
Supplier 2
Location 1
Product 3
3.0785
2
Supplier 3
Location 1
Product 3
3.263
4
Supplier 4
Location 1
Product 3
3.1685
3
Supplier 5
Location 1
Product 3
2.9279
5
Supplier 1
Location 2
Product 3
2.9365
5
Supplier 2
Location 2
Product 3
2.8682
4
Supplier 3
Location 2
Product 3
3.037
1
Supplier 4
Location 2
Product 3
3.0377
2
Supplier 5
Location 2
Product 3
3.0637
3
Supplier 1
Location 3
Product 3
3.1087
3
Supplier 2
Location 3
Product 3
3.14
4
Supplier 3
Location 3
Product 3
3.208
5
Supplier 4
Location 3
Product 3
2.704
1
Supplier 5
Location 3
Product 3
2.7049
2

<TBODY>
</TBODY>
Click to expand...

Hi and welcome to Mr Excel

Could you confirm the expected results for Location 2 - Product 1 (in red)?

Also for Location 2 - Product 2; Location 1 - Product 3; Location 2 - Product 3.

I don't understand how did you get these expected results

M.
 
Upvote 0
The below ranking should not it be 1,2,2,4,5?



-- removed inline image ---
Immagine.jpg
 
Upvote 0
Sorry, I goofed up...It should be 1,2,3,5,4 on the first box and 2,1,3,4,5 on the second.....
 
Upvote 0
hi

you may then try

=MATCH(D2,SMALL(IF(($B$2:$B$46=B2),IF(($C$2:$C$46=C2),$D$2:$D$46)),ROW($D$2:$D$46)-ROW($D$1)),0)

confirming with Control Shift Enter
 
Upvote 0
Or a regular formula

E2
=COUNTIFS(B:B,B2,C:C,C2,D:D,"<"&D2)+1
copy down

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Ranking Prices and Colour Coding
Replies
3
Views
513
SanjayGMusafir
SanjayGMusafir
E
  • Solved
How do I get sum product to ignore empty cells to calculate the cost basis of shares?
Replies
5
Views
183
Peter_SSs
Peter_SSs
E
  • Question
Alternative to complex SumProduct to rank data by specific variable
Replies
3
Views
565
Peter_SSs
Peter_SSs
Z
  • Solved
Changes required in VBA code
Replies
8
Views
479
Zubair
Z
Z
  • Solved
Document number not appearing through VBA code
Replies
2
Views
322
Zubair
Z

Forum statistics

Threads
1,214,875
Messages
6,122,039
Members
449,063
Latest member
ak94

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top