PDA

View Full Version : Assistance with data counting in VBA



BeachBum
08-07-2016, 09:48 PM
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.
16815
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
08-11-2016, 03:20 AM
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
08-11-2016, 04:39 PM
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
08-13-2016, 05:09 AM
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
08-15-2016, 12:08 AM
Brilliant! Requires a little modification for what I am after but should work quite well. Thank you so much!!!