+ Reply to Thread
Results 1 to 11 of 11

Consolidate rows by skipping zero values

  1. #1
    Registered User
    Join Date
    04-07-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    5

    Consolidate rows by skipping zero values

    Hello
    I'm an intermediate user and can usually google my way to a solution but this one has me stumped.
    I want to pull data into a spreadsheet that will be imported directly into software. Rows with $0 will create an error with the import. So what I'm looking to do is return values from an array only if the $ cell is >0. If it's not >0, I want it to move to the next row and so on until it finds a row with a $ value >0.

    For example, this table -

    table.PNG

    Would be consolidated to this -

    import.PNG

    The code, hours, and value are returned, but only for rows where the value >0, and there are no blank rows in the import.

    Thank you for your time!

    ETA: Using MS Office professional plus 2019
    Attached Images Attached Images
    Last edited by NatCon; 04-11-2023 at 10:27 PM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Consolidate rows by skipping zero values

    this would be one way... in F4 copied down until returns blank =IFERROR(INDEX(A:A,SMALL(IF($B$4:$B$8>0,ROW($B$4:$B$8)),ROWS($B$4:B4))),"")
    expand the range in col B as necessary.
    Then in col G this ... =IFERROR(INDEX(C:C,SMALL(IF($B$4:$B$8>0,ROW($B$4:$B$8)),ROWS($B$4:B4))),"")
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    3,030

    Re: Consolidate rows by skipping zero values

    Try this:

    =FILTER(A4:C7,C4:C7<>0,"None")

    (Edited)
    Or, if specifically greater than 0,

    =FILTER(A4:C7,C4:C7>0,"None")

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Consolidate rows by skipping zero values

    I completely missed the middle with my formula, so here would be my version =IFERROR(INDEX(B:B,SMALL(IF($B$4:$B$8>0,ROW($B$4:$B$8)),ROWS($B$4:B4))),"")

    Thanks Gregb for the alternative, it prompted me to realize that I missed the middle column. You know now that I have the newer version of excel I should think about some of the newer formulas more often like FILTER.

  5. #5
    Registered User
    Join Date
    04-07-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    5

    Re: Consolidate rows by skipping zero values

    Hi Greg,

    I'm using my work PC which has MS Office professional plus 2019, and it's not showing =FILTER as a valid function

  6. #6
    Registered User
    Join Date
    04-07-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    5

    Re: Consolidate rows by skipping zero values

    Hi Sam,

    Thanks for your reply! This works for the first row, but returns #NUM! for subsequent rows (I removed IFERROR to see why it was coming up blank). I've used your below addendum for the middle col too

    Attachment 825348

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    3,030

    Re: Consolidate rows by skipping zero values

    Yes, that's too bad (and in future, you should mention that in your post). Take a look at Sam's latest formula then.

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Consolidate rows by skipping zero values

    sorry your attachment is coming up as invalid. In some (maybe non office 365) versions of excel these formulas could be array formulas so have you tried to use it as an array? It isn't in my version but it could be in yours? Or try to upload the workbook again using the instructions at the top of the post.

  9. #9
    Registered User
    Join Date
    04-07-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    5

    Re: Consolidate rows by skipping zero values

    noted, i've amended my post

  10. #10
    Registered User
    Join Date
    04-07-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    5

    Re: Consolidate rows by skipping zero values

    Yep that was it! My bad for not including the version i'm working with in my original post. Entering it as an array formula seems to have done the trick. Thanks for your help!

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Consolidate rows by skipping zero values

    Great, glad you got it working. AND thank you for the rep!

+ 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. [SOLVED] How to consolidate rows with same values on columns plus sum values on other columns
    By elbarauljr in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-23-2021, 12:00 PM
  2. [SOLVED] How to consolidate duplicate rows and sum their values automatically?
    By rcurious in forum Excel General
    Replies: 1
    Last Post: 05-28-2020, 04:39 AM
  3. [SOLVED] consolidate cell values from same rows of many workbooks to single sheet
    By iamrahul in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-12-2020, 03:16 PM
  4. [SOLVED] Formula to copy and paste multiple rows while skipping blank rows in the table
    By teton88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-07-2018, 02:16 PM
  5. copy values from columns to rows while skipping empty columns...
    By EricCy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2016, 06:20 AM
  6. Replies: 14
    Last Post: 01-19-2014, 05:42 AM
  7. Auto-Incrementing values in a column while skipping specific rows
    By rgalang in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-18-2010, 02:48 AM

Tags for this Thread

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