Insert Filtered List

[briangriffin]

I have data in columns such as this:

Dept.    Date      Revenue
1000     2/1/2019    $1000
1000     2/2/2019    $2000
1000     2/3/2019    $3000
1001     2/1/2019    $4000
1001     2/2/2019    $5000
1001     2/3/2019    $6000

I need to be able to look up the department number and return all records for a single department and insert them into another worksheet.

Dept.    Date      Revenue
1001     2/1/2019    $4000
1001     2/2/2019    $5000
1001     2/3/2019    $6000

The original data comes in sorted by department and date. I can't use VBA, it need to be Excel functions only. I can get the first row to come in using vlookup but I'm not sure how to get the other rows.

TIA.

 

[SkipVought]

Hi,

The quick answer is to use a query. But that assumes some thing that may not be the case.

Are these values for a specific Dept to be added to an existing table?

Will other department data go to other sheets?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[briangriffin]

I only need to see one department's data at a time, based on changing the department number on another worksheet. The filtered data is feeding a dashboard.

 

[SkipVought]

If you only need to “see” one Dept at a time, what about just using the Auto Filter feature in your table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

If dates for department are unique and department can be selected separately, you can use pivot table with department as page field.

If you have office 2016 or higher (incl. 365) and data can be refreshed manually, you can prepare a set of queries with get&transform part of excel (aka power query). User will have to select department from the list, next right-click table and refresh all.

Another solution is to create a set of pivot tables and tables, next synchronize them with slicers (in your case with departments) - excel 2010, succesively improved in higher versions.

combo