Solved: Count unique entries that occur over consecutive dates

[turtsmurt]

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]

If this is not possible, could someone please let me know?

[GTO]

Hi Christine,

Welcome to vbax. Please read Here 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]

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

[GTO]

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]

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]

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]

Hi Christine,

Perhaps you'll find your needs met with the second function I posted at:
http://www.techsupportforum.com/micr...t-sectors.html