Help summarizing data needed

[Jon]

worksheet contains holiday info and looks like...

Date Name1 Name2 Name3
1/1/05
2/1/05
..
..
31/12/05

at the intersection of name & date a number 1 indicates a holiday

I'm trying to create a summary/pivot table (ultimately for use elsewhere in
the workbook) which will show...

Name,Week 1,Week 2.....
Name1,sum of hol,
Name2
Name3
....

I can't TRANSPOSE the data initially as the max no. of columns is 256 and
there are 365+ days in a year

Any suggestions much appreciated.

 

[David McRitchie]

Hi Jon,
I can't follow your example -- it looks incomplete, but you seem to know
exactly what you want, Pivot Tables, and you seem to know your data,
so you can probably look at some web pages concerning Pivot Tables
and solve your problem.

look for Pivot Table entries in index
http://www.contextures.com/tiptech.html

look for Pivot Table entries in index -- these are flash presentations ***
http://www.datapigtechnologies.com/ExcelMain.htm

 

[Jon]

Hi David

Sorry the example's a bit vague (can't really show column alignment properly
with this font). I'll show it comma-delimited.

Current data...

Date,Fred,Burt,Mary
1/1/05,1,0,0 (1 indicates holiday taken)
2/1/5,0,0,0
..
..
31/12/05,1,1,1

Required data...

Name,1,2,3...up to 52 or 53 (this is the week number)
Fred,#days holiday in week 1,#days holiday in week 2...
Burt,#days holiday in week 1,#days holiday in week 2...
Mary,#days holiday in week 1,#days holiday in week 2...

I'm guessing I'll only be able to do this in VB as I need to convert dates
to week numbers before summing, and the number of names is volatile (plus the
inability to TRANSPOSE the data due to the number of columns which would be
required)

Cheers

Jon

Hi Jon,
I can't follow your example -- it looks incomplete, but you seem to know
exactly what you want, Pivot Tables, and you seem to know your data,
so you can probably look at some web pages concerning Pivot Tables
and solve your problem.

look for Pivot Table entries in index
http://www.contextures.com/tiptech.html

look for Pivot Table entries in index -- these are flash presentations ***
http://www.datapigtechnologies.com/ExcelMain.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

worksheet contains holiday info and looks like...

Date Name1 Name2 Name3
1/1/05
2/1/05
.
.
31/12/05

at the intersection of name & date a number 1 indicates a holiday

I'm trying to create a summary/pivot table (ultimately for use elsewhere in
the workbook) which will show...

Name,Week 1,Week 2.....
Name1,sum of hol,
Name2
Name3
...

I can't TRANSPOSE the data initially as the max no. of columns is 256 and
there are 365+ days in a year

Any suggestions much appreciated.

 

[David McRitchie]

Firefox users may have a problem viewing some versions of Flash presentation including these, unless one of the options ("obj-tabs")
is turned off in AdBlock.

reference: https://addons.mozilla.org/extensio...irefox&numpg=25&id=10&page=comments&pageid=22

 

[David McRitchie]

Help with finding the week number can be found on Chip Pearson's site.
http://www.cpearson.com/excel/weeknum.htm

Hi David

Sorry the example's a bit vague (can't really show column alignment properly
with this font). I'll show it comma-delimited.

Current data...

Date,Fred,Burt,Mary
1/1/05,1,0,0 (1 indicates holiday taken)
2/1/5,0,0,0
.
.
31/12/05,1,1,1

Required data...

Name,1,2,3...up to 52 or 53 (this is the week number)
Fred,#days holiday in week 1,#days holiday in week 2...
Burt,#days holiday in week 1,#days holiday in week 2...
Mary,#days holiday in week 1,#days holiday in week 2...

I'm guessing I'll only be able to do this in VB as I need to convert dates
to week numbers before summing, and the number of names is volatile (plus the
inability to TRANSPOSE the data due to the number of columns which would be
required)

Cheers

Jon

Hi Jon,
I can't follow your example -- it looks incomplete, but you seem to know
exactly what you want, Pivot Tables, and you seem to know your data,
so you can probably look at some web pages concerning Pivot Tables
and solve your problem.

look for Pivot Table entries in index
http://www.contextures.com/tiptech.html

look for Pivot Table entries in index -- these are flash presentations ***
http://www.datapigtechnologies.com/ExcelMain.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

worksheet contains holiday info and looks like...

Date Name1 Name2 Name3
1/1/05
2/1/05
.
.
31/12/05

at the intersection of name & date a number 1 indicates a holiday

I'm trying to create a summary/pivot table (ultimately for use elsewhere in
the workbook) which will show...

Name,Week 1,Week 2.....
Name1,sum of hol,
Name2
Name3
...

I can't TRANSPOSE the data initially as the max no. of columns is 256 and
there are 365+ days in a year

Any suggestions much appreciated.