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?

You could use a VLOOKUP, maybe something like...

Let's say your data range is A5:E1000

In cell B2 type =VLOOKUP(A2,A5:E1000,2)

If you type "Chair" into A2 the result in B2 will be 1 as the formula
returns the value from Column 2 in the same row as "Chair". Repeat the
formula in B3,B4 and B5 but replace the 2 at the end of the formula with 3
for B3, 4 for B4 and 5 for B5 and it should return all the room numbers.

Hope this helps,

Gav.

Repeat this formula for

"raphiel2063" wrote:

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?

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?

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?

Gav

I know about the vlookup formula but I need it to skip the blanks and jsut
give me the columns with information in. I.e. there may be a stool in the
third column (room 3) but there may be no more for the next forty seven rooms
(room 50). I need it to 'filter' the blanks and just put

Room 3 Room 50
1 1

instead of

Room 1 Room 2 Room 3 Room 4................... Room 50
0 0 1 0
1





"Gav123" wrote:

You could use a VLOOKUP, maybe something like...

Let's say your data range is A5:E1000

In cell B2 type =VLOOKUP(A2,A5:E1000,2)

If you type "Chair" into A2 the result in B2 will be 1 as the formula
returns the value from Column 2 in the same row as "Chair". Repeat the
formula in B3,B4 and B5 but replace the 2 at the end of the formula with 3
for B3, 4 for B4 and 5 for B5 and it should return all the room numbers.

Hope this helps,

Gav.

Repeat this formula for

"raphiel2063" wrote:

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?

Here's one more play to try out ..

Illustrated in this sample construct:
http://www.savefile.com/files/1026029
Lookup item to return row n col data.xls

Assume source data as posted is in a sheet: x, with Room numbers in B1
across, and items listed in A2 down (eg: Chair, Table, etc)

In a helper sheet: y (say)
Put in B2: =IF(x!B2="","",COLUMNS($A:A))
Copy across and fill down to cover the max expected extent of data in x

Then in the output sheet: z (say),
Assume A2 will house the item of interest, eg: Chair

Put in B1:
=IF(COLUMNS($A:A)COUNT(OFFSET(y!$B$1:$E$1,MATCH($ A2,x!$A:$A,0)-1,)),"",INDEX(x!$B$1:$E$1,SMALL(OFFSET(y!$B$1:$E$1 ,MATCH($A2,x!$A:$A,0)-1,),COLUMNS($A:A))))

Put in B2:
=IF(COLUMNS($A:A)COUNT(OFFSET(y!$B$1:$E$1,MATCH($ A2,x!$A:$A,0)-1,)),"",INDEX(OFFSET(x!$B$1:$E$1,MATCH($A2,x!$A:$A ,0)-1,),SMALL(OFFSET(y!$B$1:$E$1,MATCH($A2,x!$A:$A,0)-1,),COLUMNS($A:A))))

Select B1:B2, copy across to cover the same max horiz. extent as done in y.
This would return the desired results neatly bunched to the left, depending
on the input in A2.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"raphiel2063" wrote:
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?

Think might as well use the entire cols range ($B$1:$IV$1) instead to cater
for it ..

In z,

Put instead in B1:
=IF(COLUMNS($A:A)COUNT(OFFSET(y!$B$1:$IV$1,MATCH( $A2,x!$A:$A,0)-1,)),"",INDEX(x!$B$1:$IV$1,SMALL(OFFSET(y!$B$1:$IV $1,MATCH($A2,x!$A:$A,0)-1,),COLUMNS($A:A))))

and in B2:
=IF(COLUMNS($A:A)COUNT(OFFSET(y!$B$1:$IV$1,MATCH( $A2,x!$A:$A,0)-1,)),"",INDEX(OFFSET(x!$B$1:$IV$1,MATCH($A2,x!$A:$ A,0)-1,),SMALL(OFFSET(y!$B$1:$IV$1,MATCH($A2,x!$A:$A,0)-1,),COLUMNS($A:A))))

Select B1:B2, copy across to cover the same max horiz. extent as done in y.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max

That looks like it will almost do the trick. The problem I have is that the
actual table doesn't just filter out one item like "chair". In fact the
'filtered' category is a type with sub-catergories like below. I want to only
take the chair information and then apply the formula's you suggested.


Type Model Room 1 Room 2 Room 3 Room 4 Room 5
Chair Art 1 1
Chair science 5
1 1
Table Art 1 1 1
Table Science 1 1


Is it possible to do such a thing? I.e. filter horizontally then vertically?

I can't get my head around it and am lost at the moment.

"Max" wrote:

Think might as well use the entire cols range ($B$1:$IV$1) instead to cater
for it ..

In z,

Put instead in B1:
=IF(COLUMNS($A:A)COUNT(OFFSET(y!$B$1:$IV$1,MATCH( $A2,x!$A:$A,0)-1,)),"",INDEX(x!$B$1:$IV$1,SMALL(OFFSET(y!$B$1:$IV $1,MATCH($A2,x!$A:$A,0)-1,),COLUMNS($A:A))))

and in B2:
=IF(COLUMNS($A:A)COUNT(OFFSET(y!$B$1:$IV$1,MATCH( $A2,x!$A:$A,0)-1,)),"",INDEX(OFFSET(x!$B$1:$IV$1,MATCH($A2,x!$A:$ A,0)-1,),SMALL(OFFSET(y!$B$1:$IV$1,MATCH($A2,x!$A:$A,0)-1,),COLUMNS($A:A))))

Select B1:B2, copy across to cover the same max horiz. extent as done in y.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Don't think it is possible. It has to be a single type (eg: Chair) or a
single concat string (eg: Chair-Art) as the combination of col headers to
values which are not blank is unique to each.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"raphiel2063" wrote:
Max

That looks like it will almost do the trick. The problem I have is that the
actual table doesn't just filter out one item like "chair". In fact the
'filtered' category is a type with sub-catergories like below. I want to only
take the chair information and then apply the formula's you suggested.


Type Model Room 1 Room 2 Room 3 Room 4 Room 5
Chair Art 1 1
Chair science 5
1 1
Table Art 1 1 1
Table Science 1 1


Is it possible to do such a thing? I.e. filter horizontally then vertically?

I can't get my head around it and am lost at the moment.

Doh! Do you know if it would be possible to have a macro which indexes the
whole thing and then sort it by type (as you can do with a formula), then
have the macro remove the empty columns? I'd need it to reset afterwards so
multiple 'reports' could be made.



"Max" wrote:

Don't think it is possible. It has to be a single type (eg: Chair) or a
single concat string (eg: Chair-Art) as the combination of col headers to
values which are not blank is unique to each.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"raphiel2063" wrote:
Max

That looks like it will almost do the trick. The problem I have is that the
actual table doesn't just filter out one item like "chair". In fact the
'filtered' category is a type with sub-catergories like below. I want to only
take the chair information and then apply the formula's you suggested.


Type Model Room 1 Room 2 Room 3 Room 4 Room 5
Chair Art 1 1
Chair science 5
1 1
Table Art 1 1 1
Table Science 1 1


Is it possible to do such a thing? I.e. filter horizontally then vertically?

I can't get my head around it and am lost at the moment.

I'm not sure. You could try a new posting in .programming.

But how about the sub and the sample offered by JB (Jacques) in response to
your multi-post* in .misc?
*pl refrain from doing this

Why not check it out and reply further to him there?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"raphiel2063" wrote in message
...
Doh! Do you know if it would be possible to have a macro which indexes the
whole thing and then sort it by type (as you can do with a formula), then
have the macro remove the empty columns? I'd need it to reset afterwards
so
multiple 'reports' could be made.