PDA

View Full Version : Solved: Count unique entries that occur over consecutive dates



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

turtsmurt
06-14-2010, 05:08 AM
If this is not possible, could someone please let me know?

GTO
06-14-2010, 05:25 AM
Hi Christine,

Welcome to vbax. Please read Here (http://www.excelguru.ca/node/7) in reference to cross posting.

Reference your question, I imagine the use of worksheet formula/functions is possible, but probably beyond me.

Cross-posted at: http://www.mrexcel.com/forum/showthread.php?t=474323

Mark

turtsmurt
06-14-2010, 05:37 AM
I'm sorry about that, I thought I had read all of the rules of posting. I can't post the links though because I have less then 3 posts. I didn't see the one about cross posting. Just trying to get some help.

I have included cross post information on the other 2 forums I posted on, thanks for the info and I will respect this rule in the future.

Cross posted.

at Mr Excel forum thread #474323

and

Microsoft.com forums thread #ed8c6e29-8007-4408-952c-c92f0adc99c2



Thanks again,

Christine :doh:

GTO
06-14-2010, 04:17 PM
Hi Christine,

There must be a better way, but I think this would do what you want.

In a Standard Module:


Option Explicit

Sub exa2()
Dim _
DIC As Object, _
wksData As Worksheet, _
rngReport As Range, _
Val As Variant, _
aryUniqRept As Variant, _
aryData As Variant, _
ary_lData As Variant, _
ary_lDataCol2 As Variant, _
DicItems As Variant, _
x As Long, _
y As Long, _
i As Long

'// CHange sheetname to suit //
Set wksData = ThisWorkbook.Worksheets(ActiveSheet.Name)
Set rngReport = Range(wksData.Range("B2"), wksData.Cells(Rows.Count, "B").End(xlUp))

Set DIC = CreateObject("Scripting.Dictionary")

With DIC
For Each Val In rngReport
.Item(Val) = Val
Next
aryUniqRept = Application.Transpose(.Items)
.RemoveAll
End With

With rngReport

aryData = .Resize(, 2).Value

ReDim ary_lData(1 To UBound(aryData, 1), 1 To 2)
For x = 1 To UBound(ary_lData, 1)
ary_lData(x, 1) = aryData(x, 1)
'// Must be a better way, but I thought to append each report with the //
'// coresponding date's long //
ary_lData(x, 2) = aryData(x, 1) & CLng(aryData(x, 2))
Next

ary_lDataCol2 = Application.Index(ary_lData, , 2)

For i = 1 To UBound(aryUniqRept, 1)
For x = 1 To UBound(ary_lData, 1)

If aryUniqRept(i, 1) = ary_lData(x, 1) Then

'// If we find a day after or before a given report, add the //
'// report to a collection and jump to next report //
If Not IsError(Application.Match(CStr(ary_lData(x, 2) + 1), _
ary_lDataCol2, 0)) _
Or Not IsError(Application.Match(CStr(ary_lData(x, 2) - 1), _
ary_lDataCol2, 0)) Then

DIC.Item(ary_lData(x, 1)) = ary_lData(x, 1)
Exit For
End If
End If
Next
Next

ReDim aryData(1 To DIC.Count, 1 To 1) As Long

DicItems = DIC.Items

For i = 1 To DIC.Count
aryData(i, 1) = DicItems(i - 1)
Next

End With

'// To list the reports that took two or more consecutive days //
wksData.Range("G2").Resize(UBound(aryData, 1)).Value = aryData

'// To list how many reports //
wksData.Range("H2").Value = DIC.Count
End Sub

Does that help?

Mark

turtsmurt
06-15-2010, 10:04 AM
Thanks Mark, Haven't been able to try it yet. There's a new emergency now in this dept. As soon as I can I will.

Thanks sooo much for the help. I really do appreciate it.

Christine

GTO
06-15-2010, 12:36 PM
Hi Christine,

Glad to help and hope it works. If you get a chance, once tested, please let me know if it did. In case not, one of the neat things (not the neatest, as the neatest thing is the folke here) about vbax is that you can post a sample wb with a Before/After, which IMO helps a lot. ".xls" format is best, as many (inluding yours truly at home) cannot 'read' 2007 format.

Hope it slows down for ya,

Mark

macropod
06-15-2010, 11:39 PM
Hi Christine,

Perhaps you'll find your needs met with the second function I posted at:
http://www.techsupportforum.com/microsoft-support/microsoft-office-support/418696-counting-unique-flight-sectors.html