• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract employee list

ExcelSur

Member
Hello Experts

I am trying extract employee details (Jobs worked and hours spent) from a table based on a division that the employee worked. I have attached a sample spreadsheet.

Appreciate your help.
 

Attachments

  • Chandoo employee names.xlsx
    9.6 KB · Views: 8
Hi, see attached.

[J7]=IF(ROW(J1)<=COUNTIF($D$5:$D$18;$J$4),INDEX(C$5:C$18,AGGREGATE(15,6,(ROW($C$5:$C$18)-4)/($D$5:$D$18=$J$4),ROW(J1))),"")
drag across and then delete the 2nd column (which is going to be the division).
 

Attachments

  • Copy of Chandoo employee names.xlsx
    10.5 KB · Views: 11
Hi, see attached.

[J7]=IF(ROW(J1)<=COUNTIF($D$5:$D$18;$J$4),INDEX(C$5:C$18,AGGREGATE(15,6,(ROW($C$5:$C$18)-4)/($D$5:$D$18=$J$4),ROW(J1))),"")
drag across and then delete the 2nd column (which is going to be the division).

Hello GraH,
Thanks for responding to my problem. I have used your formula and it is also picking other employees who are not in the division (PIE). I dont see any issues with your spreadsheet, but when I used the formula in my spreadsheet, it first picked an employee that was not in the PIE division
 
In J7, copied across and down :

=IFERROR(INDEX($C:$F,AGGREGATE(15,6,ROW($C$5:$C$18)/($D$5:$D$18=$J$4),ROW(A1)),MATCH(J$6,$C$4:$F$4,0)),"")

68190
 
Back
Top