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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GeneR - 23 Oct 2006 21:49 GMT

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?

Reply to this Message

Biff - 23 Oct 2006 22:47 GMT

Define: large inventory of equipment.

How many rows and how many columns?

Biff

>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
[quoted text clipped - 7 lines]
>
> Any suggestions?

Reply to this Message

GeneR - 24 Oct 2006 01:26 GMT

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

Thanks

> Define: large inventory of equipment.
>
[quoted text clipped - 13 lines]
> >
> > Any suggestions?

Reply to this Message

Biff - 24 Oct 2006 02:06 GMT

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

Biff

> Approx. 18,000 rows and 12 - 15 columns.
>
[quoted text clipped - 19 lines]
>> >
>> > Any suggestions?

Reply to this Message

GeneR - 24 Oct 2006 13:01 GMT

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.

> Formulas would not be the best approach. See Debra's reply.
>
[quoted text clipped - 23 lines]
> >> >
> >> > Any suggestions?

Reply to this Message

Biff - 25 Oct 2006 03:32 GMT

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

> If it makes any difference, the most any one department would have on
> their
[quoted text clipped - 33 lines]
>> >> >
>> >> > Any suggestions?

Reply to this Message

Debra Dalgleish - 24 Oct 2006 00:15 GMT

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'

> 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
[quoted text clipped - 4 lines]
>
> Any suggestions?

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message