+ Reply to Thread
Results 1 to 17 of 17

Remove Rows based upon list of search terms, copy removed rows to new sheet

  1. #1
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Remove Rows based upon list of search terms, copy removed rows to new sheet

    Hi

    I will start by saying I don't know if this is possible to do but I feel some very smart person on here will have a solution for me

    What I want to do is the below

    Please see the sample Sheet attached

    I would like to have a list of terms (Shown on the Request sheet)

    I would like Excel to search those terms on a the sheet called Stock Sheet

    Upon finding a matching result I would like Excel to Copy the entire row which the result occurs on to a Results sheet (Shown as results sheet)

    and that row to be removed from the Stock Sheet. (Example shown on sheet Stock sheet results) I would not want it to create a new sheet I would want it to remove them from the original Stock Sheet to end up looking like (Stock Sheet Results)

    If it cannot locate a corresponding result, it simply skips that search term but leaves empty row in that result

    Basically its like the below


    Say this is my search terms


    Avatar: The Burning Earth (Xbox 360)
    Avatar: The Burning Earth (Xbox 360)
    Bakugan: Battle Brawlers (Xbox 360)
    Battle Fantasia (Xbox 360)
    Battle for the Pacific (Xbox 360)


    on my stock sheet they appear as this


    Bakugan: Battle Brawlers (Xbox 360) Pre-Owned X360 £2.96
    Avatar: The Burning Earth (Xbox 360) Pre-Owned X360 £8.59
    Bakugan: Battle Brawlers (Xbox 360) Pre-Owned X360 £2.9
    Avatar: The Burning Earth (Xbox 360) Pre-Owned X360 £8.51
    Battle-Fantasia (Xbox 360) Pre-Owned X360 £4.00
    Bakugan: Battle Brawlers (Xbox 360) Pre-Owned X360 £2.90
    Battle for the Pacific (Xbox 360) Pre-Owned X360 £5.21
    Avatar: The Burning Earth (Xbox 360) Pre-Owned X360 £11.27


    I would want it to search the stock sheet and generate the below Results Sheet


    Avatar: The Burning Earth (Xbox 360) Pre-Owned X360 £8.59
    Avatar: The Burning Earth (Xbox 360) Pre-Owned X360 £8.51
    Bakugan: Battle Brawlers (Xbox 360) Pre-Owned X360 £2.90
    (None Found)
    Battle for the Pacific (Xbox 360) Pre-Owned X360 £5.21


    Leaving the stock sheet looking like this


    Bakugan: Battle Brawlers (Xbox 360) Pre-Owned X360 £2.96



    Battle-Fantasia (Xbox 360) Pre-Owned X360 £4.00
    Bakugan: Battle Brawlers (Xbox 360) Pre-Owned X360 £2.90

    Avatar: The Burning Earth (Xbox 360) Pre-Owned X360 £11.27



    As you can see Battle-Fantasia (Xbox 360) is spelled differently here, this would be an error and I would want the results sheet to show (None Found instead)

    It is crucial that it removes the full row and there will be multiple of each so it must only remove the amount shown in the search terms so if 6 appear on the search terms 6 would be removed from the stock sheet and finally its crucial the results sheet is in the exact same order as the search terms sheet leaving gaps (Such as (None found)) when a result cannot be located.

    If somone can come up with a way to do this for me I would be eternally grateful and you would officially be extremely smart haha

    Thanks for anyone who has taken the time to read this and of course an extra thank you to anyone that can offer me a solution

    Hope everyone had a Great Xmas and hope everyone has a great new years!

    Thanks

    Samuel
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    So Very sorry

    I see I have posted this in the wrong section, this needs to be under excel programming / macros.

    If someone can move this I would be grateful or if anyone can advise what I should do that would be great

    apologies again

    Sam

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    I have reported the thread to the mods/admins.

    You can try sending a private message to a mod/admin and ask them to move the thread.

    Good luck!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Done ...............
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Great Stuff,

    Thanks for moving this

    Has anyone had any luck with the above, is there a way to undertake this?

    Thanks

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Try
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Hi Again

    Absolutely Superb!!!!

    Thank you ever so much for your help. I cannot begin to explain how much this is appreciated.

    Would it be possible to apply the same code to the attached sample as this will be the actual layout I am using. I am sure the change will be minor. It simply involves more columns being moved.

    Thank you once again for such brilliant assistance. I have of course added reputation given your sterling response!

    Kind Regards

    Samuel
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Is this what you wanted?
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    That is perfection its self!

    This is most certainly solved

    Thank you ever so much and have a great new years!

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    You are welcome and thanks for the rep.

    Same to you.
    Quote Originally Posted by sam103329 View Post
    Thank you ever so much and have a great new years!

  11. #11
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Hello!

    This solution has worked flawlessly for me since its creation however after adding some new data as I do regularly without issue normally to the Stock sheet section I am now receiving the following error.

    Run-time error 13
    Type mismatch

    highlighting row
    If a(i, 3) = b(ii, 3) Then

    When clicking Debug.

    Does anyone know the solution to this. I am moving over to an access database in the next few months but still require this system for now.

    Any help would be greatly appreciated.

    Kind Regards

    Samuel

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    That kind of error is raised when the data in the cell is "Error" value.

    Need to see your data in the workbook.

  13. #13
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Thanks for getting back to me!

    The data is all normal text data. What would I be looking for in regards to something causing the issue?

    I can upload but cannot really do so on the forum and cannot provide the whole data sheet due to containing sensitive information.

    Thank you once again for your help it is greatly appreciated!

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    I just need to see the value in the cell.

    Run the code and when mesgbox comes up, it tells you i and ii,

    Check the value in both sheet in row i, ii respectively.
    Please Login or Register  to view this content.
    Last edited by jindon; 02-19-2015 at 08:38 PM.

  15. #15
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    I am not 100% in regards to identifying the problematic data.

    I have attached a screen shot showing what comes up and showing the debug screen.

    Thanks again for your help its greatly appreciated!

    Error.jpg

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Run he code I have posted in my previous post.

  17. #17
    Registered User
    Join Date
    12-21-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    28

    Re: Remove Rows based upon list of search terms, copy removed rows to new sheet

    Sorry for the confusion there.

    I have removed the offending cell and its now working again!

    Thank you for your help.

    Bit of a live saver.

    Thank you again for originally creating this for me also. Cannot tell you what a help it has been.

    Finally making the move to Access as its unfortunately outgrown Excel!

    Thank you!!

+ 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: 0
    Last Post: 06-06-2013, 02:03 AM
  2. Remove rows based on list of words
    By jewseboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2013, 09:00 AM
  3. Replies: 1
    Last Post: 02-27-2013, 10:33 PM
  4. Search Rows and Copy Based on certain string?
    By wibbsy in forum Excel General
    Replies: 4
    Last Post: 02-10-2012, 07:42 AM
  5. Search for rows in one sheet and copy into another sheet based on customer id
    By excel_chi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-20-2007, 04:18 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