| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create summarized date driven data
How do I create a summarize report of data that is date driven? For example,
I have a table of employee job history in which employees held the same job for several years. I want to create a summarized report that will simply give me the start date and end date for the employee at each change in job. How do I do that? -- JRussell |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
Hi, would be helpful providing us with the structure of ur data, otherwise we
might just guess. So, place here sample of source data and desired result. I go sleep now but somebody else will help you soon or later. "JRussell" wrote: How do I create a summarize report of data that is date driven? For example, I have a table of employee job history in which employees held the same job for several years. I want to create a summarized report that will simply give me the start date and end date for the employee at each change in job. How do I do that? -- JRussell |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
Okay, Here is a sample of my data.
EMPLOYEE EFFECT-DATE END-DATE Job-Bonus Code 75013 6/27/2005 6/30/2005 BY-MIY-15 75013 7/1/2005 12/31/2005 BY-MIY-15 75013 1/1/2006 6/30/2006 BY-MIY-15 75013 7/1/2006 12/31/2006 BY-MIY-15 75013 1/1/2007 6/30/2007 BY-MIY-15 75013 7/1/2007 12/31/2007 BY-MIY-15 75013 1/1/2008 3/31/2008 BY-MIY-15 75013 4/1/2008 6/30/2008 BY-MIY-15 75013 7/1/2008 BY-MIY-15 750753 5/1/2004 5/15/2004 BY-L0A 750753 5/16/2004 6/30/2004 BY-L0A 750753 7/1/2004 12/31/2004 BY-L0A 750753 1/1/2005 6/30/2005 BY-L0A 750753 7/1/2005 6/30/2006 BY-L0A 750753 7/1/2006 12/31/2006 BY-L0A 750753 1/1/2007 1/7/2007 BY-L0A 750753 1/8/2007 6/30/2007 BY-L0A 750753 7/1/2007 12/31/2007 BY-L0A 750753 1/1/2008 6/30/2008 BY-L0A 750753 7/1/2008 BY-L0A The desired result is EMPLOYEE EFFECT-DATE END-DATE Job-Bonus Code 75013 6/27/2005 BY-MIY-15 750753 5/1/2004 BY-L0A -- JRussell "Alojz" wrote: Hi, would be helpful providing us with the structure of ur data, otherwise we might just guess. So, place here sample of source data and desired result. I go sleep now but somebody else will help you soon or later. "JRussell" wrote: How do I create a summarize report of data that is date driven? For example, I have a table of employee job history in which employees held the same job for several years. I want to create a summarized report that will simply give me the start date and end date for the employee at each change in job. How do I do that? -- JRussell |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
Here we go. Assuming ur data are in A1:D21 inluding header on row 1.
First, create list of employee numbers starting on A26 down to Axxx. Formula for effect-date: =INDEX($B$2:$B$21,MATCH(A26,$A$2:$A$21,0),0) Formula for end-date: =IF(INDEX($C$2:$C$21,MATCH(A26,$A$2:$A$21,1),0)=0; "";INDEX($C$2:$C$21,MATCH(A26,$A$2:$A$21,1),0) ) Formula for Job-Bonus Code: =INDEX($D$2:$D$21,MATCH(A26,$A$2:$A$21,0),0) Copy all three formulas down to get result for each head. HTH "JRussell" wrote: Okay, Here is a sample of my data. EMPLOYEE EFFECT-DATE END-DATE Job-Bonus Code 75013 6/27/2005 6/30/2005 BY-MIY-15 75013 7/1/2005 12/31/2005 BY-MIY-15 75013 1/1/2006 6/30/2006 BY-MIY-15 75013 7/1/2006 12/31/2006 BY-MIY-15 75013 1/1/2007 6/30/2007 BY-MIY-15 75013 7/1/2007 12/31/2007 BY-MIY-15 75013 1/1/2008 3/31/2008 BY-MIY-15 75013 4/1/2008 6/30/2008 BY-MIY-15 75013 7/1/2008 BY-MIY-15 750753 5/1/2004 5/15/2004 BY-L0A 750753 5/16/2004 6/30/2004 BY-L0A 750753 7/1/2004 12/31/2004 BY-L0A 750753 1/1/2005 6/30/2005 BY-L0A 750753 7/1/2005 6/30/2006 BY-L0A 750753 7/1/2006 12/31/2006 BY-L0A 750753 1/1/2007 1/7/2007 BY-L0A 750753 1/8/2007 6/30/2007 BY-L0A 750753 7/1/2007 12/31/2007 BY-L0A 750753 1/1/2008 6/30/2008 BY-L0A 750753 7/1/2008 BY-L0A The desired result is EMPLOYEE EFFECT-DATE END-DATE Job-Bonus Code 75013 6/27/2005 BY-MIY-15 750753 5/1/2004 BY-L0A -- JRussell "Alojz" wrote: Hi, would be helpful providing us with the structure of ur data, otherwise we might just guess. So, place here sample of source data and desired result. I go sleep now but somebody else will help you soon or later. "JRussell" wrote: How do I create a summarize report of data that is date driven? For example, I have a table of employee job history in which employees held the same job for several years. I want to create a summarized report that will simply give me the start date and end date for the employee at each change in job. How do I do that? -- JRussell |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
Hi,
You can do this with a PivotTable inwhich you put the Employee in the outer Row field (to the left), and the Job-Bonus-Code as a Row field to the right. Put the Start date in the Data area and change its calculation to Min, put the End date into the Data area and change its calculation to Max. Format both data field to date. Drag the Data button from the row area to the Column area. Turn off Grand Totals. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JRussell" wrote: Okay, Here is a sample of my data. EMPLOYEE EFFECT-DATE END-DATE Job-Bonus Code 75013 6/27/2005 6/30/2005 BY-MIY-15 75013 7/1/2005 12/31/2005 BY-MIY-15 75013 1/1/2006 6/30/2006 BY-MIY-15 75013 7/1/2006 12/31/2006 BY-MIY-15 75013 1/1/2007 6/30/2007 BY-MIY-15 75013 7/1/2007 12/31/2007 BY-MIY-15 75013 1/1/2008 3/31/2008 BY-MIY-15 75013 4/1/2008 6/30/2008 BY-MIY-15 75013 7/1/2008 BY-MIY-15 750753 5/1/2004 5/15/2004 BY-L0A 750753 5/16/2004 6/30/2004 BY-L0A 750753 7/1/2004 12/31/2004 BY-L0A 750753 1/1/2005 6/30/2005 BY-L0A 750753 7/1/2005 6/30/2006 BY-L0A 750753 7/1/2006 12/31/2006 BY-L0A 750753 1/1/2007 1/7/2007 BY-L0A 750753 1/8/2007 6/30/2007 BY-L0A 750753 7/1/2007 12/31/2007 BY-L0A 750753 1/1/2008 6/30/2008 BY-L0A 750753 7/1/2008 BY-L0A The desired result is EMPLOYEE EFFECT-DATE END-DATE Job-Bonus Code 75013 6/27/2005 BY-MIY-15 750753 5/1/2004 BY-L0A -- JRussell "Alojz" wrote: Hi, would be helpful providing us with the structure of ur data, otherwise we might just guess. So, place here sample of source data and desired result. I go sleep now but somebody else will help you soon or later. "JRussell" wrote: How do I create a summarize report of data that is date driven? For example, I have a table of employee job history in which employees held the same job for several years. I want to create a summarized report that will simply give me the start date and end date for the employee at each change in job. How do I do that? -- JRussell |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
Excel 2007
No formulas or Pivot Table needed, just a few clicks (for the data posted) http://www.mediafire.com/file/njzmknzjt33/03_02_09.xlsx |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
Herbert, how did u get rid of EndDate data? When I try advanced filtering
with removing duplicates it still shows 15-May-2004 next to 1-May-2004 and 30-Jun-2005 next to 27-Jun-2005. What means ur comment in J3 "Hide data with conditional formatting or delete column" ? Did u remove EndDate manually? What if corresponding EndDate for particular employee is on different line then EffectDate? Thx for answer in advance. Alojz "Herbert Seidenberg" wrote: Excel 2007 No formulas or Pivot Table needed, just a few clicks (for the data posted) http://www.mediafire.com/file/njzmknzjt33/03_02_09.xlsx |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
Herbert,
What are the steps to access remove duplicates. I'm unable to find it. Thanks, -- JRussell "Herbert Seidenberg" wrote: Excel 2007 No formulas or Pivot Table needed, just a few clicks (for the data posted) http://www.mediafire.com/file/njzmknzjt33/03_02_09.xlsx |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
By the way the pivot table will look like this (but formatted):
Data EMPLOYEE Job-Bonus Eff Date End Date 75013 BY-MIY-15 6/27/2005 6/30/2008 750753 BY-L0A 5/1/2004 6/30/2008 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Alojz" wrote: Hi, would be helpful providing us with the structure of ur data, otherwise we might just guess. So, place here sample of source data and desired result. I go sleep now but somebody else will help you soon or later. "JRussell" wrote: How do I create a summarize report of data that is date driven? For example, I have a table of employee job history in which employees held the same job for several years. I want to create a summarized report that will simply give me the start date and end date for the employee at each change in job. How do I do that? -- JRussell |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Create summarized date driven data
Hi, Shane, nice. U like pivot tables for sure. One question: Can u make ur
table to replace end dates with blank cells as they should be blank when I compare to desired result of OP? "Shane Devenshire" wrote: By the way the pivot table will look like this (but formatted): Data EMPLOYEE Job-Bonus Eff Date End Date 75013 BY-MIY-15 6/27/2005 6/30/2008 750753 BY-L0A 5/1/2004 6/30/2008 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Alojz" wrote: Hi, would be helpful providing us with the structure of ur data, otherwise we might just guess. So, place here sample of source data and desired result. I go sleep now but somebody else will help you soon or later. "JRussell" wrote: How do I create a summarize report of data that is date driven? For example, I have a table of employee job history in which employees held the same job for several years. I want to create a summarized report that will simply give me the start date and end date for the employee at each change in job. How do I do that? -- JRussell |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can the data validation list range of one cell be driven by theco | Excel Discussion (Misc queries) | |||
| Create summarized list from larger list | Excel Discussion (Misc queries) | |||
| How can I create a data driven org chart in excel | Excel Discussion (Misc queries) | |||
| Date driven formula/worksheet | Excel Discussion (Misc queries) | |||
| How do I change how PivotChart data is summarized, from sum to av. | Excel Worksheet Functions | |||
Hybrid Mode
