Looking up a name in a list and returning only the column with dat

[raphiel2063]

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?

 

[Gav123]

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

 

[Roger Govier]

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 Special>Transpose.
You will now have Chair, Table, Stool etc as your column headings.
Highlight row 1
Data>Filter>Autofilter
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.

 

[raphiel2063]

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.

 

[raphiel2063]

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

 

[Max]

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]

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.

 

[raphiel2063]

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]

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.

 

[raphiel2063]

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]

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?