To Pivot or not to Pivot

[Ucubia]

Hi

I have a worksheet containing nearly 1200 offices in Column A

Column B,C and D are just administration (office address, contact, tel)
Column E identified network bandwidth utilisation
Column F identifies the minimum bandwidth usage
Column G identifies the maximum bandwidth usage
Column H identifies the number of network resets

I want to be able to get the Top 10 for each of Column F,G and H and then display the corresponding site name from Column A

I am new to Pivot Tables, so if someone could give me some idea as to the best way to tackle this I would be grateful. Unfortunately, I am having to use the customer supplied Excel 2003 although I will best looking at offering Excel 2010 as a solution.

If There is a better way to do this other than Pivot Table then I would be interested to hear that as well.

Many thanks

 

[llenza]

If this is all you need to do. I recommend keeping it simple. In Excel 2000-2010 you can set a filter for "Top 10 Autofilter" to bottom (MIN) and top (MAX)

STEPS:
1. Sheet created
2. Enable AutoFilter
3. Under column "Min Bandwidth" click on arrow and select (TOP 10...)
4. "TOP 10 Autofilter" window displays, so change values to "Bottom, 10 and Items"
5. Click OK
6. Top 10 min item are displayed
7. To calculate MAX remove TOP 10... filter in MIN
8. Do the same that was done for MIN and do it for MAX (4. Values this time are "TOP, 10 and Items")

see illustration below:

[TABLE="width: 472"]
<tbody>[TR]
[TD]Office
[/TD]
[TD]Network
[/TD]
[TD]Min Bandwith
[/TD]
[TD]Max Bandwidth
[/TD]
[/TR]
[TR]
[TD]NY
[/TD]
[TD="align: right"]750
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]1200
[/TD]
[/TR]
[TR]
[TD]GA
[/TD]
[TD="align: right"]800
[/TD]
[TD="align: right"]150
[/TD]
[TD="align: right"]1150
[/TD]
[/TR]
[TR]
[TD]MD
[/TD]
[TD="align: right"]850
[/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]1100
[/TD]
[/TR]
[TR]
[TD]SC
[/TD]
[TD="align: right"]900
[/TD]
[TD="align: right"]250
[/TD]
[TD="align: right"]1050
[/TD]
[/TR]
[TR]
[TD]NC
[/TD]
[TD="align: right"]950
[/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"]1000
[/TD]
[/TR]
[TR]
[TD]DE
[/TD]
[TD="align: right"]1000
[/TD]
[TD="align: right"]350
[/TD]
[TD="align: right"]950
[/TD]
[/TR]
[TR]
[TD]PA
[/TD]
[TD="align: right"]1050
[/TD]
[TD="align: right"]400
[/TD]
[TD="align: right"]900
[/TD]
[/TR]
[TR]
[TD]CA
[/TD]
[TD="align: right"]1100
[/TD]
[TD="align: right"]450
[/TD]
[TD="align: right"]850
[/TD]
[/TR]
[TR]
[TD]MN
[/TD]
[TD="align: right"]1150
[/TD]
[TD="align: right"]500
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD]TX
[/TD]
[TD="align: right"]1200
[/TD]
[TD="align: right"]550
[/TD]
[TD="align: right"]750
[/TD]
[/TR]
[TR]
[TD]OH
[/TD]
[TD="align: right"]1250
[/TD]
[TD="align: right"]600
[/TD]
[TD="align: right"]700
[/TD]
[/TR]
[TR]
[TD]AL
[/TD]
[TD="align: right"]1300
[/TD]
[TD="align: right"]650
[/TD]
[TD="align: right"]650
[/TD]
[/TR]
</tbody>[/TABLE]

All inclusive and all values displayed

[TABLE="width: 472"]
<tbody>[TR]
[TD]Office
[/TD]
[TD]Network
[/TD]
[TD]Min Bandwith
[/TD]
[TD]Max Bandwidth
[/TD]
[/TR]
[TR]
[TD]NY
[/TD]
[TD="align: right"]750
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]1200
[/TD]
[/TR]
[TR]
[TD]GA
[/TD]
[TD="align: right"]800
[/TD]
[TD="align: right"]150
[/TD]
[TD="align: right"]1150
[/TD]
[/TR]
[TR]
[TD]MD
[/TD]
[TD="align: right"]850
[/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]1100
[/TD]
[/TR]
[TR]
[TD]SC
[/TD]
[TD="align: right"]900
[/TD]
[TD="align: right"]250
[/TD]
[TD="align: right"]1050
[/TD]
[/TR]
[TR]
[TD]NC
[/TD]
[TD="align: right"]950
[/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"]1000
[/TD]
[/TR]
[TR]
[TD]DE
[/TD]
[TD="align: right"]1000
[/TD]
[TD="align: right"]350
[/TD]
[TD="align: right"]950
[/TD]
[/TR]
[TR]
[TD]PA
[/TD]
[TD="align: right"]1050
[/TD]
[TD="align: right"]400
[/TD]
[TD="align: right"]900
[/TD]
[/TR]
[TR]
[TD]CA
[/TD]
[TD="align: right"]1100
[/TD]
[TD="align: right"]450
[/TD]
[TD="align: right"]850
[/TD]
[/TR]
[TR]
[TD]MN
[/TD]
[TD="align: right"]1150
[/TD]
[TD="align: right"]500
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD]TX
[/TD]
[TD="align: right"]1200
[/TD]
[TD="align: right"]550
[/TD]
[TD="align: right"]750
[/TD]
[/TR]
</tbody>[/TABLE]

Above autofilter set to top 10 MIN

[TABLE="width: 472"]
<tbody>[TR]
[TD]Office
[/TD]
[TD]Network
[/TD]
[TD]Min Bandwith
[/TD]
[TD]Max Bandwidth
[/TD]
[/TR]
[TR]
[TD]NY
[/TD]
[TD="align: right"]750
[/TD]
[TD="align: right"]100
[/TD]
[TD="align: right"]1200
[/TD]
[/TR]
[TR]
[TD]GA
[/TD]
[TD="align: right"]800
[/TD]
[TD="align: right"]150
[/TD]
[TD="align: right"]1150
[/TD]
[/TR]
[TR]
[TD]MD
[/TD]
[TD="align: right"]850
[/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]1100
[/TD]
[/TR]
[TR]
[TD]SC
[/TD]
[TD="align: right"]900
[/TD]
[TD="align: right"]250
[/TD]
[TD="align: right"]1050
[/TD]
[/TR]
[TR]
[TD]NC
[/TD]
[TD="align: right"]950
[/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"]1000
[/TD]
[/TR]
[TR]
[TD]DE
[/TD]
[TD="align: right"]1000
[/TD]
[TD="align: right"]350
[/TD]
[TD="align: right"]950
[/TD]
[/TR]
[TR]
[TD]PA
[/TD]
[TD="align: right"]1050
[/TD]
[TD="align: right"]400
[/TD]
[TD="align: right"]900
[/TD]
[/TR]
[TR]
[TD]CA
[/TD]
[TD="align: right"]1100
[/TD]
[TD="align: right"]450
[/TD]
[TD="align: right"]850
[/TD]
[/TR]
[TR]
[TD]MN
[/TD]
[TD="align: right"]1150
[/TD]
[TD="align: right"]500
[/TD]
[TD="align: right"]800
[/TD]
[/TR]
[TR]
[TD]TX
[/TD]
[TD="align: right"]1200
[/TD]
[TD="align: right"]550
[/TD]
[TD="align: right"]750
[/TD]
[/TR]
</tbody>[/TABLE]

Above autofilter set to top 10 MAX

Good Luck!

 

[Ucubia]

Hi and thank you for the reply

This I was aware of the problem being that I need to dashboard thing with several small charts to show max bw sites, min bw sites, etc etc

Any thoughts?

Many thanks

 

[llenza]

If a Pivot Table is what you need create two pivot tables and then create charts from the two once that represents MIN and the other MAX.
To convert the main data into a pivot table convert it to a Link Table, see help for details or visit the youtube video feed Excel Magic #900 via the browse engine.

Good Luck!