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

1. ## 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

2. ## 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.

3. ## 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.``

4. ## 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...
Originally Posted by :) Sixthsense :)
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. ## 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. ## 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. ## 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..
Originally Posted by warriorpoet7176
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. ## 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

#### Thread Information

##### Users Browsing this Thread

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

#### 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