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

Excel has a powerful feature called AutoFilter that does what you want.

http://www.contextures.com/xlautofilter01.html

Once it is set up, click on the pull-down and pick Custom not equal to 0
for any given month
--
Gary''s Student - gsnu200822


"Dom" wrote:

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

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!B20,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
---
"Dom" wrote:
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.

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

"Gary''s Student" wrote:

Excel has a powerful feature called AutoFilter that does what you want.

http://www.contextures.com/xlautofilter01.html

Once it is set up, click on the pull-down and pick Custom not equal to 0
for any given month
--
Gary''s Student - gsnu200822


"Dom" wrote:

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

Try my response
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

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.

"Max" wrote:

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!B20,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
---
"Dom" wrote:
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.

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!B30,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
---
"Dom" wrote:
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.

Thanks for your help with this

"Dom" wrote:

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

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dom" wrote in message
...
Thanks for your help with this

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.




"Max" wrote:

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dom" wrote in message
...
Thanks for your help with this

Have posted some thoughts in your new thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---