+ Reply to Thread
Results 1 to 15 of 15

Refrence column, but exclude blank rows

  1. 06-07-2018, 10:09 AM #1
    HereComesTheBoom
    HereComesTheBoom is offline
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Refrence column, but exclude blank rows

    I have data from F4:F34, I but some of the rows are sometimes empty.
    I want to show them up without the blank rows in column E... but I can't seem to figure it out? Any guides?

    I need it to be dynamic, so can't just insert special - F column keeps changing and I need E to correct itself to the same data without the blank rows
    Last edited by HereComesTheBoom; 06-08-2018 at 04:59 AM.
    Reply With QuoteRegister To Reply
  2. 06-07-2018, 10:23 AM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Refrence column, but exclude blank rows

    Hi Boom,

    Try this:

    =SUMIF($E$4:$E$34,"<>",$F$4:$F$34)

    Explained at:

    https://exceljet.net/formula/sum-if-not-blank
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 06-07-2018, 10:27 AM #3
    HereComesTheBoom
    HereComesTheBoom is offline
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Refrence column, but exclude blank rows

    Quote Originally Posted by MarvinP View Post
    Hi Boom,

    Try this:

    =SUMIF($E$4:$E$34,"<>",$F$4:$F$34)

    Explained at:

    https://exceljet.net/formula/sum-if-not-blank
    No

    I've got 31 rows, some have a number in them, some are blank. In column E, I want to show this data without all the empty rows, so if there is F is only data in 15 rows, I want E to only populate the first 15 rows.

    Like this
    https://www.excelforum.com/attachmen...1&d=1528381794

    But be dynamic, so when I change F, they'll just fall into place
    Attached Files Attached Files
    Last edited by HereComesTheBoom; 06-07-2018 at 10:30 AM.
    Reply With QuoteRegister To Reply
  4. 06-07-2018, 10:46 AM #4
    CK76
    CK76 is online now
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Refrence column, but exclude blank rows

    You mean something like...
    In E4: Confirmed as Array (CTRL + SHFT + ENTER)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Copy down.
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein
    Reply With QuoteRegister To Reply
  5. 06-07-2018, 10:46 AM #5
    chief_abound
    chief_abound is offline
    Forum Contributor
    Join Date
    09-18-2015
    Location
    Republic of Korea
    MS-Off Ver
    2010
    Posts
    314

    Re: Refrence column, but exclude blank rows

    try this

    E4 --> =IFERROR(INDEX($F:$F,1/LARGE(INDEX(($F$4:$F$34<>"")/ROW($F$4:$F$34),),ROWS($F$4:F4))),"")
    Reply With QuoteRegister To Reply
  6. 06-07-2018, 07:30 PM #6
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Refrence column, but exclude blank rows

    Sorry,

    I thought you just wanted to sum the values and not compact the data into less rows. My bad Read your question again and I didn't see the compact the number of rows, just the sum part.
    Reply With QuoteRegister To Reply
  7. 06-07-2018, 09:20 PM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Refrence column, but exclude blank rows

    The "sum them up" part isn't apparent in upload.

    Another way to gather them similar to the others.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    And then sum those up (in row 3?).
    Dave
    Reply With QuoteRegister To Reply
  8. 06-08-2018, 02:45 AM #8
    HereComesTheBoom
    HereComesTheBoom is offline
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Refrence column, but exclude blank rows

    Quote Originally Posted by CK76 View Post
    You mean something like...
    In E4: Confirmed as Array (CTRL + SHFT + ENTER)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Copy down.
    It doesn't work in the test file - i downloaded yours, and added a 2 to row 24, nothing happens.

    ----
    @flame I don't need no sum what so ever, I just need to dynamacly in E show the data from F - without the gaps.

    I just need to have it to act like in the test file, but not permanently, so when the data in F is changed, it just changes in E
    Last edited by HereComesTheBoom; 06-08-2018 at 05:21 AM.
    Reply With QuoteRegister To Reply
  9. 06-08-2018, 07:59 AM #9
    CK76
    CK76 is online now
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Refrence column, but exclude blank rows

    i downloaded yours, and added a 2 to row 24, nothing happens.
    Works fine on my end. Make sure that you have Calculation mode set to Auto under "Formulas" tab.

    Any of the formulas posted by chief_abound, FlameRetired or myself will work fine with your sample.

    See attached, I've added all 3 variation.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 06-08-2018, 08:41 AM #10
    HereComesTheBoom
    HereComesTheBoom is offline
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Refrence column, but exclude blank rows

    Quote Originally Posted by CK76 View Post
    Works fine on my end. Make sure that you have Calculation mode set to Auto under "Formulas" tab.

    Any of the formulas posted by chief_abound, FlameRetired or myself will work fine with your sample.

    See attached, I've added all 3 variation.
    facepalm... thanks a bunch!!

    Could the same be done in the other direction? Here I need D4:D34 to go to D49:D79 - was hoping I could read it from the first one, but it's simply to complex to my skill-level...
    Reply With QuoteRegister To Reply
  11. 06-08-2018, 09:14 AM #11
    CK76
    CK76 is online now
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Refrence column, but exclude blank rows

    I'm not sure what you mean?

    Is it that you have static values in D4:D34 range and you want to extract to D49:D79 without blanks?

    Then it would be something like...
    In D49: Confirmed as Array (CTRL + SHFT + ENTER)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Copy down.
    Reply With QuoteRegister To Reply
  12. 06-08-2018, 09:31 AM #12
    HereComesTheBoom
    HereComesTheBoom is offline
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Refrence column, but exclude blank rows

    Quote Originally Posted by CK76 View Post
    I'm not sure what you mean?

    Is it that you have static values in D4:D34 range and you want to extract to D49:D79 without blanks?

    Then it would be something like...
    In D49: Confirmed as Array (CTRL + SHFT + ENTER)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Copy down.
    Exactly, this gives me a #num though and all im doing is re-writting it to other lang
    =INDEKS($D$4:$D$34;MINDSTE(HVIS($D$4:$D$34<>"";RĘKKE($D$4:$D$34)-3);RĘKKER($A$1:A1)))
    Reply With QuoteRegister To Reply
  13. 06-08-2018, 09:39 AM #13
    CK76
    CK76 is online now
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,887

    Re: Refrence column, but exclude blank rows

    Confirm it as array function. Once the formula is entered in the cell, without leaving the cell edit mode. Hit CTRL + SHIFT + ENTER.

    You will see curly brackets before and after formula.
    Reply With QuoteRegister To Reply
  14. 06-08-2018, 09:57 AM #14
    HereComesTheBoom
    HereComesTheBoom is offline
    Forum Contributor
    Join Date
    04-20-2017
    Location
    SWEDEN
    MS-Off Ver
    2016
    Posts
    123

    Re: Refrence column, but exclude blank rows

    Quote Originally Posted by CK76 View Post
    Confirm it as array function. Once the formula is entered in the cell, without leaving the cell edit mode. Hit CTRL + SHIFT + ENTER.

    You will see curly brackets before and after formula.
    It works untill it runs out of numbers

    https://www.excelforum.com/attachmen...1&d=1528466220
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 06-08-2018, 10:20 AM #15
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Refrence column, but exclude blank rows

    Put this in D40

    =IFERROR(INDEX($D$4:$D$34,SMALL(IF($D$4:$D$34<>"",ROW($D$4:$D$34)-3),ROWS($A$1:A1))),"")

    Enter with the CSE confirm and pull it down. That will get rid of those errors.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Exclude Blank Rows With Array
    By billgyrotech in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-15-2016, 11:25 AM
  2. [SOLVED] IF Sumproduct function in excel to exclude blank rows
    By newbie4 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2015, 12:22 AM
  3. Exclude rows in chart where blank values are present in a certain column
    By smockpuv in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-16-2013, 09:52 AM
  4. Exclude blank rows from CSV file
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-05-2011, 10:52 AM
  5. Set Print Area Macro - Exclude Blank Rows
    By shudder in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-27-2009, 10:31 PM
  6. Sort Macro to Exclude Blank Rows?
    By ScottPcola in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-05-2006, 03:15 PM
  7. Copy rows, but exclude blank rows
    By Mary in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-01-2005, 04:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1