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

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

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

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

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

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

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

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

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

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