Simple Excel sorting issue... 1

[f0rg3tfu1]

Hi all,

I hope this is the best place to ask this, so I am working with regular old Microsoft Excel/ Office Professional 2010.

So I have a regular old pivot table, and I have set the options to show "No Data" rather than the dreaded #DIV/0/. I have another sheet where I do % calculations based on hours of uptime vs hours of downtime. The #DIV/0! occurs when there is no data (0) and no instances (0), the result is the dreaded result.

So due to business reasons, I need to have this "No Data" result show to the top of all of our pivot tables. Is there a way to sort them so the "No Data" results show at the top of my top 20 pivot tables?

Example of the current pivot below:

Sum of ACDMA Voice Success Rate
Cell Tower Name Total
Cell 1805 - Kodiak Launch1 100.00%
Cell 1822 - Ouzinkie1 3.42%
Cell 1802 - Coast Guard (Bldg. 576)1 3.03%
Cell 1800 - Pillar Mountain2 2.87%
Cell 1801 - Near Island1 2.33%
Cell 1804 - Brechan1 1.78%
Cell 1800 - Pillar Mountain3 1.71%
Cell 1800 - Pillar Mountain 1.60%
Cell 1820 - Old Harbor1 1.60%
Cell 1821 - Port Lions1 1.57%
Cell 1805 - Kodiak Launch2 0.00%
Cell 1805 - Kodiak Launch3 No Call

 

[SkipVought]

Please post an example of your source data including at lease one instance of NO DATA.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[f0rg3tfu1]

I have set the #DIV/0! to show as "No Data" in the pivot table (right click->PivotTableOptions->For error values show)

Cell 1111 - Rabbit Creek CO1 0.0%
Cell 1111 - Rabbit Creek CO2 3.2%
Cell 1111 - Rabbit Creek CO3 5.3%
Cell 1125 - Anchorage Daily News (ADN)1 1.2%
Cell 1125 - Anchorage Daily News (ADN)2 4.3%
Cell 1125 - Anchorage Daily News (ADN)3 2.7%
Cell 1191 - IBEW1 2.9%
Cell 1191 - IBEW2 2.2%
Cell 1191 - IBEW3 3.8%
Cell 1211 - Key Bank1 2.3%
Cell 1211 - Key Bank2 2.0%
Cell 1211 - Key Bank3 2.9%
Cell 1266 - Knob Ridge1 0.0%
Cell 1306 - Valley1 2.7%
Cell 1306 - Valley2 2.9%
Cell 1306 - Valley3 11.3%
Cell 1406 - Admiralty1 5.8%
Cell 1406 - Admiralty2 4.5%
Cell 1406 - Admiralty3 2.4%
Cell 1416 - Schrock Road1 5.8%
Cell 1107 - Service Hight1 1.4%
Cell 1107 - Service Hight2 0.0%
Cell 1107 - Service Hight3 0.0%
Cell 1115 - Government Hill1 1.3%
Cell 1115 - Government Hill2 4.0%
Cell 1115 - Government Hill3 0.0%
Cell 1132 - Key Center1 3.3%
Cell 1132 - Key Center2 1.3%
Cell 1132 - Key Center3 2.7%
Cell 1162 - Lake Otis & Tudor1 1.8%
Cell 1162 - Lake Otis & Tudor2 2.9%
Cell 1162 - Lake Otis & Tudor3 0.7%
Cell 1164 - Inlet Towers1 3.0%
Cell 1164 - Inlet Towers2 2.8%
Cell 1164 - Inlet Towers3 3.7%
Cell 1181 - Jewel Lake & Rasperry1 0.0%
Cell 1181 - Jewel Lake & Rasperry2 1.7%
Cell 1181 - Jewel Lake & Rasperry3 0.0%
Cell 1194 - Hope CO1 5.4%
Cell 1194 - Hope CO2 2.3%
Cell 1194 - Hope CO3 0.0%
Cell 1196 - Bear Valley (Anchorage)1 4.9%
Cell 1196 - Bear Valley (Anchorage)2 0.0%
Cell 1196 - Bear Valley (Anchorage)3 1.7%
Cell 1359 - Gunnuck1 1.1%
Cell 1402 - Meadow Lakes1 3.3%
Cell 1402 - Meadow Lakes2 0.0%
Cell 1402 - Meadow Lakes3 1.3%
Cell 1408 - MTA Spruce1 2.6%
Cell 1408 - MTA Spruce2 0.6%
Cell 1408 - MTA Spruce3 1.6%
Cell 1409 - NEK Lake1 1.5%
Cell 1409 - NEK Lake2 2.8%
Cell 1409 - NEK Lake3 0.5%
Cell 1423 - Talkeetna1 5.2%
Cell 1423 - Talkeetna2 0.0%
Cell 1763 - Kenai1 15.4%
Cell 1763 - Kenai2 1.3%
Cell 1763 - Kenai3 2.1%
Cell 1784 - Homer Spit1 2.1%
Cell 1121 - Alaska Regional1 2.3%
Cell 1121 - Alaska Regional2 0.6%
Cell 1121 - Alaska Regional3 1.1%
Cell 1184 - Ship Creek (Centennial)1 2.8%
Cell 1184 - Ship Creek (Centennial)2 2.6%
Cell 1184 - Ship Creek (Centennial)3 3.3%
Cell 1261 - Delta Junction1 1.6%
Cell 1452 - Tolsona Ridge1 0.0%
Cell 1453 - Willow Mountain1 6.3%
Cell 1453 - Willow Mountain2 2.9%
Cell 1741 - Naptowne1 2.5%
Cell 1744 - Soldotna City1 1.7%
Cell 1744 - Soldotna City2 3.2%
Cell 1744 - Soldotna City3 3.2%
Cell 1804 - Brechan1 1.8%
Cell 1213 - Bentley Mall1 1.4%
Cell 1213 - Bentley Mall2 2.4%
Cell 1213 - Bentley Mall3 3.3%
Cell 1217 - Sportsmans Way1 1.9%
Cell 1217 - Sportsmans Way2 1.7%
Cell 1217 - Sportsmans Way3 4.8%
Cell 1291 - Kuparuk1 2.1%
Cell 1291 - Kuparuk2 1.6%
Cell 1291 - Kuparuk3 3.4%
Cell 1361 - Cape Spencer1 #DIV/0!
Cell 1118 - Skyline1 0.0%
Cell 1118 - Skyline2 1.7%
Cell 1118 - Skyline3 2.3%
Cell 1169 - Sand Lake1 3.6%
Cell 1169 - Sand Lake2 4.3%
Cell 1169 - Sand Lake3 2.1%
Cell 1175 - Airport (Reeves)1 0.0%
Cell 1175 - Airport (Reeves)2 3.0%
Cell 1175 - Airport (Reeves)3 1.7%
Cell 1182 - Spenard1 1.6%
Cell 1182 - Spenard2 1.4%
Cell 1182 - Spenard3 2.3%
Cell 1186 - Elmendorf Base Housing1 4.8%
Cell 1186 - Elmendorf Base Housing2 5.5%
Cell 1186 - Elmendorf Base Housing3 1.4%
Cell 1208 - UAF1 1.7%
Cell 1208 - UAF2 1.1%
Cell 1208 - UAF3 2.1%
Cell 1221 - Richardson Hwy1 3.7%
Cell 1221 - Richardson Hwy2 5.2%
Cell 1221 - Richardson Hwy3 4.9%
Cell 1223 - Herning Rd1 2.6%
Cell 1223 - Herning Rd2 0.5%
Cell 1223 - Herning Rd3 1.7%
Cell 1259 - ECO (Eielson CO)1 3.2%
Cell 1265 - Pedro Dome1 3.8%
Cell 1304 - Juneau City1 3.8%
Cell 1304 - Juneau City2 0.0%
Cell 1304 - Juneau City3 2.9%
Cell 1351 - Sitka Mud Bay1 7.6%
Cell 1360 - Point Howard1 #DIV/0!
Cell 1367 - Petersburg1 1.3%
Cell 1371 - Petersburg 2 (Lindenberg)1 #DIV/0!

 

[SkipVought]

In your source data, append a column to your table in the next column with this Heading NO DATA, and this formula...
[tt]
=IF(ISERROR(B2),"NO DATA","")
[/tt]

Select in the Pivottable and in the Ribbon>>>

PovitTable Tools > Analyze > Data > Change Data Source...

and change the source to reflect the new column.

Refresh your PT

Drag the NO DATA field to the FIRST position in the ROW area.

SORT this new column in Z-A order.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[f0rg3tfu1]

SkipVought,

Worked perfectly! I have one very odd issue that I have been trying to figure out. I have several different PT running off the same data, they are all formatted exactly the same coming from the same data, yet ONE of them has a different output than the others:

All others come back like this:
Sum of ACDMA Voice Success Rate
NO CALLS Cell Tower Name Total
NO CALLS Cell 1122 - Kincaid Park3 No Call
Cell 1920 - Kotzebue No Call
Cell 1116 - Eagle River Valley3 10.8%
Cell 1160 - O'Malley2 10.7%
Cell 1160 - O'Malley1 10.5%
Cell 1209 - North Pole2 8.7%
Cell 1131 - Holy Spirit2 8.3%
Cell 1150 - Sullivan Arena1 7.1%
Cell 1179 - Ft Richardson Hwy3 6.9%
Cell 1172 - Arctic Blvd1 6.7%
Cell 1114 - Turnagain3 6.5%
Cell 1183 - Debarr & Boniface2 6.1%


ONE PT comes back like this and I can't figure out why:

Sum of ACDMA Voice Success Rate
Row Labels Total
NO CALLS
Cell 1463 - Chitna1 No Call

Cell 1458 - Tok Cut-Off1 6.56%
Cell 1457 - Valdez 21 14.68%
Cell 1455 - Aurora1 12.50%
Cell 1453 - Willow Mountain1 6.25%
Cell 1434 - Maud Road3 4.49%
Cell 1433 - Willow3 5.00%

 

[SkipVought]

Check to determine if they ALL refer to the same source table range reference and have ALL been Refreshed.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]