SOLVED

Return filtered column data from one sheet to another

Copper Contributor

Hi! I have a sheet called Employees with 3 columns:

  • ID
  • Name
  • Work Status (Active, Inactive)
IDNameWork Status
001AbigailActive
002BrianActive
003GraceInactive

 

I'd like to filter active employees and send them over to another sheet called Payroll, which fills 2 columns:

  • Employee ID
  • Employee Name
Employee IDEmployee Name
001Abigail
002Brian

 

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.

10 Replies
best response confirmed by emilioramirezz (Copper Contributor)
Solution

@emilioramirezz 

In 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: 

 
 
 
 

Active Employees.PNG

Thanks @Twifoo, that worked great! I just have a couple of questions.

 

  • I don't understand what the SMALL function, applied through AGGREGATE, is doing. What is the division Employees!A$2:A$11/(Employees!C$2:C$11="Active") doing and ROW()-1? Could you explain it to me a little bit further?

 

  • Is there a way to avoid having to manually edit the formula to include future employees added to the Employees sheet? 

 

@emilioramirezz 

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: 

Active EmployeesV1.0.PNG

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. 

@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.

@emilioramirezz 

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.

@emilioramirezz 

Employee number 108 is missing producing a mismatch between row numbers and employee IDs.

@emilioramirezz 

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)),"")

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.

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.

1 best response

Accepted Solutions
best response confirmed by emilioramirezz (Copper Contributor)
Solution

@emilioramirezz 

In 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: 

 
 
 
 

Active Employees.PNG

View solution in original post