You have a set of data containing information about a car fleet:
Of course, one car can be used by several drivers and one driver can drive several cars. You are supposed to build a new column showing how many drivers drove each car. You need to keep the dataset as it is and just add the new column.
You can do this without VBA, see here, but for large datasets or when you have several files you might need VBA. So here we go.
- Public Sub CountWithinGroup()
- Dim i
- Dim LastRow, LastCol
- Dim sht As Worksheet
- Dim rs As Recordset
- Dim SQL As String
- Dim FirstColName As String, SecondColName As String
- ' check if current sheet is the one with the data
- If MsgBox("Macro will run on '" & ActiveSheet.Name & "' sheet!" & vbCr & _
- "Is it OK?", vbOKCancel) = vbCancel Then Exit Sub
- Set sht = ActiveSheet
- FirstColName = Cells(1, 1).Value
- SecondColName = Cells(1, 2).Value
- LastRow = LastRowOrCol(, , 3)
- LastCol = LastRowOrCol(1, , 3)
- Cells(1, LastCol + 1).Value = "Drivers per Car"
- ' parse all rows and find the count using SQL
- For i = 2 To LastRow
- SQL = "SELECT DISTINCT " & SecondColName & " FROM [" & sht.Name & "$] WHERE [" & FirstColName & "] ='" & Cells(i, 1).Value & "'"
- Set rs = RangeToRecordset2(ActiveWorkbook.FullName, SQL)
- Cells(i, LastCol + 1).Value = rs.RecordCount
- Set rs = Nothing
- Next i
- MsgBox "Done!"
- End Sub
See this file, [1 Sorting] sheet for a dummy dataset.
RangeToRecordset2 function can be found here.
LastRowOrCol function can be found here.