Querying a list with Excel

[Martin]

I am starting out with a list like


Tech Date Code Actual Billed
DON 11/15/2004 E 5.40 6.00
DON 11/15/2004 W 3.00 4.00
DON 11/15/2004 E 4.00 4.00
DON 11/15/2004 DO 0.30 4.00
DON 11/15/2004 HD 8.00 0.00
Paul 11/15/2004 E 3.00 4.00
Paul 11/15/2004 E 2.30 2.00
Paul 11/15/2004 E 0.40 0.50
Paul 11/15/2004 E 0.60 0.50
Paul 11/15/2004 E 3.00 3.00
Paul 11/16/2004 W 3.00 0.90
Paul 11/16/2004 CV 2.00 2.30
Paul 11/16/2004 CV 2.40 2.00
Fred 11/15/2004 PDI 3.50 4.50
Fred 11/15/2004 W 0.30 0.20
Fred 11/15/2004 W 0.10 0.20
Fred 11/15/2004 W 2.30 3.00
Fred 11/16/2004 LD 2.00 1.50
Paul 11/16/2004 LD 3.00 3.00
Paul 11/16/2004 ST 0.40 0.00
Paul 11/16/2004 E 4.00 3.00
Paul 11/16/2004 E 3.00 4.50

and I want to create a report that looks like this

Beginning date/Ending date Don/Paul/Fred/Paul/etc.
(this has to be dynamic when we add or delete
Tech's)
CV SUM OF TOTAL ACTUAL HOURS BY CODE
E SUM OF TOTAL ACTUAL HOURS BY CODE
PDI ETC.
ST
LD
W
HD
DO

BIGGEST PROBLEMS I HAVE IS GETTING THE TECH NAMES IN THE LIST IN A
COLUMN HEADING AND THEN CALCULATING THE SUM OF EACH CODE BY TECH IN
THE TABLES CELLS.
In addition the spreadsheet needs to be able to recognize the
beginning and ending date from the list.

I WOULD APPRECIATE ANY HELP I CAN GET

MARTIN

 

[Guest]

Your data is perfect for a Pivot Table. Select the data and choose Data ->
Pivot Table

When the Wizard comes up You can just choose finish (This is most likely
what you want)

Drag the data items (dimensions) off of the tool bar and onto the table.
Tech in the left column and Amount in the middle. Since you have dates you
can also do an active time series if you need to. Add the dates to the pivot
table and right click on them. Choose Group and you will be able to group by
Month or Quarter of whatever you want. Play around with it it is really
powerful.

 

[JE McGimpsey]

Did the replies to the exact same post you made yesterday not work for
you?

If not, instead of posting the exact same thing, try explaining why it
didn't work.

If you didn't *see* the replies, you can always check the archives
(starting 4-8 hours after your post):

http://groups.google.com/advanced_group_search?q=group:*excel*

Put your name/email address in the "Author" box to see all the posts
you've ever made to the XL groups, along with any replies.