Excel summary table creation

[Jrufin]

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!

 

[Nick Hodge]

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
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Eli]

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

 

[Herbert Seidenberg]

No rearrangement, no formulas
http://www.freefilehosting.net/download/3cjd8

 

[Jrufin]

Hi Eli,
Thanks, i'll try it...

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]

Hi Nick,
Thanks, I'll give it a shot...

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
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Jrufin]

Hi Herbert,
That is AWESOME!!! Great!

Thanks!

 

[Jrufin]

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]

Answers and solutions here:
http://www.freefilehosting.net/download/3ckl3

 

[Jrufin]

Cool! Thanks again!