Returning row headings for values greater than 0

D

Dom

Hi,
I have a table of data that runs over several months in columns B to D
and I have different types of fruit in rows 2 to 4 e.g apples, pears and so
on. Under each month would be the number of that fruit sold that month:-

Jan Feb Mar
Apples 0 2 3
Pears 5 0 5
Oranges 7 0 6

What I need excel to do is just return the types of fruit where the value is
greater than 0 in a particular month. So if I wanted to know what fruit sold
in Jan it would return:-

Pears
Oranges

The table of data i actually have has 51 "types of fruit" and 28 "months"
but i only need excel to look at one "month" at a time. Hopes this makes
sense and any help you can provide is much appreciated as i've hit a wall
with this.

Thanks
 
D

Dom

Hi,
I'm afraid auto filter isn't an option for what I want to do. What I
want is for excel to pull the data off into a seperate table which then
populates a graph. I've got to do 50 odd of these tables and ideally want to
just paste the info in and then have excel do the rest via formulas. Was
planning to use vlookups to retrive the rest of the data from the table but
need to have the row headings first to do it. Sorry should of said that in
the original post. Any ideas?

Thanks for your help
 
M

Max

One simple play to retrieve all of it at one go in a new sheet
Assume your source table, structure as posted, is in sheet: x,
with data in row2 down, fruits in col A,
"month" cols in cols B across

In a new sheet,
Put in B2: =IF(x!B2>0,ROW(),"")
Copy B2 across by 28 cols, fill down as far as required

Then place in say, AK2:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(x!$A:$A,SMALL(B:B,ROWS($1:1))))
Copy AK2 across by 28 cols, fill down to the same extent, to return the
required results, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
 
D

Dom

Hi Max,
This seems to be working but bring back the wrong values because
my data doesn't start in the columns said. My "fruits" starts in A3 and
"Months" Headings start in B2. What would the formula be? Thanks for your
help with this.
 
M

Max

If your source data starts in row 3 down,
make a small arithmetic adjustment to the earlier extract expression

In the new sheet,
In B2: =IF(x!B3>0,ROW(),"")
Copy B2 across by 28 cols, fill down as far as required

Then in AK2, use this instead:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(x!$A:$A,SMALL(B:B,ROWS($1:1))+1))
Copy AK2 across by 28 cols, fill down to the same extent, to return the
required results, all neatly packed at the top.

[ ROWS($1:1))+1 replaces ROWS($1:1) within the SMALL part ]

The above should work ok. Please press the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
 
D

Dom

Hi Max,
Wondered if you could help me some more on this. Now the formula
works I wanted to vlookup some other values to return a table of data which I
then wanted to populate a graph. The problems I'm having is that all the
error values and so on are messing up the graph. The table looks like this:-

Consultant - Security 1
GIS and Geospatial 1
Helpdesk/Customer Services 15
Information Technology 34
#N/A
#N/A
#N/A
#N/A

The table is 56 rows in total. Is there an easy way to get the graph not to
plot the blank or error values? or am I asking the impossible. Again thanks
for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup, index, match, range, oh my... 1
Return Totals for multiple categories from a subreport 4
PivotTable Grand Totals By Item 4
Monthly Data Analysis 2
Sumif w/ relative column reference 3
Looking Up the Answer by Row and Column 6
How to filter merged cells to get more than the first row 1
Look up based on Column and Row headings 4

Top