| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help summarizing data needed
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help summarizing data needed
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 "Jon" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help summarizing data needed
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 "David McRitchie" wrote: 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 "Jon" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help summarizing data needed
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/extension...ents&pageid=22 "David McRitchie" wrote in message news:% 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help summarizing data needed
Help with finding the week number can be found on Chip Pearson's site.
http://www.cpearson.com/excel/weeknum.htm "Jon" wrote in message ... 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 "David McRitchie" wrote: 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 "Jon" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I import formmail data to a custom excel template? | Excel Worksheet Functions | |||
| Help in getting external data needed please | Excel Worksheet Functions | |||
| Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) | |||
| Line Graph Data Recognition | Charts and Charting in Excel | |||
| Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
Linear Mode
