Date client stoped activity

[cocks17]

Hi Board

My challenge is in the screenshot below. Its a typical data set from what I work with. I need to populate the 2 tables with a list of clients which have stopped activity last month and this month with the date they stopped and the revenue they generated for that month.

The rule to say a client has stopped in current month is no activity in a the most recent date of data available or earlier. The data is not always today so would need to be based on the most recent date along the top row.

The rule needed for the last month to say someone has stopped is logical meaning they either stopped mid month or did not continue to the current month.

any and all suggestions welcome!

Thanks
Paul

 

[tweedle]

Let's give this a swing;

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2		6/27/2011	6/28/2011	6/29/2011	6/30/2011	7/1/2011	7/2/2011	7/3/2011	7/4/2011	7/5/2011	7/6/2011		Client Stopped Last Month	Date Stopped	Revenue		Client Stopped This Month	Date Stopped	Revenue
3	Client 1	315	403	405	498								Client 1	6/30/2011	$498.00		Client 2	7/6/2011	$580.31
4	Client 2	164.96	189.704	218.1596	250.88354	288.5161	331.7935	381.5625	438.7969	504.6164	580.3089						Client 3	7/3/2011	$1,001.49
5	Client 3	432.97	497.9155	572.602825	658.4932488	757.2672	870.8573	1001.486
Sheet

The code goes in a module.
MakeReport is the primary procedure to run.
You can use the variables to adjust as needed.

Option Base 0
Type ClientRec
    ClientName As String * 20
    StopDate As Date
    Revenue As Currency
    CurrOrLast As String
End Type
Dim arr() As ClientRec
 
Sub MakeReport()
DateRow = 2
FirstCol = 3
LastCol = 12
LastRow = Range("B" & Rows.Count).End(xlUp).Row
arrIdx = 0
ReDim arr(LastRow - (DateRow + 1))
For RowIdx = (DateRow + 1) To LastRow
    arr(arrIdx).ClientName = Cells(RowIdx, 2)
    For ColIdx = LastCol To FirstCol Step -1
    Cells(RowIdx, ColIdx).Select
        If Cells(RowIdx, ColIdx) > 0 Then
            arr(arrIdx).StopDate = CDate(Cells(DateRow, ColIdx))
            arr(arrIdx).Revenue = Cells(RowIdx, ColIdx)
            If Month(arr(arrIdx).StopDate) = Month(Cells(DateRow, LastCol)) Then
                arr(arrIdx).CurrOrLast = "C"
            Else
                arr(arrIdx).CurrOrLast = "L"
            End If
            arrIdx = arrIdx + 1
            Exit For
        End If
    Next ColIdx
Next RowIdx
For arrIdx = LBound(arr) To UBound(arr)
    Debug.Print arrIdx, arr(arrIdx).ClientName, arr(arrIdx).StopDate _
    ; arr(arrIdx).Revenue, arr(arrIdx).CurrOrLast
Next
DumpArray "N3", "R3"
End Sub
Sub DumpArray(LastMonth$, ThisMonth$)
Dim StartCell As Range
Set StartCell = Range(LastMonth)
vSwitch = "L"
 
For l0 = 1 To 2
RoSet = 0: CoSet = 0
    If l0 = 2 Then
        vSwitch = "C"
        Set StartCell = Range(ThisMonth)
    End If
    For arrIdx = LBound(arr) To UBound(arr)
        If arr(arrIdx).CurrOrLast = vSwitch Then
        StartCell.Offset(RoSet, CoSet).Select
            StartCell.Offset(RoSet, CoSet) = arr(arrIdx).ClientName: CoSet = CoSet + 1
            StartCell.Offset(RoSet, CoSet) = arr(arrIdx).StopDate: CoSet = CoSet + 1
            StartCell.Offset(RoSet, CoSet) = arr(arrIdx).Revenue: CoSet = CoSet + 1
            RoSet = RoSet + 1: CoSet = 0
        End If 'C or L
    Next arrIdx
Next l0
End Sub