turtsmurt
06-14-2010, 04:56 AM
Hello All,
Need a bit of help, either as a formula or VBA.
I have a database with 151000 entries of transactional data.
Column A lists all of the employees by their employee number
Column B lists all of the report numbers that the employees prepared
Column C lists all of the dates that the reports were prepared
All 3 columns contain duplicate data, as the employee can create a report or multiple reports everyday.....and also because more than 1 employee may be involved in 1 report.
The information I am trying to extract is how many reports are carried over consecutive days (namely, how many unique reports took multiple days to complete. *Note* some reports are also updated at a later date, however, I only want a count of the ones that were worked on over CONSECUTIVE dates.
This biggest problem I am having is how to count the number of reports that are carried over consecutive dates...
Sample of the data: In this sample I would be looking for all reports that are worked on over consecutive days such as Report# 224119273, which would count as 1.
Emp ID Report# Date worked
204898 224038883 7/2/2010
221377 224038883 7/2/2010
161592 224119273 5/3/2010
206935 224119273 5/3/2010
209186 224119273 5/3/2010
221988 224119273 5/4/2010
209186 224119273 5/4/2010
221988 224119273 5/5/2010
209186 224119273 5/5/2010
221988 224119273 5/6/2010
209186 224119273 5/4/2010
221988 224119273 5/4/2010
Any suggestions would be greatly appreciated.
Christine
Need a bit of help, either as a formula or VBA.
I have a database with 151000 entries of transactional data.
Column A lists all of the employees by their employee number
Column B lists all of the report numbers that the employees prepared
Column C lists all of the dates that the reports were prepared
All 3 columns contain duplicate data, as the employee can create a report or multiple reports everyday.....and also because more than 1 employee may be involved in 1 report.
The information I am trying to extract is how many reports are carried over consecutive days (namely, how many unique reports took multiple days to complete. *Note* some reports are also updated at a later date, however, I only want a count of the ones that were worked on over CONSECUTIVE dates.
This biggest problem I am having is how to count the number of reports that are carried over consecutive dates...
Sample of the data: In this sample I would be looking for all reports that are worked on over consecutive days such as Report# 224119273, which would count as 1.
Emp ID Report# Date worked
204898 224038883 7/2/2010
221377 224038883 7/2/2010
161592 224119273 5/3/2010
206935 224119273 5/3/2010
209186 224119273 5/3/2010
221988 224119273 5/4/2010
209186 224119273 5/4/2010
221988 224119273 5/5/2010
209186 224119273 5/5/2010
221988 224119273 5/6/2010
209186 224119273 5/4/2010
221988 224119273 5/4/2010
Any suggestions would be greatly appreciated.
Christine