+ Reply to Thread
Results 1 to 10 of 10

Consolidate list of only cells with non-blank values

  1. #1
    Registered User
    Join Date
    03-27-2018
    Location
    Macon, Ga
    MS-Off Ver
    2010
    Posts
    6

    Consolidate list of only cells with non-blank 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? Can this be done with formulas?Excel Question.JPG
    Attached Files Attached Files
    Last edited by whittlemurf; 03-27-2018 at 02:37 PM.

  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 non-blank values

    Do not post multiple threads asking the same thing. A solution has been provided in the previous one. Thanks.

    EDIT: That one wasn't your post, so do not ask question in other thread. Even so, you will find a solution there.

  3. #3
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,353

    Re: Consolidate list of only cells with non-blank values

    In cell A11, array-enter (enter using Ctrl-Shift-Enter) the formula

    =IFERROR(INDEX($A:$A,SMALL(IF(INDEX($2:$6,,MAX(IF($B$2:$Z$6="YES",COLUMN($B$2:$Z$6))))="YES",ROW($A$2:$A$6)),ROW(A1))),"")

    and copy down to A12:A15. If your dates could eventually extend past column Z then change the Zs to AZs or BZs....
    Bernie Deitrick
    Excel MVP 2000-2010

  4. #4
    Registered User
    Join Date
    03-27-2018
    Location
    Macon, Ga
    MS-Off Ver
    2010
    Posts
    6

    Re: Consolidate list of only cells with non-blank values

    That's super - and easy. What if instead of "YES", the data in B2:Z6 was the actual product name (such as "banana")?Excel Question - 2.JPG

  5. #5
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,353

    Re: Consolidate list of only cells with non-blank values

    Try this, also array-entered:

    =IFERROR(INDEX($A:$A,SMALL(IF(INDEX($2:$6,,MAX(IF($B$2:$Z$6<>"",COLUMN($B$2:$Z$6))))<>"",ROW($A$2:$A$6)),ROW(A1))),"")

  6. #6
    Registered User
    Join Date
    03-27-2018
    Location
    Macon, Ga
    MS-Off Ver
    2010
    Posts
    6

    Re: Consolidate list of only cells with non-blank values

    That's works great. Thanks so much!!

  7. #7
    Registered User
    Join Date
    03-27-2018
    Location
    Macon, Ga
    MS-Off Ver
    2010
    Posts
    6

    Re: Consolidate list of only cells with non-blank values

    Bernie, I have tried adapting what you sent for use in my actual spreadsheet, but I'm unsuccessful so far. I have attached a copy of my spreadsheet with my actual data. It's a bit backwards, but I enter a "1" for the current month in FR36:FR58 if i own that particular investment.

    What i am trying to do is use your formula to consolidate that data for the current month into a list of the things i actually own (see the sample output down in A66:B70).

    The formula i am using is: =IFERROR(INDEX($C:$C,SMALL(IF(INDEX(36:59,,MAX(IF($D$36:$ZN$59<>"",COLUMN($D$36:$ZN$59))))<>"",ROW($C$36:$C$59)),ROW(C35))),"")

    Thanks for looking at this again!!Excel Question - 3.xlsxExcel Question - 3.JPG

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,353

    Re: Consolidate list of only cells with non-blank values

    The error that you made in adapting the formula was updating the cell reference passed to ROW( ) to C35 - that would return the 35th smallest row number that is filled in (which does not exist).

    Array-enter this instead (using Ctrl-Shift-Enter)

    =IFERROR(INDEX(B:B,SMALL(IF(INDEX($36:$59,,MAX(IF($D$36:$ZN$59<>"",COLUMN($D$36:$ZN$59))))<>"",ROW($C$36:$C$59)),ROW(A1))),"")

    Then copy down and across one column if you also want the values from column C (your example returned the values from B, not C) - your picture and your workbook differ as to which column's values are returned, so I went with your workbook.

  9. #9
    Registered User
    Join Date
    03-27-2018
    Location
    Macon, Ga
    MS-Off Ver
    2010
    Posts
    6

    Re: Consolidate list of only cells with non-blank values

    You are awesome - thanks a ton!

  10. #10
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,353

    Re: Consolidate list of only cells with non-blank values

    You're welcome - happy ot hear you got it all worked out!

+ 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. Consolidate list of only cells with positive values
    By seanppp in forum Excel General
    Replies: 3
    Last Post: 03-27-2018, 02:46 PM
  2. Replies: 1
    Last Post: 09-12-2017, 01:19 PM
  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. Consolidate values from multiple cells
    By jasonmcbride in forum Excel General
    Replies: 2
    Last Post: 10-28-2008, 06:34 PM
  7. Function to list values of last 3 non-blank cells in a vertical bl
    By ANJ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-13-2005, 08:06 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