The problem is that the filter needs to apply to multiple lines with the same
name.... i.e. chair would actually be a brand, so I could filter all the
items from one manufacturer and produce their locations. There might be ten
different items from teh same manufacturer, each with unique locations.




"Roger Govier" wrote:

Hi
Assuming your number of items is less than 256 (for XL2003 and earlier),
then you could copy your data, and on another sheet
Paste SpecialTranspose.
You will now have Chair, Table, Stool etc as your column headings.
Highlight row 1
DataFilterAutofilter
Use the dropdown on any column to select non-blanks, and you will see the
result you want.

If you want to see a total of the items, Insert a row above your header and
in B1 enter
=SUBTOTAL(9,B2:B10000)
copy across through the remainder of row 1.
When the filter is applied for any column, you will see the total for that
column in the first cell.
--
Regards
Roger Govier



"raphiel2063" wrote in message
...
I'm trying to set up a sort of filter so that I can pull information from
one
main page.

Room 1 Room 2 Room 3 Room 4
Chair 1 2 1
Table 1 2 1
Stool 1

The main data sheet looks (sort of ) like the above with individual
products
down the left and their locations across the top. In reality there are
hundreds of products and hundreds of locations.

I want to have a formula/macro which looks up the word 'chair' for
example,
then returns me the quantity with the corresponding heading (location),
but I
need it to skip the blanks.

I.e. If I ask it to look up 'Chair', the following is displayed

Room 1 Room 2 Room 3
Chair 1 2 1


If I asked it look up 'Stool' it would display

Room 3
Stool 1



Any ideas?