Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
I have a spreadsheet which is about 1500 rows long. There are about a total
of 150 people on it, with duplicates. Each person had a listing for each piece of desktop software they currently have on his/her system. The header line is as follows: Name Org CompName Property# Software/Program Version Licensed (Y/N) If Licensed, artifact or proof (describe) Comments It obviously goes all the way across. My boss wants to see a breakdown (I think a pivot table will be the best way to go about it) based first on the Program, then the version, and then whether it's licensed or not. The problem is that not all fields have a value, so the pivot table may not be the way to go. Can anyone throw out some ideas of how to best present this? I thought of doing just a custom sort first by program, version, and then license. But I think there has to be a nicer way to present this data. Thanks so much!!! Judi |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
PivotTable would my choice, see
http://www.ozgrid.com/Excel/excel-pivot-tables.htm and place the criteria fields in the Page Field/Report Filter -- Regards Dave Hawley www.ozgrid.com "Judi" wrote in message ... I have a spreadsheet which is about 1500 rows long. There are about a total of 150 people on it, with duplicates. Each person had a listing for each piece of desktop software they currently have on his/her system. The header line is as follows: Name Org CompName Property# Software/Program Version Licensed (Y/N) If Licensed, artifact or proof (describe) Comments It obviously goes all the way across. My boss wants to see a breakdown (I think a pivot table will be the best way to go about it) based first on the Program, then the version, and then whether it's licensed or not. The problem is that not all fields have a value, so the pivot table may not be the way to go. Can anyone throw out some ideas of how to best present this? I thought of doing just a custom sort first by program, version, and then license. But I think there has to be a nicer way to present this data. Thanks so much!!! Judi |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
I'm not sure I understand how your data is laid out.
When you say it goes all the way across, does this mean that you have multiple columns with the program name, version, licensed, etc on the same row. So a record for one user could be 9(?) columns (a single piece of software) and the next person could have 34 columns (4 id fields + 5 programs (each with 5 fields)? So each user has exactly one row???? If that's the case, then I'd rearrange the data into one row of data per software/program. Yep, then some users will have 1 record/row and some users will have lots of rows. After that's done, you could use data|sort and data|subtotals or pivottables to get nice counts of different slices of your data. Judi wrote: I have a spreadsheet which is about 1500 rows long. There are about a total of 150 people on it, with duplicates. Each person had a listing for each piece of desktop software they currently have on his/her system. The header line is as follows: Name Org CompName Property# Software/Program Version Licensed (Y/N) If Licensed, artifact or proof (describe) Comments It obviously goes all the way across. My boss wants to see a breakdown (I think a pivot table will be the best way to go about it) based first on the Program, then the version, and then whether it's licensed or not. The problem is that not all fields have a value, so the pivot table may not be the way to go. Can anyone throw out some ideas of how to best present this? I thought of doing just a custom sort first by program, version, and then license. But I think there has to be a nicer way to present this data. Thanks so much!!! Judi -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Pivot Table and Pivot Table dates are not in correct order | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel |