Newbie in forum. Automate filters to another sheet.

Hi,

I am new to this thread and would like to ask a question.
Attached is a sample sheet. The original sheet has about 33k of lines and 24 columns and is updated about 3 times a week, sometimes everyday.
I would like to know if there is a way based on column B and D to list only the active sales agents to the "Active" sheet and to have it automatically update when the Employee list is updated.

I know that this could be done manually but I would just like to know if there is a way to automate.

Perhaps use a Pivot Table?

A formula solution would be to add first a helper column to the Employee List sheet...

So in G2:

=IF(AND(B2="sales agent",D2="Active"),COUNT(G$1:G1)+1,"")

copied down.

Then in the Active sheet, use a cell to get the count of matches from the Employee List sheet, so for example in Active sheet, C1: =MAX('Employee List'!$G:$G)

then in A2:

=IF(ROWS($A$1:$A1)>$C$1,"",(INDEX('Employee List'!A:A,MATCH(ROWS($A$2:$A2),'Employee List'!$G:$G))))

copied down as far as you need.

Adjust all references and ranges to suit.

Note:

If you turn the Employee List into a Table, then the helper formula should automatically get copied down as you enter new data below.

Personally, I'd probably lean towards the PivotTable, as in the attached. Like NBVC suggests though, the data should really be in an Excel table, as this is a way better data source for this kind of activity than a list or non-structured table (as you currently have it.)

One thing that gives me a bit of pause though, is tracking updates. I suppose you could copy the existing table, to a history sheet, then pull in the new data table and add a VLOOKUP to check what's changed, then report on that in the PivotTable.