Ignoring blank cells in results

Occasional Contributor

Ignoring blank cells in results

Hi All, I've attached a test sheet based upon our food bank worksheet. We have over 300 families in the rows and over a year of weekly food deliveries in the columns. I can generate results using IF, as on the test sheet (Ive also used vlookup and hlookup). With small numbers of rows and columns this is OK but what i need to do is produce reports of who was fed on a particular date - but without all the blank lines of families that were not fed and similarly for each family a list of the weeks they were fed with out all the blanks of weeks they were not fed. Thanks in advance.

6 Replies

Re: Ignoring blank cells in results

Try this:

=FILTER(\$C\$7:\$C\$13,OFFSET(\$C\$7,0,XMATCH(F27,\$D\$6:\$I\$6,0),7,1)="Food")

Re: Ignoring blank cells in results

Hi Patrick, thank you so much for your help. Your suggestion works perfectly on the columns, so I can get a report based on selecting a date. I have tried to modify your solution to search horizontally but failed misserably. What I would like to be able to do is request a list of dates that say family 'b' were fed. Please can you help me again. Thanks Simon.

Re: Ignoring blank cells in results

Try this one:
=FILTER(\$D\$6:\$I\$6,OFFSET(\$D\$6,XMATCH(C29,\$C\$7:\$C\$13,0),0,1,6)="Food")

C29 is the cell containing the family letter.

Re: Ignoring blank cells in results

Hi again Patrick, thanks so much again, this also worked perfectly on my little test page. For some reason when i rewrite both these on the actual sheet the lists produced are not correct, I've obviuosly made a mistake somewhere but cant see it at the moment!

Re: Ignoring blank cells in results

Is it off by 1 row? Possibly a different arrangement with the header?

Re: Ignoring blank cells in results

Again, THANKS! there were other columns with phone numbers etc. between the reference and the first date. Both reports working perfectly now. Thanks again!