Querying a list with Excel

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.

APPRECIATE ANY HELP I CAN GET

MARTIN
0
11/21/2004 4:24:38 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
224 Views

Similar Articles

[PageSpeed] 52

I'd recommend a Pivot Table...

See Debra Dalgleish's beginner's tutorial at

    http://peltiertech.com/Excel/Pivots/pivotstart.htm

She's also got a lot more good stuff on PTs at her site:

    http://contextures.com/tiptech.html

In article <e7d85b64.0411210824.309b066a@posting.google.com>,
 mzonnenberg@campingtime.com (Martin) wrote:

> 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.
> 
> APPRECIATE ANY HELP I CAN GET
0
jemcgimpsey (6723)
11/21/2004 4:33:29 PM
Two options...

1. Build a pivot table from your data. In order to include new records
create a dynamic named range which you can feed to the pivot tabl
wizard as range. See for an example:

http://www.mrexcel.com/board2/viewtopic.php?t=114283&highlight=pbase

2. A formula system that would extract a distinct list of Tech's i
order to invoke formulas for multi-conditional summing. Are yo
interested in this option?

Martin Wrote:
> 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.
> 
> APPRECIATE ANY HELP I CAN GET
> 
> MARTI

--
Aladin Akyure
-----------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=416
View this thread: http://www.excelforum.com/showthread.php?threadid=31916

0
11/21/2004 4:51:44 PM
Reply:

Similar Artilces:

Convert excel to java
Hi does anybody know a way to convert an XL spreadhseet (with charts) to a fully interactive Java object?? I have tried exceleverywhere and xlsius, but no luck! Thanks in advance -- shnim1 ...

Am I able to sort dates by month rather than year in excel?
I am using office 2003. I need a report that sorts my employees in order by the month they are hired. Excel always sorts by year. Just an example: If a date is in A1 and the rest of the column, then in B1 enter =MONTH(A1) and copy down Then sort by column B -- Gary's Student "Ruth k." wrote: > I am using office 2003. I need a report that sorts my employees in order by > the month they are hired. Excel always sorts by year. ...

Advanced Find query
Hi all, We would like to get a list of all accounts that haven't had any activities for the last three months. This is similar to system view "Accounts: No Orders in Last 6 Months" which is a system, API based view which to my knowledge can't be produced using the Advanced Find view. I'm just trying to get my head around how we are going to produce these types of reports/queries in the future. These are seldomly run and I believe they need to be modified all the time so building reports feels like overdoing things. What would you do? SQL queries in Excel against ...

Corrupted Personal.xls file in Excel 97 ??
Oops! This is a correction to the subject of my previous posting. Please excuse me. =========================================================== I am using Excel 97. As of a few days ago, every time I start Excel I get a dialogue box that says "Personal.xls is being modified by Milt" (that's me) and giving me three choices: (1) Open the Personal.xls as a Read Only file, (2) Notify me when it becomes available, (3) Cancel. No matter which choice I take, the same dialogue box appears the next time I start Excel. The only way I have found to stop getting that dialogue b...

Rank query help
Below is the SQL for a query that I have. Below the SQL is the results. What I need to do is rank the score by each name in descending order. When the name changes I need the rank to start at 1 again. Thanks in advance for your help. I am stuck. SELECT [Plant].Name, [Plant].DefectCode, [Plant].Score FROM [Plant] GROUP BY [Plant].Name, [Plant].DefectCode, [Plant].Score ORDER BY [Plant].Name, [Plant].Score DESC; Name DefectCode Score Plant A 2000 Widget 100 8 Plant A 2000 Widget 105 5 Plant A 2000 Widget 106 4 Plant A 2001 Widget 105 4 Plant A 2001 Widget 102 3 Plant A 2001 wid...

nested query hell
i'm trying to use a nested query (previous post under counting problem but no solution found). Here's what I've done so far: SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre, Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID FROM dbo_Appt GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID; With this I'm getting a count of apptID for each distinct date. What I want though is a count with different where clauses for each distinct apptdate For grouping [Pre] a count of apptID with an apptdate...

Excel Date Format of MMM/YY
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I enter a date in the format of MMM/YY it saves ok but if you then look in tools and calculator I have found that it creates a formula of 2004 and when you copy this over to a Windows based PC it displays incorrect dates. How do I turn off this apparent formula creation. I live in UK and have settings to UK Don't let the info in the Calculator mislead you. It's simply confirming that the workbook you're in is employing the 1904 Date System which is the default in Mac Excel. Dates in Mac Exce...

Query Criteria 05-24-07
Finding it hard to get my head round this so help needed! I have a query based on form fields. There is date range field (always selected) and 2 other optional criteria needing 4 WHERE/OR statements to get the required results. I want to add another optional criteria field which i think will leave me with 11 WHERE/OR statements and a big headache! Is there a simple way around this? ...

2003 Excel Macros Run Slowly
I upgraded to Office 2003 and have a macro in one of my spreadsheets. When I execute this macro it runs very, very slowly i.e. mulitple minutes vs 2 sec on Office 2000. Is there anything I should be doing to improve the speed? Regards, Ed Close excel, clean up your windows temp folder. set calculation to manual, run your code, reset it to what it was. Turn screenupdating off when you start and on when you finish. turn the display of pagebreaks off when you start. Lots of my macros have this at the top: Dim CalcMode As Long CalcMode = Application.Calculation Applicatio...

Vlookups to Excel Version 3 files.
I use a system that on a daily basis creates an Excel version 3 file containing cost centers and their budgets. When I do vlookups from other Excel 97 files to this file it takes a long time for the sheet to calculate. If I save the Excel V3 file as Excel 97 its OK, and if the Excel V3 file is open before the 97 file its OK. I don't want to have to constantly save the V3 file as 97 (I may not be around to do that always) Does anyone know how to resolve this. Thanks Naz ...

query question 12-05-07
Hi all, I need some help with this one not sure if it even possible. I have a table that has identifier number, transaction number, status, and comments field. In the 1st query I look in the comments field to find a specific date (using Like). That query shows me all that is Postperiod. What I need is for the preperiod records to show up in a query by themselves. I have tried to use NOT Like but this doesn't provide me with correct results. Anyone have any idea how I can do this? thanks in advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acces...

Could Excel be used as accounting software?
If so, any tutorials. (I don't want to learn or bky quick books) I jus want to keep tabs on business expenses and I would rather not do it o paper -- Freddy_Kruge ----------------------------------------------------------------------- Freddy_Kruger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2962 View this thread: http://www.excelforum.com/showthread.php?threadid=52382 Theoretically yes but practically no. There a lot of other softwares particularly databases that could do a lot than Excel. These include SAP,Oracle etc., Are you saying that excel wou...

Append Query 10-29-07
Does an append query need exclusive rights to the destination table to write to it, or can the destination table be in use? On Mon, 29 Oct 2007 12:50:02 -0700, Ryan Tisserand <RyanTisserand@discussions.microsoft.com> wrote: >Does an append query need exclusive rights to the destination table to write >to it, or can the destination table be in use? It can be in use. John W. Vinson [MVP] Thank you for your response, you saved me alot of testing time. "John W. Vinson" wrote: > On Mon, 29 Oct 2007 12:50:02 -0700, Ryan Tisserand > <RyanTisserand@...

Import External Date/Web query problem
I have a spreadsheet that does a Data/Import External Data/Web Query to get data from my Yahoo stock portfolio. It has worked for years. On January 31, Yahoo made a change to their web page and my query stopped working with the following message: "Unable to open http://finance.yahoo.com/p?v&k=pf_1. The internet site reports that a connection was established but the data is not available." I have redone the query with the same results. Interestingly, I can query other tables on Yahoo, just nothing on their finance page. I tried MSN and also got the same results from ...

Inserting Columns in Excel
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I have just switched from a Dell to an Imac. I have such a basic question. I cannot insert rows or columns. It says something like can't shift data off of page. Any ideas? On 7/8/08 10:47 PM, in article 59b539bd.-1@webcrossing.caR9absDaxw, "Ex_Windows_User@officeformac.com" <Ex_Windows_User@officeformac.com> wrote: > Version: 2008 > Operating System: Mac OS X 10.5 (Leopard) > Processor: intel > > I have just switched from a Dell to an Imac. I have such a basic question. I > cannot...

Excel front ends to Access Database
We are in the process of converting all our old 95 databases and I have one with excel front ends. Upon converting the DB to Access 2000 and reentering the Excel front end the DB is unrecognized. Any suggestions? Thanks! ...

how to create button commande to refresh data in query in excel 2.
I have a excel spreedsheet that contain external data. I would like to put a button in the excel sheet to update the sheet without doing right click and refresh. My user here are very dummies. Jean Francois If this is external data then bringing it in should launch the external data toolbar. They will only need to click the exclamation (!) mark. Seems like re-inventing the wheel. If you must then put a button on the worksheet and assign it to this macro Sub refreshdata() Dim wks As Worksheet Dim qryTab As QueryTable Set wks = ActiveSheet For Each qryTab In wks.QueryTables qryTab.Refr...

I need complete idiot proof excel setup database help. please
I need to learn all about Excel and how to make a database.. I have read and read and done practise tutorials but I just cannot grasp the concept, it completely goes over my head everytime. I am quick to learn when I am being shown or told exactly what to do, can anyone help me learn all about Excel.. ? The first thing you need to know is that Excel is not a database application, and while it is commonly used for databases, it doesn't do them well. Expecting a database developed in XL to be "complete idiot proof" is setting yourself up for disaster. Without knowing any...

Menu Maker and Excel 2007
Hi all I see that John Walkenbach's Menu Maker doesn't require much modification to work with the QAT. Is there a similar method for building a custom Ribbon tab or group? Thanks in advance Paul Martin Melbourne, Australia Hi, Check out Mike's menu builder for 2007. http://www.datapigtechnologies.com/freeware.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Paul Martin" <melbournefilm@gmail.com> wrote in message news:40c5f73c-9e78-43f5-b4fd-d3f84097837c@f20g2000vbl.googlegroups.com... > Hi all > &...

How to tell Excel to recalc, unconditionally, now?
It's easy to invalidate an Excel 2002 workbook, putting it into a recalc-required state, yet Excel fails to do it. For example, I have a worksheet with thousands of UDF calls (=MyUDF(Param)). If I make an edit whose only impact is to change the value(s) of the UDF params, that kind of edit seems too oblique for Excel and it won't recalc. (Or if calc is set to Manual, the Calculate flag doesn't appear, so pressing F9 does nothing. Nor does toggling Manual/Automatic calc. Even if you set calc to Automatic, save and reopen the WB, it stays UNCALC'ed.) Yes, I know I could, say: ...

Convert list of pairs [a(i),b(i)] to table that shows # of occurrences of each pair
Given something like this: apple,jan apple,feb apple,mar apple,jan apple,feb orange,jan orange,feb orange,jan orange,feb banana,jan banana,feb banana,mar banana,jan banana,feb I'd like to create a table that has three rows (the fruits) and three columns (the months). At the intersection of each row and column I'd like the number of occurrences of that particular fruit-month pair. Thanks for your time. Tom Z. Tom, Assume your data is in the range A1:A14, and you have a grid starting in cell C1 that looks like: C D E F 1 jan feb mar 2 apple 3 orange 4 banana ...

Excel 2007 sorting bug?
If an Excel 2007 sheet (#1) has formula referenced cells to another sheet (#2), and if the column in sheet (#2) containing the referenced cells is then A-Z sorted on the value then the original references in sheet #1 get lost as they are now pointing to different cells. Is this an Excel 2007 bug? Why after the sort does the relative position not move so keeping the correct references?. I have not used $ in the cell address. Beemer No, this is not a bug. If you have cells on Sheet1 pointing to other cells on Sheet2 with a direct reference like =Sheet2!B1, then if you change the c...

List a group of repeated data from sheet1 to sheet2 but not repeat
-- thadi I have one excel sheet1 look like the following. UserName Location Division Software Name Version Maria.Sigmund Parkade Probation Adobe Reader 7.0 Maria.Sigmund Parkade Probation Lotus Notes 6.5.5 Eric.Bell Parkade IT WebFldrs 1.0 Eric.Bell Parkade IT Adobe Reader 7.0 Eric.Bell Parkade IT Symantec AntiVirus 8.1 I want to modify this sheet1 to sheet 2 ...

Passing Query Parameters from BP to a URL
Hey Eighties Fans: After constructing a query in Business Portal, is it possible to pass a parameter from that query into a URL? Doing so would be similar to the SmartList Builder feature where you can create a web site (URL) "Go-To" link and pass a parameter within that link that literally becomes part of the web site address that appears in your web browser. Thanks! childofthe1980s ...

Having some problems saving excel files
I had a Excel file that was worked on using a Office XP machine, and know it is back on my Mac. Know everytime I save I keep on getting this message everytime using Office 98 for Mac. "This file was created using a later version of Microsoft Excel. If you save this file using Microsoft excel 98, information created with features in the later version may be lost." I have tried the following. 1) Saving as a new file and renaming it 2) Saving as Excel 5.x, and then back to Excel 98 3) deleting the Cell comments that I created in Excel XP, and redoing them in Excel 98 Thank...