+ Reply to Thread
Results 1 to 8 of 8

How to automatically eliminate blank rows from list when formula is dragged in tabularform

  1. #1
    Forum Contributor
    Join Date
    01-07-2014
    Location
    chandigarh
    MS-Off Ver
    Excel 2010
    Posts
    122

    How to automatically eliminate blank rows from list when formula is dragged in tabularform

    PROBLEM 2.png
    Hi Friends,
    Warm Greeting,

    I am attaching snapshot & Excel of the problem file please have a look at it & kindly suggest me a suitable solution for it. different & new ideas are most welcome

    Thanks in Advance to the reader of this forum
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to automatically eliminate blank rows from list when formula is dragged in tabular

    In G3 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

    =IFERROR(INDEX(A$3:A$18,SMALL(IF($C$3:$C$18="Male",ROW($C$3:$C$18)-MIN(ROW($C$3:$C$18))+1),ROW(1:1))),"")

    In K3 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

    =IFERROR(INDEX(A$3:A$18,SMALL(IF($C$3:$C$18="Female",ROW($C$3:$C$18)-MIN(ROW($C$3:$C$18))+1),ROW(1:1))),"")

    Drag both the formulas down and right...

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: How to automatically eliminate blank rows from list when formula is dragged in tabular

    Right Click on the sheet name at the bottom of excel and select view code to edit my code.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 02-01-2014 at 01:11 AM.

  4. #4
    Forum Contributor
    Join Date
    01-07-2014
    Location
    chandigarh
    MS-Off Ver
    Excel 2010
    Posts
    122

    Re: How to automatically eliminate blank rows from list when formula is dragged in tabular

    Excellent, Perfect & outstanding
    everyone in my office gave up even the trainers of microsoft excel recomended me manual filters but I knew that although its tough but can be done now I will show them how was it done & how "SIXTHSENSE" did what was impossible for them.
    You are an outstanding dear Thanks Thanks Thanks...
    Quote Originally Posted by :) Sixthsense :) View Post
    In G3 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

    =IFERROR(INDEX(A$3:A$18,SMALL(IF($C$3:$C$18="Male",ROW($C$3:$C$18)-MIN(ROW($C$3:$C$18))+1),ROW(1:1))),"")

    In K3 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

    =IFERROR(INDEX(A$3:A$18,SMALL(IF($C$3:$C$18="Female",ROW($C$3:$C$18)-MIN(ROW($C$3:$C$18))+1),ROW(1:1))),"")

    Drag both the formulas down and right...

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to automatically eliminate blank rows from list when formula is dragged in tabular

    You're Welcome

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  6. #6
    Forum Contributor
    Join Date
    01-07-2014
    Location
    chandigarh
    MS-Off Ver
    Excel 2010
    Posts
    122

    Re: How to automatically eliminate blank rows from list when formula is dragged in tabular

    Hi mehmetcik
    Thanks for your efforts & time you really worked a lot in creating the requisite codes but your created codes are not properly working in TOTAL FEMALE table as it is not giving me the required result, for more clarification, I want to create the same result table in other sheet but if female records will be absent than my purpose will get defeated for making this sheet.
    I still thank you for your efforts..

  7. #7
    Forum Contributor
    Join Date
    01-07-2014
    Location
    chandigarh
    MS-Off Ver
    Excel 2010
    Posts
    122

    Re: How to automatically eliminate blank rows from list when formula is dragged in tabular

    Hi mehmetcik
    Thanks for your efforts & time you really worked a lot in creating the requisite codes but your created codes are not properly working in TOTAL FEMALE table as it is not giving me the required result, for more clarification, I want to create the same result table in other sheet but if female records will be absent than my purpose will get defeated for making this sheet.
    I still thank you for your efforts..
    Quote Originally Posted by warriorpoet7176 View Post
    Excellent, Perfect & outstanding
    everyone in my office gave up even the trainers of microsoft excel recomended me manual filters but I knew that although its tough but can be done now I will show them how was it done & how "SIXTHSENSE" did what was impossible for them.
    You are an outstanding dear Thanks Thanks Thanks...

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: How to automatically eliminate blank rows from list when formula is dragged in tabular

    You won't get surprised this much if you have seen this thread already.

    In the below link another member of this forum shared all the stuffs which he learned till now...

    Have a look in the first post of the below link

    http://www.excelforum.com/tips-and-t...e-learned.html

+ 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] Offset function across columns when dragged down rather than rows.
    By Xiophoid in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-03-2013, 05:33 PM
  2. average formula for non contiguous range and eliminate blank cells
    By kkotter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2013, 10:13 AM
  3. Eliminate blank rows by shifting rows up
    By jman0707 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-06-2008, 02:10 AM
  4. Replies: 2
    Last Post: 04-19-2005, 12:06 PM
  5. Eliminate creating list that returns blank cells
    By Marc Todd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-26-2005, 06: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