• 466K Members
• 10.2K Online
• 564K Conversations
SOLVED

## Return filtered column data from one sheet to another

Occasional Contributor

# Return filtered column data from one sheet to another

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

• ID
• Name
• Work Status (Active, Inactive)
 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
 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.

10 Replies
Solution

# Re: Return filtered column data from one sheet to another

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:

# Re: Return filtered column data from one sheet to another

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?

# Re: Return filtered column data from one sheet to another

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.

# Re: Return filtered column data from one sheet to another

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

# Re: Return filtered column data from one sheet to another

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.

# Re: Return filtered column data from one sheet to another

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

# Re: Return filtered column data from one sheet to another

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

# Re: Return filtered column data from one sheet to another

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.

# Re: Return filtered column data from one sheet to another

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.

# Re: Return filtered column data from one sheet to another

You're welcome!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies