Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]() All, I am trying to perform the following tasks within Excel. Task 1 I have three columns, Date, Game, Score. Is there a way I can request that Excel only display rows that fall withinn a cetain date range? Task 2 Once I have the refined data I would like to consolidate it. I.e Several games might have been played on the same date I would like to just have one row per date per game with the score totalled. So if two games of bowling were played on 21/07/05 with score of 163 and 187. The row would read, ----A------------B-----------C 21/07/05---Bowling-----350 You get the idea! ;) Thanks in advance. AU -- AcesUp ------------------------------------------------------------------------ AcesUp's Profile: http://www.excelforum.com/member.php...o&userid=26519 View this thread: http://www.excelforum.com/showthread...hreadid=397877 |
#2
![]() |
|||
|
|||
![]()
See help on subtotals. This should answer your questions
HTH "AcesUp" wrote: All, I am trying to perform the following tasks within Excel. Task 1 I have three columns, Date, Game, Score. Is there a way I can request that Excel only display rows that fall withinn a cetain date range? Task 2 Once I have the refined data I would like to consolidate it. I.e Several games might have been played on the same date I would like to just have one row per date per game with the score totalled. So if two games of bowling were played on 21/07/05 with score of 163 and 187. The row would read, ----A------------B-----------C 21/07/05---Bowling-----350 You get the idea! ;) Thanks in advance. AU -- AcesUp ------------------------------------------------------------------------ AcesUp's Profile: http://www.excelforum.com/member.php...o&userid=26519 View this thread: http://www.excelforum.com/showthread...hreadid=397877 |
#3
![]() |
|||
|
|||
![]()
Task 1: Depending on the complexity of the criteria you want to use for
dates, you could use either Data | AutoFilter or Data | Advanced Filter. Be sure that the columns that contain your dates and scores are headed by labels such as "DATE" and "SCORE". Task 2: Create a pivot table (Data | PivotTable and PivotChart Report) with Date as the Row field and Score as the Data field. If your scores are all numeric values, the Data field defaults to Sum. However, you might not want to point the pivot table at your consolidated data if you used AutoFilter to consolidate it -- all that AutoFilter does is hide rows, but a pivot table might pick up values in those hidden rows. Safer to use Advanced Filter and opt to copy the results to another location. Or you can point the pivot table at your original data and then use the Field Settings in the pivot table itself to suppress the dates you don't want. (BTW, the term "parameter query" is normally used to refer to an SQL query whose results change according to criteria supplied by its users; the criterion is in that context called a parameter.) C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "AcesUp" wrote in message ... All, I am trying to perform the following tasks within Excel. Task 1 I have three columns, Date, Game, Score. Is there a way I can request that Excel only display rows that fall withinn a cetain date range? Task 2 Once I have the refined data I would like to consolidate it. I.e Several games might have been played on the same date I would like to just have one row per date per game with the score totalled. So if two games of bowling were played on 21/07/05 with score of 163 and 187. The row would read, ----A------------B-----------C 21/07/05---Bowling-----350 You get the idea! ;) Thanks in advance. AU -- AcesUp ------------------------------------------------------------------------ AcesUp's Profile: http://www.excelforum.com/member.php...o&userid=26519 View this thread: http://www.excelforum.com/showthread...hreadid=397877 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Linking parameter query from Access to pivot table in Excel | Excel Discussion (Misc queries) | |||
MS Query not installed for New Database Query | Excel Discussion (Misc queries) | |||
Web Query Help... | Excel Discussion (Misc queries) | |||
"Query cannot be edited by the Query Wizard" | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |