Help manipulating external data??

[jock]

In work I pull through a list of data externally using Microsoft Query and a
thing called relativity. It pulls through a list of job numbers and for each
job number there is a timestamp and an event for each time. Basically the
info looks like this

Job Number Time Event

31555 13:10 1
31555 13:15 3
31555 13:25 11
31666 18:10 5
31666 18:30 6
31666 18:33 12
31667 21:22 1
31667 21:26 11


However, I want to turn the information round in Excel so that it runs left
to right for each status, so for example

Job Number Event 1 Event 3 Event 11
31555 13:10 13:15 13:25


Does anyone know of a way to do this. I can't do it at source or through
Query, so it has to be done within Excel?

Thanks for any help in advance

 

[Bernie Deitrick]

jock,

Select your data table, then use Data / Pivot table and click Finish.

Then drag the Job Number button to the row area, the Event button to the column area, and the Time
button to the data area, and set the data field to sum and number format it for time.


HTH,
Bernie
MS Excel MVP

 

[jock]

Thanks Bernie, that has solved quite a few problems! Just another quick query
though, for some reason it is putting, for example, Event 3 first in the
table and then event 1, then event 11.

The Event lists are actually words like Completed, Booked etc, but I want to
be able to put these in the order along the row as I want, not how excel
decides. Can that be done?

 

[jock]

dont worry i have sorted that by reordering them.

Can i add extra information onto the end of each row, like driver name and
customer etc. This will be pulled from the external data as well?

 

[Bernie Deitrick]

jock,

You have a few options, and the best one depends on your data structure.

If your driver name is in the same table, you can just add it as the row data field. If it is in
another table, you can add a column of formulas to your data table to look up the driver name based
on the job number, and then use that in the pivot table, or you can use the job number in a formula
outside the pivot table - just type the cell address when referencing it instead of using cell
clicks, which will add the getpivotdata function (that doesn't copy well).

HTH,
Bernie
MS Excel MVP