Oct 15 2019 10:15 PM - edited Oct 15 2019 10:16 PM
Hi! I have a sheet called Employees with 3 columns:
ID | Name | Work Status |
001 | Abigail | Active |
002 | Brian | Active |
003 | Grace | Inactive |
I'd like to filter active employees and send them over to another sheet called Payroll, which fills 2 columns:
Employee ID | Employee Name |
001 | Abigail |
002 | Brian |
I've read multiple forums and they all provide formulas that combine IFERROR, INDEX, SMALL, and ROW functions. Unfortunately, I don't understand entirely how their formulas work, so I haven't been able to use them on my data set. I'm attaching an example workbook.
Oct 15 2019 11:13 PM
SolutionIn the attached version of your file, the formula in A2 is:
=IFERROR(INDEX(Employees!A$2:A$11,
AGGREGATE(15,6,Employees!A$2:A$11/(Employees!C$2:C$11="Active"),
ROW()-1)),"")
Meanwhile, the formula in B2 is:
=IF(A2="","",
LOOKUP(A2,
Employees!A$2:B$11))
The result of the foregoing formulas is this:
Oct 16 2019 04:16 PM - edited Oct 16 2019 04:16 PM
Thanks @Twifoo, that worked great! I just have a couple of questions.
Oct 16 2019 09:37 PM
To make the ranges in both sheets dynamic, I converted them to Table (by selecting a cell therein and pressing Ctrl+T). The new formula in A2 is now:
=IFERROR(INDEX(EmployeeTable[ID],
AGGREGATE(15,6,EmployeeTable[ID]/(EmployeeTable[Work Status]="Active"),
ROW()-1)),"")
Also, the new formula in B2 is now:
=IF([Employee ID]="","",
LOOKUP([Employee ID],
EmployeeTable[ID]:EmployeeTable[Name]))
The result of the foregoing formulas is now this:
In the formula in A2, EmployeeTable[ID]/(EmployeeTable[Work Status]="Active") creates this array:
{1;2;#DIV/0!;4;#DIV/0!;#DIV/0!;#DIV/0!;8;9;#DIV/0!}
In the options argument of AGGREGATE, 6 ignores the #DIV/0 errors in the foregoing array. ROW()-1 evaluates to 1, and increases to 2, 3, and so forth as the formula is copied down the rows. Thus, AGGREGATE returns the 1st smallest, 2nd smallest, 3rd smallest, and so forth.
Oct 16 2019 11:47 PM
@Twifoo thanks for explaining. I have a better understanding of what the formula is doing now.
I seem to be having a problem with the A2 formula. I noticed this before and after converting the data to Table. The result brings most of the correct active workers but omits some of them and replaces them with inactive workers.
I'm attaching the workbook with cells that contain an error highlighted in red.
Oct 17 2019 12:30 AM - edited Oct 17 2019 12:32 AM
If anyone has a similar problem and is working within an Office 365 environment, a simple solution now exists.
I have assumed @Twifoo's use of a table for the source data to make the data dynamic but I returned the output table to a range. The formula is written into a single cell of the payroll sheet (A2 for example) and will spill into adjacent cells to pull all the required information forward.
= FILTER(EmployeeTable[[ID]:[Name]],
EmployeeTable[Work Status]="Active")
Something to bear in mind is that the payroll list is dynamic so a given employee will not always appear on the same row. This means that any further fields need to be populated by looking up by the employee ID rather than typing directly into the worksheet.
Oct 17 2019 12:45 AM
Employee number 108 is missing producing a mismatch between row numbers and employee IDs.
Oct 17 2019 02:11 AM
I assumed that the Employee ID's were consecutive. Now, I noticed that Employee ID 108 and 184 were missing. In the attached version of the file, I modified the formula in A2 to this:
=IFERROR(INDEX(EmployeeTable[ID],
AGGREGATE(15,6,ROW(EmployeeTable[ID])/(EmployeeTable[Work Status]="Active")-1,
ROW()-1)),"")
Oct 17 2019 03:01 PM
Thanks @Peter Bartholomew. The FILTER function seems really useful; unfortunately, my version of Excel doesn't support it. I'll keep it in mind if Microsoft rolls it out to every user.
Oct 17 2019 03:03 PM
Thanks @Twifoo. They are supposed to be consecutive. I made a mistake and missed those two missing rows.
Thanks for helping me solve this problem.
Oct 18 2019 01:01 AM
You're welcome!
Oct 15 2019 11:13 PM
SolutionIn the attached version of your file, the formula in A2 is:
=IFERROR(INDEX(Employees!A$2:A$11,
AGGREGATE(15,6,Employees!A$2:A$11/(Employees!C$2:C$11="Active"),
ROW()-1)),"")
Meanwhile, the formula in B2 is:
=IF(A2="","",
LOOKUP(A2,
Employees!A$2:B$11))
The result of the foregoing formulas is this: