Linking Data and Ignoring Zero values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been struggling with this issue for a couple of days now....

In one spreadsheet there is a list of daily production data. Each day another value is added at the bottom of the column. Some days there is no production, so a zero value is entered.

This data is copied and pasted into another sheet, however in this instance and zero value cells are removed (manually) so the list only shows days there was production. So you end up with a column that is shorter than the original data column.

What I am trying to do is to 'automate' this process and for the second 'adjusted' column to be linked to the original data, so that each day as a new production values is entered on one sheet, the value also appears at the bottom of the adjusted column, as long as there was production that day.

I have tried using VLOOKUP and combinations of OFFSET, MATCH and COUNTIF. However, the problem is that the argument criteria for those functions needs to be a certain value, when I want to include anything >0.

I would sincerely appreciate anyone's suggestions.

Steve Howe
 
Hi Steve
Assumptions:
-sheet1 contains your original data
- row 1 is a heading row
- column A is the production date
- column B the value
- sheet2 should contain the 'filtered' list
- also row 1 is a heading row

Enter the following array formulas (entered with CTRL+SHIFT+ENTER):
A2:
=INDEX('sheet1'!$A$1:$A$10000,SMALL(IF('sheet1'!$B$2:$B$10000<>0,ROW('s
heet1'!$B$2:$B$10000)),ROW(1:1)))

B2:
=INDEX('sheet1'!$B$1:$B$10000,SMALL(IF('sheet1'!$B$2:$B$10000<>0,ROW('s
heet1'!$B$2:$B$10000)),ROW(1:1)))
and copy both formulas down

But you may also consider using 'Data - filter - Advanced Filter' to
create such a filtered list. Record a macro whild doing this manually
and use this macro to create a new list
 
Hi Frank!

Your suggestion works great - Thank you. Really!

I have never used arrays before. One aspect of your solution is that the cells in the filtered column that contain the formula but have yet to be populated with filtered data have the #NUM error message showing. If thought that I could add an IF statement to give a blank output if that is the case. For example, in written words:

=IF(filter_array_formula='#NUM',"",filter_array_formula)

I tried enclosing the array references in the curly brackets, but excel does not seem to like this.

Thanks Again

Steve
 
Hi
try using the following formula
=IF(ISERROR(INDEX('sheet1'!$A$1:$A$10000,SMALL(IF('sheet1'!$B$2:$B$1000
0<>0,ROW('s
heet1'!$B$2:$B$10000)),ROW(1:1)))),"",INDEX('sheet1'!$A$1:$A$10000,SMAL
L(IF('sheet1'!$B$2:$B$10000<>0,ROW('s
heet1'!$B$2:$B$10000)),ROW(1:1))))
also entered with CTRL+SHIFT+ENTER

for array formulas see:
http://www.cpearson.com/excel/array.htm
 
Frank,

Thank you - your suggestion works perfectly!

I've seen the number of contributions you have made to this site, you obviously put a lot of effort in to it.

Thanks again
Steve
 
Back
Top