+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    11-26-2013
    Location
    Jackson, Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    29

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    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:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Registered User
    Join Date
    11-26-2013
    Location
    Jackson, Wyoming
    MS-Off Ver
    Excel 2010
    Posts
    29

    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. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

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

    Glad to help, good luck!

+ 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. Replies: 8
    Last Post: 01-17-2018, 10:53 AM
  2. VBA code for Copy paste multiple tables skipping blank in to another worksheet
    By ripan81 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2015, 06:25 PM
  3. [SOLVED] How to copy formula skipping multiple rows
    By mgoutam in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2014, 09:19 AM
  4. Replies: 14
    Last Post: 01-19-2014, 05:42 AM
  5. Copy Formula from set cells, paste in blank rows below as value
    By cappie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2013, 02:45 PM
  6. [SOLVED] Paste formula down a column (While skipping rows)
    By daivd_vachon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-20-2012, 05:10 PM
  7. Copy Paste Formula on Each Blank Rows
    By triaji in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-26-2010, 10:53 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1