Tested in Excel 2016 (16.0.9226.2114) 64-bit
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.
This file contains dummy data, the formula and the steps needed (no macros).
Jump to
Method 1: Sorting the data
See sheet [1 Sorting] from the above mentioned file.
You need a help column, let’s call it Drivers rank and the final column Drivers per car.
You need to count for each Car plate the number of Driver so sort the data at least by these 2 columns, in this order.
Logic: if the Car plate is the same but the Driver changed, increase the rank. Type in row 2, Driver rank column:
- =IF(A2=A1,IF(B2<>B1,IFERROR(E1+1,1),IFERROR(E1/1,1)),1)
Autofill.
You can use the header here because you can consider it as car zero, being different from the first car.
Notice that the highest rank is the actual count you are searching for. So you need to find the max within each Car plate. Type in row 2, Drivers per car column:
- =MAXIFS(E:E,A:A,A2)
Autofill.
Copy-paste values and delete the help column if it isn’t needed.
Note: the formula will return what you need as long as the dataset stays sorted this way. It’s better to copy-paste values once you are happy with the result.
Method 2: Removing duplicates
This time, instead of a help column use a help sheet. Copy-paste the 2 columns for each you want to calculate the sum into a new sheet and here remove duplicates.
See sheet [Unique combinations] from the above mentioned file.
What’s left is a set of unique car-driver combinations. This means that a car plate will appear just as many times as drivers are associated with it. You can add in the third column the formula:
- =COUNTIF(A:A,A2)
Now you can bring these values in [2 Removing duplicates] sheet using:
- =VLOOKUP(A2,'Unique combinations'!A:C,3,0)
Autofill.
Copy-paste values and delete the help sheet.
Method 3: Using a pivot table
This is similar with removing duplicates but instead we use a pivot table.
We select the data and use Insert=> Pivot Table. We tick Add this data to the Data Model. This will allow us to use Distinct Count later.
Add the Car plate to Rows and Driver to Values.
Click on Count of Driver and select Value Field Settings. Here scroll down and select Distinct Count then click OK.
Now you can bring these values in [3 Using a pivot] sheet using:
- =VLOOKUP(A2,Pivot!A:B,2,0)
For larger datasets, you can use VBA like this.