# Consolidate list of only cells with positive values

1. ## Consolidate list of only cells with positive values

I'll try to explain this as simply as possible:

I have a calculator that creates corresponding values in three columns, for many rows long. Meaning that the calculator may do this:

0.5g Gypsum Mash
1.2g Epsom Mash
15mL PhosAcid Mash
0 0 0
1.1g CaCl2 Sparge

The 0 0 0 is so set because I made the calculator show zero on all columns if the first column was null.

What I'd like to do is have a new table that points to this one, but omits any of the rows containing zeros.

2. ## Re: Consolidate list of only cells with positive values

Maybe something like this:

``Please Login or Register  to view this content.``
This will copy all the rows that do not contain 0 in column A in Sheet1 to Sheet2. The line marked in green can be used if you want to remove them from Sheet1. Remove it if not necessary

3. ## Re: Consolidate list of only cells with positive values

I have never done macros or VBA, so I am struggling with a question: I have a table as shown. Each day, a new column is filled in (for instance E2:E6 for today). For any product that sold, how do i get it to tell me so down in A11:A15. Then, when the next column gets data (tomorrow's data in column F), how do i get A11:A15 to go get the latest data from column F?

Excel Question.JPG

4. ## Re: Consolidate list of only cells with positive values

See attached based on your example. In:

A8: YES
A9: =TODAY()
A12 and drag down: =IFERROR(INDEX(\$A\$2:\$A\$6, SMALL(IF(COUNTIF(\$A\$8, \$B\$2:\$H\$6)*COUNTIF(\$A\$9, \$B\$1:\$H\$1), ROW(\$A\$2:\$A\$6)-MIN(ROW(\$A\$2:\$A\$6))+1), ROW(A1)), COLUMN(A1)),"")

Last one is an array formula, meaning that after inserting/writing you will have to press Ctrl+Shift+Enter simultaneously instead of regular Enter

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1