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.
Few array (CSE) formula, combined of INDEX and MATCH wrapped with IFERROR, solves the issue:
:Caveat:
How it works:
Select A12:E29 and SORT the data on Employee column in Ascending order.
Insert date 02/01/2001 in cell F13.
An array formula in cell G13:
{=IFERROR(INDEX(B$13:B$29, SMALL(IF(COUNTIF($F$13, $A$13:$A$29), ROW($A$13:$D$29)-MIN(ROW($A$13:$D$29))+1), ROW(A1)), COLUMN(A1)),"")}
Enter this array formula in cell H13:
{=IFERROR(INDEX($C$13:$C$29, SMALL(IF(COUNTIF($F$13, $A$13:$A$29), ROW($A$13:$D$29)-MIN(ROW($A$13:$D$29))+1), ROW(A1)), COLUMN(A1)),"")}
An array formula in cell I13:
{=IFERROR(INDEX($E$13:$E$29, SMALL(IF(COUNTIF($F$13, A$13:A$29), ROW($A$13:$D$29)-MIN(ROW($A$13:$D$29))+1), ROW(A1)), COLUMN(A1)),"")}
Note:
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments