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.