Pull data only if entries present

[Mogogo]

Not even sure how to phrase this question but here goes.

I am building a report that pulls data from another worksheet. Source data might look like this:
John Smith 6 5 3 2
Mary May 0 0 0 0
Mike March 4 0 1 0
Lisa July 0 0 0 0

This could go on for dozens of rows.

I want the target cells to be presented as:
John Smith 6 5 3 2
Mike March 4 0 1 0

In other words I want it to search for a value >0 in columns 2-5 and if present then include the entries. If values are all zero then skip to next row. I hope this is clear enough. Thanks in advance for any help.

Excel 2007, Windows 7

 

[Andrew Fergus]

Hi

This would be easier if we had a helper column. Is it an option to add a new 6th column (or insert a new 2nd column) that contained a sum of the other columns?

Andrew

 

[Mogogo]

Could definitely add whatever columns or cells that would be needed. Could bring the data into a hidden worksheet and just display what I want if its easier.

 

[Andrew Fergus]

I have used the following layout:

  A          B  C  D  E  F   G  H        I          J     K     L     M     
1 Name       f1 f2 f3 f4 Sum    1        Name       f1    f2    f3    f4    
2 John Smith 6  5  3  2  16     2        John Smith 6     5     3     2     
3 Mary May   0  0  0  0  0      4        Mike March 4     0     1     0     
4 Mike March 4  0  1  0  5      6        Fred J     0     0     0     1     
5 Lisa July  0  0  0  0  0      7        Andrew F   1     0     0     0     
6 Fred J     0  0  0  1  1      8        Jane Smith 0     9     0     0     
7 Andrew F   1  0  0  0  1      3.87E+08 #REF!      #REF! #REF! #REF! #REF! 
8 Jane Smith 0  9  0  0  9                                                  
9 Ignore Me  0  0  0  0  0                                                  
Sheet2
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
F2:F9 =SUM(B2:E2)
H2:H7 {=MIN(IF(ROW($A$1:$A$30)>H1,IF($F$1:$F$30>0,ROW($A$1:$A$30),9^9),9^9))}
I2:M7 =INDEX(A:A,$H2)
{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down
[Table-It] version 09 by Erik Van Geit

It was lazy of me to not code for the errors but it gives you an idea of how you could get this to work. Columns A:F have the data, columns H:M contain the results. The key to this is the array formula in column H which finds the relevant row - notice I have hard-coded the value 1 in cell H1 - this number represents the last row number of the heading (in other words the data starts at row 2 without the heading).

You will probbaly want to extend the range of the formula in column H to more than 30 rows (this will need to be done in 3 places in the formula). You could also introduce an error test in the formulas in columns I to M.

Cheers
Andrew