basic pivot table group

[Alan]

I have looked at some basic Pivot Table tutorials, but I have a
very basic question not answered by their examples. . . .

I have data in a spreadsheet formatted like this, each row
representing an event:

Timestamp Process Message ID
11:23:45:123 processA message123
11:23:45:124 processB message123
11:23:45:136 processC message123
11:23:46:123 processA message456
11:23:47:123 processB message456
11:23:47:678 processC message456

Can a pivot table help me reformat the data like below?

Process
Message ID processA processB processC
message123 11:23:45:123 11:23:45:124 11:23:45:136
message456 11:23:46:123 11:23:47:123 11:23:47:678

Or is that not the type of thing it can do?

I tried to lay this out, but the timestamp data was never
displayed (showed all "0"s or "1"s).

Thanks, Alan

 

[Dave Peterson]

Maybe...

If you convert that timestamp to a real time:
11:23:45.123 (notice the last colon was replaced by a decimal point)

And there can only be one timestamp per process per messageID. Then you can use
"Sum of" to add up that single time entry.

If you can do that, then you could use data|pivottable (xl2003 menus) to create
a table like:

Sum of Timestamp Process
Message ID processA processB processC Grand Total
message123 11:23:45.123 11:23:45.124 11:23:45.136 10:11:15.383
message456 11:23:46.123 11:23:47.123 11:23:47.678 10:11:20.924
Grand Total 22:47:31.246 22:47:32.247 22:47:32.814 20:22:36.307


But there is a bug/feature in xl that you'll have to work around, too.

The timestamp in the original data has to be formatted as general--or you'll
lose the decimal points in the pivottable.

You can either change the number format of that data--or you could just use a
helper column of formulas: =A2 that are formatted as general.

 

[Alan]

Dave,
Thanks for the tips. I managed to get what I wanted out of
this.

Pivot tables are sort of strange to set up when you`re not used
to them, but playing around with the layout interactively was
helpful.

Alan

 

[Alan]

I didn`t mention it earlier, but I wanted to do calculations on
the times, also. So, formatting it as General will not work. I think
I will use Java to do the calculations and skip the pivot table.

 

[Dave Peterson]

The format of the cells with times should bother any other cell that uses it in
a calculation.

Or you could just use the helper cell formatted as general.