I am working on transportation of some employees whose route detail is given in blue region. Login means towards office and logout towards home. Same route ID means that those employees are traveling together in same cab. No of employees suggest how many employees are traveling in each trip.
I want to make a summary report as shown in green region. I want to know with how many employees each employee traveled with - i.e. the yellow highlighted region. The cell shows me that employee d for Login 05:00 shift has either traveled alone or with only one other employee. I want these values for the whole Clubbing column.
To achieve this, I have used the formula shown in formula bar, in the highlighted cell. On pressing F9, I am getting the result like the one in yellow highlighted cell.
But my formula seems a little clumsy. Also, to get comprehensible results, I have to go to each cell in Clubbing column and hit F9 which is unwieldy (this table is small, I have hundreds of thousands of entries).
Is there a more efficient and lucid way of getting this result? Please share. I am also open to VBA solutions.
To clarify, the final output I need is something like this - the values given in R column.
