Extracting all.....

[dawa]

Hi excel masters... a lil help ....

Department Name
receiving santosh
receiving janak
picking madan
picking suraj
putaway aman
putaway giri
closing ramu
putaway hemant
receiving hari

when i type in the department(e.g receiving) it extract all the names of the employees related with receiving so with putaway and others

 

[Somendra Misra]

@dawa

Assuming your department list in Column A. A1 is header "Department"
Name list in column B. B1 is header "Name"

D2: you will input department
E2: Put below formula and copy down as per your need.
=IF(ROWS(E$2:E2)<=SUMPRODUCT((--($A$2:$A$9=$D$2))),INDEX($B$2:$B$9,SMALL(If($A$2:$A$9=$D$2,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(E$2:E2))),"")

Enter with Ctrl+Shift +Enter as this is an array formula.

Just advise if any issue.

Regards,

 

[dawa]

i tried by copying down doesn't extract
its only extracting santosh the 1st name cell

 

[Somendra Misra]

Press F2 by selecting the cell in which you put the formula and then press Ctrl+SHift+Enter. After this you can see {} around formula. If you saw them then copy it down, it will surely extract.

Regards,

 

[dawa]

thanks Somendra you rock .... its was my mistake. Its working fine now. Solved

 

[Somendra Misra]

@dawa

Glad you solve it & thanks for youe kind words. Welcome back whenever needed.

Regards,