Consolidation of Ranges & Nested IF

**scorpio0077** · 06-10-2009, 11:59 PM

Hey All, I'm currently working with a Production control problem. I have three columns, namely
a. Product Part number
b. Vendor
c. Cost of a product

I need do these with the work sheet:

1. Find the vendor with the highest cost of products sold and his net cost. (The same vendor will be selling multiple part numbers. So I would have to calculate the biggest vendor by grouping net cost across multiple part numbers)
2. Find the net part numbers supplied by the vendor with the highest cost.

I have attached an example excel sheet. It would be great if you could help me out with this.

Thanks!!

**DonkeyOte** · 06-11-2009, 01:49 AM

You could use a PT to do this for you.

Using your sample file you can create as follows:

1. highlight the data A1:C21
2. Insert -> Pivot Table
3. Click OK
4. Drag Vendor to Row Label
5. Drag Part to Row Label
6. Drag Cost to Value area
7. Right Click on a Vendor entry in the PT and select Filter -> Top10 then:
change 10 to 1 (and ensure Filtering on Sum of Cost)

The PT will now display the Vendor with the greatest cost and subsequently list all Parts assigned to that Vendor.

If you were to change say C2 on the source sheet from 57 to 1000 and refresh you will find the Vendor changes from B to A and the parts listed update accordingly.

**scorpio0077** · 06-11-2009, 10:44 AM

Thanks for the reply. A pivot table would not be the best solution for me, as I plan to use the final result from this data as part of a model, namely the number fits in to calculate another set of formulas.

I'm trying to automate the entire sheet, that a single change in the master file will recalculate the entire database {if you may call an excel worksheet so

}

Are there any more ideas?