+ Reply to Thread
Results 1 to 19 of 19

Copy rows from two sheets to another based on criteria

  1. #1
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Copy rows from two sheets to another based on criteria

    Hi

    I have two sheets with the exact same column headers and data that changes constantly. I need a formula that will "search" both sheets for a specific word in one of the columns and copy the rows that has that word to the third sheet.

    Dont know if i'm explianing it correctly.... but how it should work is, if i type the word into "A1" of the third sheet, all the rows that contain that word in the specified column in sheet1 & 2 must "copy" to the third sheet from "A3" down.

    Example : (criteria = hi
    Sheet1 Sheet2 Sheet3
    Column1 Column2 Column3 Column1 Column2 Column3 Column1 Column2 Column3
    1 hi 2 3 ho 4 1 hi 2
    5 ho 6 7 hi 8 7 hi 8

    I don't know if this will have any bearing, but the actual sheets are 9 columns and the data goes as far as row 5000 in each of the two "search" sheets.

    Any help will be greatly appreciated.

  2. #2
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    See attached example sheet, the result i need is on sheet3 with discriptions.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Copy rows from two sheets to another based on criteria

    Here is an attempt which seems to work. Not entirely robust solution since there is a -1 in the formula to compensate for the columns starting at different rows.

    As you may notice, there are some array formulas in there which needs to be entered with Ctrl + Shift + Enter.
    Attached Files Attached Files

  4. #4
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    this is perfect! tahnks for your efforts!

    when on of the criteria is aken away in one of the sheets and no longer fits the criteria it shows a ref error. is there a way to display that ref error as an empty cell?

  5. #5
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    Never mind..... ID ten T moment. i can use conditional formatting to "hide" the ref error.

    thanks again Jacc. 1million rep coming our way

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Copy rows from two sheets to another based on criteria

    Glad to hear it works! And I appreciate the million reps, I always thougth I was a little behind on those.

  7. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Copy rows from two sheets to another based on criteria

    Ref error? That sounds like you have been deleting columns or something. Better get that sorted, it shouldn't be a problem. Post a workbook with a small sample or mockup data.

  8. #8
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Copy rows from two sheets to another based on criteria

    I can only assume that you have deleted a column in one of the source datasheets and this formula does not handle that well. I improved the simple dataretrieving formulas to go look for the columns with the correct heading so they are ok now. The problem is making the formulas in columns "Sheet" and "Row index" smart. I'm thinking helper cells that finds the correct columns first will be the way to go. Just in case I implemented that too. It may be overkill for you but it is a robust solution. You can change the name of the columns both in the source data sheets and in the master data sheet and the data will still end up in the correct column (at least that is the idea ). You still have to keep track of the overall datarange though and change that in the formulas as needed but it doesn't have to be exact as long as the upper left cell is correct and the is equal to or bigger than it needs to be.
    Last edited by Jacc; 06-08-2014 at 08:55 AM.

  9. #9
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Copy rows from two sheets to another based on criteria

    In the row index column (col K in the last sheet) in Jacc's workbook below you can use the below formula (confirm it as an Array formula by pressing Ctrl+Shift+Enter) to remove #REF! error.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  10. #10
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Copy rows from two sheets to another based on criteria

    Ooops! It seems like the workbook I just posted has calculations set to Manual. Sorry for any inconvinience!
    Nice with formula that includes errror handling, Sarang.

  11. #11
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    Hi

    Sorry i took so long to respond

    Thanks for all the help so far.

    Try as i might, i cannot get this to work properly in my workbook, although it did exactly what i wanted in the first sheet you sent.

    I have attached the actual workbook that i will be using. sheets "EXISING CLIENTS" & "PC CLIENTS" cannot be changed except for adding data i.e. columns cannot be added as this affects the userform.

    Your formulas should do their work in "Sheet1".

    The idea with the formulas you provide is to be able to type a specific suburb into textbox2 on the userform, in turn, the userform will paste the suburb name into "Sheet1", this must then be used as a criteria for your formulas which then basically "extracts" the rows that contain said suburb into "Sheet1" where i will be able to make it display in the userform.

    The data entry sheets "EXISING CLIENTS" & "PC CLIENTS" will go to about row 5000 in each sheet and in "Sheet1" i need at least 50 records to show for the given criteria.

    I hope i am explaining this corectly and my apologies if it's a bit long winded

    yours truly
    excel DumbAss
    Attached Files Attached Files

  12. #12
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    Bump..................

  13. #13
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    and bunp again......

  14. #14
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Copy rows from two sheets to another based on criteria

    Ok try this. I can see why you had some problems adapting my formulas. For starters the ' ' needed to be included since your sheet names have space in them. And then there was a whole lot of static stuff to adapt. Changing the range was such a pain I just had to put it in a separate cell. I think it's well adapted for your needs now but I have done very little testing. You have some test work to do!
    Attached Files Attached Files

  15. #15
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    gr8 thanks. will test the sweet cr@p out of it and get back to you.

  16. #16
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    "You must spread some Reputation around before giving it to Jacc again." well...... THAT SUCKS. looks like i'll have to give the million reps in slooooooow increments, the site does not allow me to give it all at once.

    I cannot enable editing nat the moment on the sheet, work PC , so i'll test it tonight.

    Thank you very much for your patience and all the help.

  17. #17
    Forum Contributor ANDREAAS's Avatar
    Join Date
    05-06-2013
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    368

    Re: Copy rows from two sheets to another based on criteria

    Please Login or Register  to view this content.
    So, it looks like i'll have to give that million rep in sloooooooow increments lol
    unfortunately i'm at work so the sheet won't enable editing, so i'll have to wait 'till tonight to test it. for now i'll mark this as solved (taken up enough of your time with this )
    i'll PM you with how it went.

    ......and thanks again for all the help.

  18. #18
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Copy rows from two sheets to another based on criteria

    Your are welcome!

  19. #19
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Copy rows from two sheets to another based on criteria

    I took a break and looked over the sheet again, cycling through AA, BB etc. Indeed there was a nasty bug! Gave it a good spray with Tabard and now it seems to be gone.
    Attached Files Attached Files

+ 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. read sheets of a workbook and then copy rows from each based on criteria
    By mada111 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2013, 08:19 AM
  2. Copy rows to 2 sheets using criteria
    By moroformat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 11:49 PM
  3. Macro to copy rows based on column criteria to a new sheets
    By Scott821 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2012, 01:55 AM
  4. Copy Rows From Two Sheets Into Master Based On Criteria
    By longstrider in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-28-2012, 05:17 PM
  5. Copy rows from master sheet to other sheets based on criteria
    By spikeysas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-07-2011, 04:28 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