# Formula to copy and paste multiple rows while skipping blank rows in the table

1. ## Formula to copy and paste multiple rows while skipping blank rows in the table

hi,

I am trying to figure out a formula that would copy rows from a table on a tab and post on another tab while skipping blank rows and neatly packing all the pasted data all together.

I have attached a sheet titled " Student Education Tracking". On the 'Combined Data' tab is aggregating all the data I want to copy. What I would like to happen is if a row in the 'Combined Data' sheet has information I want it to be copied and pasted on the 'Exam Summary' tab. When the rows are copied and pasted, I want the blank rows to be skipped and the pasted information to have no blank rows.

Ex. on the 'Combined Data' tab, Row 2,3,4,5,6 have data, and then there are blank rows until row 16. When copied and pasted onto the 'Exam Summary' tab, I want the formula to paste rows 2,3,4,5,6,16 in descending order without any blank rows.

I am also open to altering the spreadsheet to achieve these results. It just needs to be simple as the the people using the spreadsheet will have very limited understanding of excel.

2. ## Re: Formula to copy and paste multiple rows while skipping blank rows in the table

It looks like you were trying to make a helper column in column F of Combined Data - that was a good idea, it simplifies things a bit. I went with the following in F2, filled down:

=IF(SUMPRODUCT(--(LEN(A2:E2)>1))=0,"FALSE","TRUE")

With that helper in place, I used the formula below in Exam Summary, A2; the formula must be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

=IFERROR(INDEX('Combined Data'!\$A\$2:\$E\$91,SMALL(IF('Combined Data'!\$F\$2:\$F\$91="TRUE",ROW('Combined Data'!\$F\$2:\$F\$91)-ROW('Combined Data'!\$F\$2)+1),ROW(1:1)),MATCH(A\$2,'Combined Data'!\$A\$1:\$E\$1,0)),"")

Fill right and down through the rest of the table, and you should be all set. Your users shouldn't touch the 'Exam Summary' sheet; it should update automatically as data is entered elsewhere. Take a look at the attachment to see if I've understood the goal correctly:

3. ## Re: Formula to copy and paste multiple rows while skipping blank rows in the table

That solves my issues perfectly, thank you for the prompt and detailed explanation and solution

4. ## Re: Formula to copy and paste multiple rows while skipping blank rows in the table

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

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.05993 seconds
• Memory Usage 8,961KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (4)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (2)postbit_attachment
• (4)postbit_legacy
• (4)postbit_onlinestatus
• (4)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (49):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete