+ Reply to Thread
Results 1 to 4 of 4

Consolidate list of only cells with positive values

  1. #1
    Registered User
    Join Date
    07-13-2015
    Location
    United States
    MS-Off Ver
    2003
    Posts
    54

    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. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,100

    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. #3
    Registered User
    Join Date
    03-27-2018
    Location
    Macon, Ga
    MS-Off Ver
    2010
    Posts
    6

    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. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,100

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-12-2017, 01:19 PM
  2. Replies: 5
    Last Post: 01-30-2017, 10:16 AM
  3. How to consolidate values in cells in order and remove duplicate?
    By london7871 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-23-2016, 11:33 PM
  4. Consolidate list and returning corresponding values
    By Robsheep in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-23-2015, 09:20 AM
  5. Macro that consolidate values from cells basing on conditions
    By golomb123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2014, 09:21 AM
  6. Averaging every 48 cells of only positive values
    By clideson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2013, 05:10 PM
  7. Consolidate values from multiple cells
    By jasonmcbride in forum Excel General
    Replies: 2
    Last Post: 10-28-2008, 06:34 PM

Bookmarks

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