Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 466K Members
- 10.2K Online
- 564K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Return filtered column data from one sheet to another

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
## Return filtered column data from one sheet to another

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-15-2019 10:15 PM - edited 10-15-2019 10:16 PM

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.

Labels:

10 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Best Response confirmed by
emilioramirezz (Occasional Contributor)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-16-2019 04:16 PM - edited 10-16-2019 04:16 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2019 12:30 AM - edited 10-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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-17-2019 12:45 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-18-2019 01:01 AM

You're welcome!

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
35.7K
Views

7 Likes

35 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
182K
Views

8 Likes

30 Replies

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
31.4K
Views

14 Likes

14 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
16.9K
Views

12 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft