A formula to filter data and copy it from one worksheet to another

[Guest]

I currently have one worksheet setup that contains a large inventory of
equipment. I want to be able to setup another worksheet for users to input a
number (department code) and have it filter and copy the inventory from the
large inventory. In other words, I only want them to be able to call up
their individual inventories by simply inputing their code, without having to
see or worry about anything else.

Any suggestions?

 

[Biff]

Define: large inventory of equipment.

How many rows and how many columns?

Biff

 

[Debra Dalgleish]

You can use programming and an Advanced Filter. There are examples here:

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0001 - Product List by Category' or 'FL0005 -
Phone List for Selected Name'

 

[Guest]

Approx. 18,000 rows and 12 - 15 columns.

Thanks

Define: large inventory of equipment.

How many rows and how many columns?

Biff

 

[Biff]

Formulas would not be the best approach. See Debra's reply.

Biff

 

[Guest]

If it makes any difference, the most any one department would have on their
inventory is probably 200 items with most of the departments only having less
than 75. The database can be sorted by department number.

 

[Biff]

Ok, if your data is sorted by the dept code you can try this and see if the
performance is acceptable.

THE DATA MUST BE SORTED BY THE DEPT CODE OR THIS WON'T WORK!

Here's a small sample file:

Filter.xls 26.5kb

http://cjoint.com/?kzekHu6Yq6

The sample file uses a much smaller data table than you describe, 20 rows by
5 columns, but you'll see the formula needed.

**The formula on Sheet2 B2 has been copied across to 5 columns then down to
enough rows that it covers the maximum expected number of matches. For
example, you say:

the most any one department would have on their inventory is probably
200 items with most of the departments only having less than 75.

So that means you would have to copy the formula to AT LEAST 200 rows.

For those who might be following this thread but don't want to/can't
download the sample file:

Data table on Sheet1 in the range A2:F20. The SORTED code numbers in column
A. The sort can be either ascending or descending, it doesn't matter.

Sheet2 A2 = user input of code number to filter.

Formula entered in Sheet2 B2:

=IF(ROWS(Sheet1!$1:1)<=COUNTIF(Sheet1!$A$2:$A$20,$A$2),INDEX(Sheet1!$B$2:$F$20,MATCH($A$2,Sheet1!$A$2:$A$20,0)+ROWS($1:1)-1,COLUMNS($A:A)),"")

Copied across then down (see ** above).

Biff