Create a new table separating managers from a larger employees database table with select columns

Copper Contributor

Hello,
I hope someone could help. 

I have a massive table of employee data with hundreds of data points on one sheet.

I want to create a new table on a new sheet which has just a few data points (Emp ID, Name, Email, Etc) from the main table but is only Managers or People Manager (from the Manager Status column in the main table).

 

I've tried Pivot Tables and VLookup but can't get it to work how I want? VLookup was close, but when I filled down it was just the same row repeated.

 

Any advice would be helpful. 

 

Thank you!

2 Replies

@Alex10101 

Let's say the table is named Employees.

On the other sheet, enter the following formula in A1:

 

=VSTACK(Employees[#Headers],FILTER(Employees, (Employees[Manager Status]="Manager")+(Employees[Manager Status]="People Manager"), ""))

@Alex10101 

=IFERROR(INDEX(A$2:A$20,SMALL(IF(($A$2:$A$20="People Manager")+($A$2:$A$20="Manager"),ROW($A$2:$A$20)-1),ROW(A1))),"")

 

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is in cell I2 and filled across range I2:N12 in the example. From the result you can delete the columns you don't want.

manager status.png

 

If you work with Office 365 or Excel for the web you can apply this formula which spills the result.

=CHOOSECOLS(FILTER(A2:F20,(A2:A20="People Manager")+(A2:A20="Manager")),1,3,4,6)

choosecols.png