Hello all

Long time no post but I do still check in occasionally. I know there are some excel gurus on here so I thought I'd ask:

I have a spreadsheet which is used for data collection but it's way too big to see the useful data properly. I've been using filters to block out some of the data but now I have over 100 filters and I wondered if it would be possible to filter in another way. across several columns.

The easiest way to explain this is to mock up a simpler set of data:



My real spreadsheet does not involve monitoring fruit consumption (I'm not that mental).

At the moment I am filtering each column individually but this is why I have over 100 filters and growing.

I'd like to see what fruit a person eats in the whole week and just blank out the fruit rows that are not eaten.....

FILTER DAVE


FILTER MARY



I will then have far fewer filters and can cross-reference the data for a particular name.

I've tried using advanced filters and even tried using lookup formulas on a separate sheet to return the values I need, but cannot get either to work.

Any help will be greatly appreciated.

Matt

Have you tried using Pivot Tables? I've never used them but many people swear by them...

I've just tried that but every range I select it returns the error:

Ah, I did wonder if your data may not have been arranged in a pivot-table-friendly way...

Does your data really look like that though, with "names" and "days of the week" and "fruit" (which may be other things in real life) or is it more complex?

And if you filter using "DAVE" surely you should not get back any results for "MARY"? And for "DAVE" would you still want (blank) results returned for those days when no "fruit" was "eaten" (do you want to keep all 5 days in your result table)?

I've made up something that works depending on whether you enter DAVE or MARY but I've got a "helper" columns to count up non-blank rows for each "person" and another helper column to pick up the non-blank rows for the selected "person", and another "helper" cell to keep track of where in the table the entries for each "person" start. It's kind of clunky but works...

Hey Dave, I think you have it. I just added a helper column next to each name which counts the number of "fruit" and then I just create the filter with that column which filters out all the zero rows. I can even hide that column too.

I thought this would be too difficult to do easily, so thanks!

In my helper column I just showed the row number if there was any "fruit" eaten by that "person", so for DAVE, I might get

1
0
0
4
0

The results table has the same number of rows, but I use the SMALL function to pull off each non-zero row number.

Lets' say the above column is in cells A1:A5, then my results table uses this formula in the first row

=SMALL($A$1:$A$5,ROW(1:1)-ROW($1:$1)+1)

Then copy that down to the remaining rows, and you'll end up with

1
4
#NUM!
#NUM!
#NUM!

You can use IFERROR to hide the #NUM! results

Now you can use the short helper column to pull out the results just for those rows.

Hopefully that makes sense?

Thanks again Dave. I did the first bit the same and then used filters to just filter the extra column.

I will play around with the formula though as I'd quite like to hide the other "names" to tidy it up.

I like learning new formula - it's very satisfying when you get the results you want.