Extracting data by data difference

[saylur]

I have a large amount of data which includes account numbers and
'transactions.' Each account has more than one transaction, and each
transaction has an associated date. I.e.:

Account Transaction Date
123 1 1/1/08
123 2 1/31/08
234 1 1/15/08
234 2 1/16/08
234 3 1/17/08

OK; How do I create a report which extracts all account data for each
account with transactions less than a certain number of days apart (like 30
days apart)?

My macro and formula skills are limited.

THanks for any help!

 

[saylur]

Clarification:

I need to include all data for accounts which have at least one pair of
transactions which are 30 days or less apart.

 

[Rick Rothstein \(MVP - VB\)]

Two questions... One, is the data always going to be sorted (as you showed
in your example)? Two, what do you want included in the report (which I
assume will be placed on another worksheet)? For that last question, what if
you had this data...

Account Transaction Date
234 1 1/01/08
234 2 2/16/08
234 3 2/17/08

For this account, only the last two transactions are within 30 days of each
other... how do want them listed in the report?

Rick

 

[saylur]

Thanks;

I suppose I can sort them any way I need to, and I often do. Usually they
are sorted by account.

For the report, I'd like to have all of the data in the row for each account
(there are several other columns of data). Finally, if there's only two
transactions for an account which are within 30 days, I'd want to see all of
the transactions for that account.

I would like them in a new worksheet if possible.

Thanks!!!!

 

[Rick Rothstein \(MVP - VB\)]

See if the following does what you want. This macro **requires** your data
to be sorted by account number first and then by date second; and it also
**requires** that your source data start **on or after** Row 2 (whether you
have a header row or not). Before running the program, set the values in the
Const statements to those that match your actual setup (Account and DateCol
are column letters where your account numbers and transaction dates are
located).

Sub CreateReport()

' Set the Const(ant) values to reflect your data
Const SourceDataStartRow As Long = 2
Const ReportDataStartRow As Long = 2
Const DaysDifferential As Long = 30
Const Account As String = "A"
Const DateCol As String = "C"
Const SourceSheet As String = "Sheet3"

Dim X As Long
Dim Z As Long
Dim Index As Long
Dim LastRow As Long
Dim ReportRow As Long
Dim NewAccount As Long
Dim ReportSheet As Worksheet
Dim TestValue As String
Dim Hits() As String

With Worksheets(SourceSheet)
LastRow = .Cells(Rows.Count, DateCol).End(xlUp).Row
ReDim Hits(0 To LastRow)
Hits(0) = "X"
NewAccount = SourceDataStartRow
For X = SourceDataStartRow + 1 To LastRow
If TestValue <> .Cells(X, Account).Value Then
If .Cells(X, Account).Value <> .Cells(X - 1, Account).Value Then
NewAccount = X
End If
If .Cells(X, Account).Value <> Split(Hits(Index), Chr(1))(0) Then
If .Cells(X, Account).Value = .Cells(X - 1, Account).Value And _
.Cells(X, DateCol).Value - .Cells(X - 1, _
DateCol).Value < DaysDifferential Then
Index = Index + 1
TestValue = .Cells(X, Account).Value
Hits(Index) = .Cells(X, Account).Value & Chr(1) & NewAccount
End If
End If
End If
Next
ReDim Preserve Hits(0 To Index)
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
Set ReportSheet = Worksheets(Worksheets.Count)
ReportSheet.Name = "Report (" & Format(Now, _
"dd-mmm-yyyy hh\hmm\mss\s") & ")"
If ReportDataStartRow > 1 Then
.Rows(ReportDataStartRow).Offset(-1).Copy ReportSheet.Rows(1)
End If
ReportRow = ReportDataStartRow
For X = 1 To Index
Z = Split(Hits(X), Chr(1))(1)
Do While .Cells(Z, Account).Value Like Split(Hits(X), Chr(1))(0)
.Cells(Z, Account).EntireRow.Copy ReportSheet.Cells(ReportRow, "A")
Z = Z + 1
ReportRow = ReportRow + 1
Loop
Next
End With

End Sub



Rick