Leasing Report Progress

[a_t83]

Please check the below table for each leasing agent:

Client	Status <tbody> </tbody>	Just Browsing	Interested	On the Fence	Hook Line & Sinker	Lead Lost
1	Active	14-Jul-19 <tbody> </tbody>	15-Jul-19 <tbody> </tbody>	16-Jul-19 <tbody> </tbody>	17-Jul-19 <tbody> </tbody>
2	Active		9-May-19 <tbody> </tbody>	16-Sep-19 <tbody> </tbody>
3	Active		9-Apr-19			5-May-19

<tbody>
</tbody>

I need to count 5 categories as the below:

1- Just Browsing
2- Interested
3- On the Fence
4- Hook Line & Sinker
5- Lead Lost

But I want to count the last date of the progress without deleting the date/s before.

For example, in Client 1 we need to count only (Hook Line & Sinker), and Client 2 we will count only (On the Fence), etc

The results I need to be as the below:

Progress Update	Clients Status
Just Browsing	5
Interested	6
On the Fence	4
Hook Line & Sinker	6
Lead Lost	2

<tbody>
</tbody>

Please let me know if this it clear.

 

[alansidman]

Here is a VBA solution that will work for you. Make sure your raw data is on a sheet called Data and the Results are on a sheet named Results. Put your Results headers in column A starting with A1.

Option Explicit


Sub Leasing()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Data")
    Set s2 = Sheets("Results")
    Dim a As Long, b As Long, c As Long, d As Long, e As Long
    Dim lr As Long, lc As Long, i As Long
    Application.ScreenUpdating = False
    a = 0
    b = 0
    c = 0
    d = 0
    e = 0
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        lc = s1.Cells(i, Columns.Count).End(xlToLeft).Column
        If lc = 3 Then a = a + 1
        If lc = 4 Then b = b + 1
        If lc = 5 Then c = c + 1
        If lc = 6 Then d = d + 1
        If lc = 7 Then e = e + 1
    Next i
    s2.Range("B1") = a
    s2.Range("B2") = b
    s2.Range("B3") = c
    s2.Range("B4") = d
    s2.Range("B5") = e
    Application.ScreenUpdating = True
    MsgBox "Action Complete"




End Sub

Sheet Data

v	A	B	C	D	E	F	G
1	Client	Status	Just Browsing	Interested	On the Fence	Hook Line & Sinker	Lead Lost
2	1	Active	7/14/2019	7/15/2019	7/16/2019	7/17/2019
3	2	Active		5/9/2019	9/16/2019
4	3	Active		4/9/2019			5/5/2019
5	4	Active	7/14/2019	7/15/2019	7/16/2019	7/17/2019
6	5	Active		5/9/2019	9/16/2019
7	6	Active		4/9/2019			5/5/2019
8	7	Active	7/14/2019	7/15/2019	7/16/2019	7/17/2019
9	8	Active		5/9/2019	9/16/2019
10	9	Active		4/9/2019			5/5/2019

Sheet Results

v	A	B
1	Just Browsing	0
2	Interested	0
3	On the Fence	3
4	Hook Line & Sinker	3
5	Lead Lost	3

 

[a_t83]

thanks a lot, I will try it and let you know

 

[a_t83]

Many thanks it's working perfectly.

Here is a VBA solution that will work for you. Make sure your raw data is on a sheet called Data and the Results are on a sheet named Results. Put your Results headers in column A starting with A1.

Option Explicit


Sub Leasing()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Data")
    Set s2 = Sheets("Results")
    Dim a As Long, b As Long, c As Long, d As Long, e As Long
    Dim lr As Long, lc As Long, i As Long
    Application.ScreenUpdating = False
    a = 0
    b = 0
    c = 0
    d = 0
    e = 0
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        lc = s1.Cells(i, Columns.Count).End(xlToLeft).Column
        If lc = 3 Then a = a + 1
        If lc = 4 Then b = b + 1
        If lc = 5 Then c = c + 1
        If lc = 6 Then d = d + 1
        If lc = 7 Then e = e + 1
    Next i
    s2.Range("B1") = a
    s2.Range("B2") = b
    s2.Range("B3") = c
    s2.Range("B4") = d
    s2.Range("B5") = e
    Application.ScreenUpdating = True
    MsgBox "Action Complete"




End Sub

Sheet Data

v	A	B	C	D	E	F	G
1	Client	Status	Just Browsing	Interested	On the Fence	Hook Line & Sinker	Lead Lost
2	1	Active	7/14/2019	7/15/2019	7/16/2019	7/17/2019
3	2	Active		5/9/2019	9/16/2019
4	3	Active		4/9/2019			5/5/2019
5	4	Active	7/14/2019	7/15/2019	7/16/2019	7/17/2019
6	5	Active		5/9/2019	9/16/2019
7	6	Active		4/9/2019			5/5/2019
8	7	Active	7/14/2019	7/15/2019	7/16/2019	7/17/2019
9	8	Active		5/9/2019	9/16/2019
10	9	Active		4/9/2019			5/5/2019

<tbody>
</tbody>

Sheet Results

v	A	B
1	Just Browsing	0
2	Interested	0
3	On the Fence	3
4	Hook Line & Sinker	3
5	Lead Lost	3

<tbody>
</tbody>