Assistance with data counting in VBA

[BeachBum]

Hi,

I have been working on a project comprised of several components. One part asks for a number of sessions from the user. The second part divides data in column A into these sessions. I have several repetitions in Column A but they have different dates, sessions and locations. What I want to find out is for each Item in Column A, how many sessions it appears in. For instance in the attached image, A appears 4 times and B appears twice. But A appears in 3 sessions on the 10th on Fleet St as per the attached image.
Capture.JPG
I need to write this as a VBA program and my initial thinking was to have a loop similar to:

For each item in column A calculate the Max and min values appearing in column C.
If Max(C)-Min(C) = 0, then number of sessions = 1
If Max(C)-Min(C) = 1, then number of sessions = 2
If Max(C)-Min(C) = 2, then number of sessions = 3 etc (for an arbitrary number of sessions)

However this does not all for the same item on a different date or street.

Does anyone have any suggestions to make this happen? I feel like it should be something simple with a couple of for loops and if statements but I just cant quite get my head around it. The sources of information I have found, while useful, just don't quite seem to assist with the problem.

Thanks,

[xavier73]

With this code you can find the max and min of column C.I don't understand the rest what you need. Can you explain more.

Sub min-max()
    Dim rng As Range
    Dim dblMin As Double
    
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    
    Set rng = Range("C3:C" & LastRow)
    dblMin = Application.WorksheetFunction.Min(rng)
    dblmax = Application.WorksheetFunction.max(rng)
    
End Sub

[BeachBum]

Thanks for that. That will definitely help.

With regard to the overall problem. I am trying to get the code to cycle through the items in Column A and create a "shortened" list of the items. For instance, I am trying to get the code to Select the first cell in column A (A3) and create a subrange listing all the rows that coincide with cells containing "A". The max min will then be run on column of the subrange and write the max-min value in a new column next to all A's. The code will then go to the next item in column A and find all that item and so on (in a loop) until the end of the range.

[xavier73]

I think that this code will work:

Sub Sort()
lastrowi = Range("C" & Rows.Count).End(xlUp).Row
j = 3
For i = 3 To lastrowi

    For k = 3 To j
                
        If (Cells(i, "A") = Cells(k, "F") And Cells(i, "B") = Cells(k, "G") And Cells(i, "D") = Cells(k, "I")) Then
            equal = "TRUE"
            Exit For
         Else
            equal = "FALSE"
        End If
    Next k
    
    If equal = "FALSE" Then
        Cells(j, "F") = Cells(i, "A")
        Cells(j, "G") = Cells(i, "B")
        Cells(j, "I") = Cells(i, "D")
        j = j + 1
    End If

Next i

lastrowA = Range("A" & Rows.Count).End(xlUp).Row
lastrowF = Range("F" & Rows.Count).End(xlUp).Row
Max = "empty"
Min = "empty"

For i = 3 To lastrowF

    For j = 3 To lastrowA
            If Cells(i, "F") = Cells(j, "A") And Cells(i, "G") = Cells(j, "B") And Cells(i, "I") = Cells(j, "D") Then
                If Max = "empty" Then
                    Max = Cells(j, "C")
                    Min = Cells(j, "C")
                    Else
                        If Cells(j, "C") < Min Then
                            Min = Cells(j, "C")
                        End If
                        If Cells(j, "C") > Max Then
                            Max = Cells(j, "C")
                        End If
                End If
            End If
    Next j
       
    Cells(i, "H") = (Max - Min) + 1
    Max = "empty"
    Min = "empty"
Next i

End Sub

[BeachBum]

Brilliant! Requires a little modification for what I am after but should work quite well. Thank you so much!!!