Forum Discussion
Create a new table separating managers from a larger employees database table with select columns
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
=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.
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)
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"), ""))
