+ Reply to Thread
Results 1 to 9 of 9

Copy rows from one sheet to another based on text in one cell.

  1. #1
    Registered User
    Join Date
    01-02-2020
    Location
    Tacoma, WA
    MS-Off Ver
    Office 365
    Posts
    7

    Copy rows from one sheet to another based on text in one cell.

    I have a workbook that has multiple spreadsheets. I would like to copy ROWS from the different spreadsheets to a master sheet if they have any text in column E starting with row 3 (row 1 & 2 are headings).

    For example, spreadsheet named SoundView has 100 rows in it with data in the first three columns. Only 25 rows have data in cell E (this is the column for last name). I want those 25 rows to be copied over to the master then I want to check the spreadsheet named Graham for the same thing, then I want to check the spreadsheet named Puyallup, etc. Unfortunately, we are not allowed to use macros here. I have attached a test spreadsheet for an example. The master is blank. Is it possible to copy the rows in each spreadsheet with data in Column E to the master sheet using a formula?
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Need to copy rows from one sheet to another based on whether there is text in one cell

    EDIT: Sorry, I missed the part about not being able to use VBA. Maybe this will be of benefit to someone else. Be patient, we have some really good formula people here that may jump in.

    Here is a VBA solution:
    Please Login or Register  to view this content.
    How to install your new code
    • Copy the Excel VBA code
    • Select the workbook in which you want to store the Excel VBA code
    • Press Alt+F11 to open the Visual Basic Editor
    • Choose Insert > Module
    • Edit > Paste the macro into the module that appeared
    • Close the VBEditor
    • Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    To run the Excel VBA code:
    • Press Alt-F8 to open the macro list
    • Select a macro in the list
    • Click the Run button

    Work smarter! Not harder!
    Last edited by alansidman; 01-02-2020 at 06:04 PM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    01-02-2020
    Location
    Tacoma, WA
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Need to copy rows from one sheet to another based on whether there is text in one cell

    For those who can use macros - that worked like a charm! Since I cannot, I look forward to seeing if a formula can do this. Thank you so much, Alan!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need to copy rows from one sheet to another based on whether there is text in one cell

    You could do it using formulae. How many subsidiary sheets are you likely to have in your real file? Your attached file shows 3.

    Is the criteria just that column E is not empty?

    Pete

  5. #5
    Registered User
    Join Date
    01-02-2020
    Location
    Tacoma, WA
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Need to copy rows from one sheet to another based on whether there is text in one cell

    Hi Pete,

    There are 9 sheets. and yes, the only criteria is that E not be empty. Currently we copy and paste the data from each spreadsheet into a master sheet then filter/sort.

    Thank you so much for your consideration.

    Julie

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need to copy rows from one sheet to another based on whether there is text in one cell

    I've set this up for you in the attached file. The basic idea is to identify the records which match the criteria in each subsidiary sheet, and to allocate some unique code to them. Then in the Master sheet you can regenerate the code to find the sheet and the row in that sheet where a particular record occurs, and bring that record across. The "unique code" that I have used is a simple sequential number, which propagates from one sheet to the next automatically.

    So, in each of the subsidiary sheets I have used column M for this code, with this formula in M3 of each sheet:

    =IF(E3<>"",MAX(M$2:M2)+1,"-")

    and this is copied down as appropriate to the bottom of your data - the hyphens help to show where the formula is active. It generates sequential numbering for those records where column E is not blank, using cell M2 as the starting value. In the first sheet (Soundview), M2 is set to zero, but in order to propagate the numbers from one sheet to the next, M2 in the second sheet (Graham) contains this formula:

    =MAX(SoundView!M:M)

    and in the third sheet (Puyallup) it contains this:

    =MAX(Graham!M:M)

    so that the starting point in each sheet is where the previous sheet had got up to. You will need to do this for the other 6 sheets in your real file.

    In the Master sheet I have set up a small table in columns Q and R (shaded blue) where I have listed the names of the subsidiary sheets - only 3 in this example, but you will need to list all 9 of your sheets here in your real file. These should be in the order that the numbers occur. I have put zero in cell R1 (important for the formulae which follow), and this formula in R2:

    =IF(Q2="","",MAX(INDIRECT("'"&Q2&"'!m:m")))

    and when this is copied down it shows the largest number that had been reached in each sheet. So, we just need a way of generating sequential numbers, and this is achieved by means of this term in many of the formulae which follow:

    ROWS($1:1)

    This returns 1 initially (there is 1 row between $1 and 1), but when this is copied down it becomes ROWS($1:2) (returning 2), then ROWS($1:3) (returning 3) etc. on successive rows. so we can make use of this in the following formula in N2 to retrieve the appropriate sheet name:

    =IF(ROWS($1:1)>MAX(R:R),"",INDEX(Q:Q,MATCH(ROWS($1:1)-1,R:R)+1))

    Similarly, this formula in O2:

    =IF(N2="","",MATCH(ROWS($1:1),INDIRECT("'"&N2&"'!m:m"),0))

    can be used to find the row in the sheet given in N2 where the appropriate record exists. I've copied these formulae down to row 15 in the example file, but in your real file you may need to copy them further (until you start to get blanks). Then we can start to retrieve the data using this formula in A2:

    =IF($N2="","",INDEX(INDIRECT("'"&$N2&"'!A:L"),$O2,COLUMNS($A:A)))

    This can then be copied across to L2, and then the block of formulae copied down as far as you need them (to row 15 in the example file).

    This is now fully automatic, in that if you add a new (4th) record to the first subsidiary sheet, say, all of the other records will be moved down on the Master sheet to make room for it.

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-02-2020
    Location
    Tacoma, WA
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Copy rows from one sheet to another based on text in one cell.

    This works like a charm. Thank you so much for the detailed explanation.

    I appreciate your hard work and your assistance, Pete.

    Gratefully,

    Julie

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Copy rows from one sheet to another based on text in one cell.

    Glad to help, Julie.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can show your appreciation directly and thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  9. #9
    Registered User
    Join Date
    01-02-2020
    Location
    Tacoma, WA
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Copy rows from one sheet to another based on text in one cell.

    Done and Done! Thanks again.

+ 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] Copy rows to new sheet based on value in a cell
    By ndtsteve in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-21-2019, 02:40 AM
  2. [SOLVED] Copy rows in sheet if certain cell contains text
    By CallumS in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-14-2013, 05:34 AM
  3. [SOLVED] copy rows to new sheet based on cell value
    By batchjb69 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-02-2013, 03:38 PM
  4. Copy rows to new sheet based on cell value
    By iseman002 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2013, 10:40 AM
  5. Replies: 3
    Last Post: 10-10-2011, 02:52 PM
  6. Copy rows based on cell's to another sheet.
    By the.root in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-11-2009, 04:31 AM
  7. Copy Rows from selected Sheet to New Sheet based on Cell Value
    By joemcmillen in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 04-03-2008, 03:44 PM

Tags for this Thread

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