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

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

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