Extracting names from a list but adding all names

[rcmii]

I know there is an answer to this because I've seen it done...but I can't find the thread I've reviewed in the past.

What I'm trying to solve.
I have a list of over 1000 names in multiple departments. I'm trying to extract all of the names to a specific department, and put those names on the department tab but the list grows to capture all of the names from a specific department. Then, I want to move to the next tab (new department) and have the formula pull all of the names from the list for that specific department.

I do not know VBA so a formula would be very helpful.

Thanks in advance for your help.

RCM

 

[DanteAmor]

For this operation I recommend VBA.
Otherwise you would have 1000 formulas on each sheet for each column, that is, if you want to move 10 columns you would have 10,000 formulas on each sheet of each department.

The sheets of the departments are already created?

Even the macro could create the missing sheets.

You want to copy the data after the last data of each sheet, or it would be to delete the sheet and put the new data.

 

[rcmii]

After a long search, I found the old file from years ago. This still works in case anyone else wants to utilize it. Well, it works for me.

Obviously, the file names are specific to me...

=@IF(@OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0)+ROW(A1),,,)<>$D$4,"",OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0)+ROW(A1),1,,))

 

[rcmii]

I need to correct this to illustrate how this looks at a list of names (in my example) and lists only those names specific to each department, row by row.

First cell (D44 in my example)
=@OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0)-1,1,,)

Next Cell down (D45 in my example)
=@IF(@OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0),,,)<>$D$4,"",OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0),1,,))

Next cell down (D46 in my example)
=@IF(@OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0)+ROW(A1),,,)<>$D$4,"",OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0)+ROW(A1),1,,))

Next cell down...
=@IF(@OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0)+ROW(A2),,,)<>$D$4,"",OFFSET('Retrieve - HC Name'!$B$1,MATCH($D$4,'Retrieve - HC Name'!$B:$B,0)+ROW(A2),1,,))

and so on...

D4 in my example is the department reference (like a specific dept. #)