+ Reply to Thread
Results 1 to 10 of 10

Finding and deleting a small list of names from a really big list of names

  1. #1
    Registered User
    Join Date
    06-30-2011
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Smile Finding and deleting a small list of names from a really big list of names

    Hello!

    First time poster, I'm willing to read up on things but I was hoping someone could direct me to the appropriate location (or tell me what to do, hah)

    I have no experience with VBA but I was told I need to use it to do what I need to do:

    I have a list of 600 names that I need to omit from a database of 100,000. The names of 100,000 are in a column that is attached to a database of 20 columns. I need to delete these names from the list of 100,000 and the entire row that the name corresponds to.

    So far I can find the names doing the LOOKUP function. And I have them matched with my list. However, I do not know how to make excel tell me in which row the LOOKUP function found the names, nor do I know how to tell excel to delete the row in which it finds the name.

    While it would be nice if you could do my job for me and just tell me what VBA code I need, I realize that that's a bit of a stretch. If someone could direct me to where I could learn how to do this I would appreciate it. Google searching this does not provide any explanation of basic foundation for me to develop my own code, and I don't really want to begin from scratch (if possible).

    Cheers,
    Vital

  2. #2
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Finding and deleting a small list of names from a really big list of names

    Hi,


    Welcome too the forum.

    You could use a filter to delete the "Names" from the list.
    If you could attach an example of your data.
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  3. #3
    Registered User
    Join Date
    06-30-2011
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Thumbs up Re: Finding and deleting a small list of names from a really big list of names

    Yes!

    Thank you sooo much! I didn't even know about the sort and filter option, I just added the names to a new 'Custom Sort...' list and had them sorted to the top!

    This is great! I didn't even have to touch VBA. :D

    Thank you Kindly Charles!

  4. #4
    Registered User
    Join Date
    06-30-2011
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Unhappy Re: Finding and deleting a small list of names from a really big list of names

    Errr, nm I spoke too soon.

    Custom lists only allow for 255 characters, and I can't create more than one list for one variable because it won't let me sort that way..

    I'll make an example of the data and post it here today or tomorrow.

    Sorry!
    V

  5. #5
    Registered User
    Join Date
    06-30-2011
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Post Re: Finding and deleting a small list of names from a really big list of names

    Ok, So say for example the first list, from A1:A99 is my list of 100,000 names.

    The second list, A101:A117, represents the 600 names that I need to remove from the first list. I want to delete the entire row that the name appears in.

    Is there a way to make the filter automatically do this? I don't really want to find and click off 600 names in a list of 100,000.

    Thank You!
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Finding and deleting a small list of names from a really big list of names

    HI,


    Thanks for the workbook, however you are showing all of the data in column "A". Is that the way your actual data is laid out. The List of names you want to compare is in column A, but where are the names you want too remove ? Are they in the same workbook? And too i'm looking at doing this via code.

  7. #7
    Registered User
    Join Date
    06-30-2011
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Finding and deleting a small list of names from a really big list of names

    The second list can be placed anywhere. It is an independent list of names I need to remove from the original list It might make more sense to place it in a different worksheet. If I placed it in a different column I wouldn't be able to delete the entire rows though.

    Hopefully someone can help us out!

    Cheers,
    V

  8. #8
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Finding and deleting a small list of names from a really big list of names

    option to make it, please check attachment, run code "test"

    PS. Numbers used instead of names.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Finding and deleting a small list of names from a really big list of names

    Ok,

    This is what I came up with.
    I put a button on sheet1. Check both worksheets before you click. I placed a dummy name in sheet2. it will not be deleted from sheet2 b/c it's not in sheet1

    watersev thanks for your code.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-30-2011
    Location
    Winnipeg, Canada
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Finding and deleting a small list of names from a really big list of names

    Ahh this looks good!
    Sorry I am off to the Winnipeg Folk Festival for the weekend but I will check it on Monday when I get back!

    Cheers,
    Kevin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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