Issues w/ using equations on pivot tables

[5hourenergy]

Hello all,

So i have been reviewing a large record set of data using a pivot table connected directly to my access database. From that pivot table i am trying to extract information but am getting stuck. Here's my issue:

In my pivot table i am pulling savings per item by supplier (data field = savings, item = row field, column field equals supplier). For each item in my row field, i am trying to find what my max savings is and then identify what supplier that is associated to it. I find my max savings using the following expression:

=IF(ISERROR(LARGE(G5:HW5,1)),"",IF(LARGE(G5:HW5,1)=0,IF(ISERROR(SMALL(G5:HW5,IA5)),0,SMALL(G5:HW5,IA5)),LARGE(G5:HW5,1)))

My goal is for it to pull the greatest number if there is a positive number, put in "" if there is no informaiton, or return the least negative number if all savings are negative. From there i use the following expression to identify what supplier is associated to that savings:

=IF(IE5="","",INDEX($G$4:$HW$4,1,MATCH(IE5,G5:HW5,0)))

Where g4:hw4 contain my supplier names; g5:hw5 contains the spends; and IE5 equals the max savings as pulled from the equation above. Here is where my issue is occuring...

For the second query, if there are multiple suppliers who are providing the same savings the query is pulling the first supplier name in that g4:hw4 range (sorted alphabetically). Therefore, if suppliers Bob, Cathy, and Rob bid the same savings on 40 items, it tells me that Bob is the "Best supplier" on all 40.

What equations can i use that will not give a weighting for suppliers this way and show me that Bob, Cathy, and Rob are ALL the top suppliers on the 40 items they had the top savings for? Also, would this calculation(and others like this) be better suited to use getpivotdata functions? If so, what would be the best place to get tutorials to do this effectively? I am using excel 2007.

Thank you in advance for your assistance and time.
Regards,
David

 

[Fazza]

Hi, David.

It would be easier to understand the question with some simple sample data, and maybe a screen image.

I don't understand about the pivot table. Do you need it? I get a bit lost with the mention of weightings. And then unsure of the ultimate objective.

Instead of a pivot table, what about a query table? Via keyboard ALT-D-D-N [I don't use Excel 2007] (Or alternative such as using VBA and a recordset via ADO.) Or even doing the query in Access?

Does this do one step for you? I've used this in an Excel query table for testing.

SELECT A.item, A.savings, A.supplier
FROM YourTable A, (
SELECT item, Max(savings) AS [savings]
FROM YourTable
GROUP BY item) B
WHERE A.item = B.item AND A.savings = B.savings

If there are multiple suppliers with the same maximum savings, all names will be returned.

I guess it'd be better to have such a query in Access and if there is another step after this, that one also in Access. Though can be in Excel if you prefer.

OK? HTH, Fazza