|
Excel summary table creation
Hi,
Hope can anyone please help me on this predicament: I have a raw data table showing Names versus Hours Worked as shown Name Jan 1, Jan 2, Jan 3, Jan 4, Jan 5 €¦. Bob 10hrs, 8hrs, 2hrs, 0hr, 1hr Steve 8hrs, 8hrs, 8hrs, 2hrs, 2hrs John 6hrs, 5hrs, 3hrs, 1hr, 0hr The dates are from Jan 1 to Dec 31 (whole year) so I just cut it for illustration purposes. My question is how to easily create a table to group the work summaries as per the dates I want (actually based on our company fiscal calendar range dates) or as per the date range I want€¦... The final table should be looking like this: Name Jan 1 ~ 15, Jan 16 ~ 20, Jan 21 ~ 30,etc Bob ?? Hrs, ?? Hrs, ?? Hrs, Steve ?? Hrs, ?? Hrs, ?? Hrs, John ?? Hrs, ?? Hrs, ?? Hrs, The date range can vary as per user's requirement, say they want work summaries every 3 wks, forthnightly, etc Please help! Thanks! |
Excel summary table creation
Your data structure is not friendly for this. There are two major issues
1) You have data going across 2) You have text "hrs" in each data cell and this will not naturally calculate or group. I would start by re-arranging the data and setting it out with a start and an end time and then calculate in Excel the total, e.g. columns as follows... Date Name In Out Total and data like this in the columns, running down the rows with each entry. 01/01/2008 Nick Hodge 08:00 17:00 9:00 You may need to take account of lunch etc, so just make them another column. You can then summarise using excel functions, Pivot tables, etc. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk "Jrufin" wrote in message ... Hi, Hope can anyone please help me on this predicament: I have a raw data table showing Names versus Hours Worked as shown Name Jan 1, Jan 2, Jan 3, Jan 4, Jan 5 €¦. Bob 10hrs, 8hrs, 2hrs, 0hr, 1hr Steve 8hrs, 8hrs, 8hrs, 2hrs, 2hrs John 6hrs, 5hrs, 3hrs, 1hr, 0hr The dates are from Jan 1 to Dec 31 (whole year) so I just cut it for illustration purposes. My question is how to easily create a table to group the work summaries as per the dates I want (actually based on our company fiscal calendar range dates) or as per the date range I want€¦... The final table should be looking like this: Name Jan 1 ~ 15, Jan 16 ~ 20, Jan 21 ~ 30,etc Bob ?? Hrs, ?? Hrs, ?? Hrs, Steve ?? Hrs, ?? Hrs, ?? Hrs, John ?? Hrs, ?? Hrs, ?? Hrs, The date range can vary as per user's requirement, say they want work summaries every 3 wks, forthnightly, etc Please help! Thanks! |
Excel summary table creation
I would recommend structuring your data in a slightly different way.
Instead of having a column for each day and a row for each Name, have a row for each Day and Name as follows: Date Name Hours GroupCode Jan.1.2007 Bob 10 1 Jan.18.2007 Bob 8 2 Jan.1.2007 Steve 8 1 Etc. The GroupCode column will be a formula (VLOOKUP(DAY(Date),GroupingTable,2)) that matches a Grouping code to each day in the month. For this you have to define a table with 31 entries, where the first column has 1 in the first row and 31 in the 31st row. The second column will have a group code which indicates to which group of the month the day belongs (e.g. G1 = 1-7, G2=8-14, G3=15-21 etc). The Group codes are defined and entered by you. This will enable you to define groups by which you want to aggregate the hours. Once you have done that, create a Pivot table based on your 4 columns table and then group the Names by Group and Month. Regards Eli Prism - Business Intelligence for Everybody www.sisense.com "Jrufin" wrote in message ... Hi, Hope can anyone please help me on this predicament: I have a raw data table showing Names versus Hours Worked as shown Name Jan 1, Jan 2, Jan 3, Jan 4, Jan 5 .. Bob 10hrs, 8hrs, 2hrs, 0hr, 1hr Steve 8hrs, 8hrs, 8hrs, 2hrs, 2hrs John 6hrs, 5hrs, 3hrs, 1hr, 0hr The dates are from Jan 1 to Dec 31 (whole year) so I just cut it for illustration purposes. My question is how to easily create a table to group the work summaries as per the dates I want (actually based on our company fiscal calendar range dates) or as per the date range I want.... The final table should be looking like this: Name Jan 1 ~ 15, Jan 16 ~ 20, Jan 21 ~ 30,etc Bob ?? Hrs, ?? Hrs, ?? Hrs, Steve ?? Hrs, ?? Hrs, ?? Hrs, John ?? Hrs, ?? Hrs, ?? Hrs, The date range can vary as per user's requirement, say they want work summaries every 3 wks, forthnightly, etc Please help! Thanks! |
Excel summary table creation
|
Excel summary table creation
Hi Eli,
Thanks, i'll try it... "Eli" wrote: I would recommend structuring your data in a slightly different way. Instead of having a column for each day and a row for each Name, have a row for each Day and Name as follows: Date Name Hours GroupCode Jan.1.2007 Bob 10 1 Jan.18.2007 Bob 8 2 Jan.1.2007 Steve 8 1 Etc. The GroupCode column will be a formula (VLOOKUP(DAY(Date),GroupingTable,2)) that matches a Grouping code to each day in the month. For this you have to define a table with 31 entries, where the first column has 1 in the first row and 31 in the 31st row. The second column will have a group code which indicates to which group of the month the day belongs (e.g. G1 = 1-7, G2=8-14, G3=15-21 etc). The Group codes are defined and entered by you. This will enable you to define groups by which you want to aggregate the hours. Once you have done that, create a Pivot table based on your 4 columns table and then group the Names by Group and Month. Regards Eli Prism - Business Intelligence for Everybody www.sisense.com "Jrufin" wrote in message ... Hi, Hope can anyone please help me on this predicament: I have a raw data table showing Names versus Hours Worked as shown Name Jan 1, Jan 2, Jan 3, Jan 4, Jan 5 .. Bob 10hrs, 8hrs, 2hrs, 0hr, 1hr Steve 8hrs, 8hrs, 8hrs, 2hrs, 2hrs John 6hrs, 5hrs, 3hrs, 1hr, 0hr The dates are from Jan 1 to Dec 31 (whole year) so I just cut it for illustration purposes. My question is how to easily create a table to group the work summaries as per the dates I want (actually based on our company fiscal calendar range dates) or as per the date range I want.... The final table should be looking like this: Name Jan 1 ~ 15, Jan 16 ~ 20, Jan 21 ~ 30,etc Bob ?? Hrs, ?? Hrs, ?? Hrs, Steve ?? Hrs, ?? Hrs, ?? Hrs, John ?? Hrs, ?? Hrs, ?? Hrs, The date range can vary as per user's requirement, say they want work summaries every 3 wks, forthnightly, etc Please help! Thanks! |
Excel summary table creation
Hi Nick,
Thanks, I'll give it a shot... "Nick Hodge" wrote: Your data structure is not friendly for this. There are two major issues 1) You have data going across 2) You have text "hrs" in each data cell and this will not naturally calculate or group. I would start by re-arranging the data and setting it out with a start and an end time and then calculate in Excel the total, e.g. columns as follows... Date Name In Out Total and data like this in the columns, running down the rows with each entry. 01/01/2008 Nick Hodge 08:00 17:00 9:00 You may need to take account of lunch etc, so just make them another column. You can then summarise using excel functions, Pivot tables, etc. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk "Jrufin" wrote in message ... Hi, Hope can anyone please help me on this predicament: I have a raw data table showing Names versus Hours Worked as shown Name Jan 1, Jan 2, Jan 3, Jan 4, Jan 5 €¦. Bob 10hrs, 8hrs, 2hrs, 0hr, 1hr Steve 8hrs, 8hrs, 8hrs, 2hrs, 2hrs John 6hrs, 5hrs, 3hrs, 1hr, 0hr The dates are from Jan 1 to Dec 31 (whole year) so I just cut it for illustration purposes. My question is how to easily create a table to group the work summaries as per the dates I want (actually based on our company fiscal calendar range dates) or as per the date range I want€¦... The final table should be looking like this: Name Jan 1 ~ 15, Jan 16 ~ 20, Jan 21 ~ 30,etc Bob ?? Hrs, ?? Hrs, ?? Hrs, Steve ?? Hrs, ?? Hrs, ?? Hrs, John ?? Hrs, ?? Hrs, ?? Hrs, The date range can vary as per user's requirement, say they want work summaries every 3 wks, forthnightly, etc Please help! Thanks! |
Excel summary table creation
Hi Herbert,
That is AWESOME!!! Great! Thanks! "Herbert Seidenberg" wrote: No rearrangement, no formulas http://www.freefilehosting.net/download/3cjd8 |
Excel summary table creation
Hi Herbert,
I became ecstatic/excite when I saw your file. But when I actually tried doing it I have some problems/inquiry: 1. How to do the "Grouping" as shown in your worksheets PT2 and PT3 ? 2. If I want to group in a customized manner say, I want to group it with Variable "Number of Days" can it be done? Because our company fiscal calendar is structured in a strange way for example: January will have a date range from "Jan 7 to Jan 27", February will have a date range from "Jan 28 to Feb 17", it short the number of weeks per month is varying sometimes it is 3wks per month, 4 wks per month or even 5wks per month! So I need to have a freedom to group it as per the date range I specify. 3. Do I really need to select "Multiple consolidation ranges" as shown in worksheet PT1? What if I select "Microsoft Office Excel list or database" ? Please help! Thanks! "Herbert Seidenberg" wrote: No rearrangement, no formulas http://www.freefilehosting.net/download/3cjd8 |
Excel summary table creation
|
Excel summary table creation
Cool! Thanks again!
"Herbert Seidenberg" wrote: Answers and solutions he http://www.freefilehosting.net/download/3ckl3 |
| All times are GMT +1. The time now is 04:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com