+ Reply to Thread
Results 1 to 12 of 12

Match and Copy Unique Values

  1. #1
    Registered User
    Join Date
    03-12-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Match and Copy Unique Values

    Hi All! I am looking for some help with my excel spreadsheet. Right now I have two sheets. One has roughly 65,000 names in column A. These names appear about 20 times each. In the second sheet, I have about 800 of these names that only appear once.

    I need the rows of Sheet1 that have names in column A that are also in column A of Sheet2 to be copied to a new sheet. Here's what I have so far, but it's not working for me. Any help would be greatly appreciated! Thank you all so much!


    Please Login or Register  to view this content.
    Last edited by Leith Ross; 03-12-2011 at 10:59 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    re: Match and Copy Unique Values

    Please Login or Register  to view this content.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Match and Copy Unique Values

    Hello quiksilver00024,

    Welcome to the Forum!

    To make posts easier to read, copy and edit, please use code tags. Also, I have changed your thread title to better reflect your question. I have done this for you because you are new. Do take some time to read the forum rules. These help make it easier to find and get answers quickly to your questions.

    As a new member, please take some time to familiarize yourself with the rules.
    Click here for the Forum Rules
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    03-12-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Re: Match and Copy Unique Values

    Hi, thank you so much for all of your help. I am, however, receiving an error when I try to use the macro. It says: Compile Error: Variable Required - can't assign to this expression...any ideas on why this may be happening?

    Thanks!

  5. #5
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Match and Copy Unique Values

    Could you upload your spreadsheet to this thread ?

  6. #6
    Registered User
    Join Date
    03-12-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Re: Match and Copy Unique Values


  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Match and Copy Unique Values

    Files can be uploaded here on the forum. Just click GO ADVANCED and use the paperclip icon.

    Anyway, based on your description, assuming column AA is free to use as a "helper" column, this is how I would do it:
    Please Login or Register  to view this content.
    This macro uses Sheet1 as the name of the sheet with data to copy, and Sheet2 as the name of the sheet to match to.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  8. #8
    Registered User
    Join Date
    03-12-2011
    Location
    Miami, FL
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Re: Match and Copy Unique Values

    Thanks for your help, but I receive the following error when I try to use that code:

    Method 'AutoFilter' of subject 'Range' failed.

    Any ideas on what might be causing this error?

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Match and Copy Unique Values

    Did you consider removing from a copy of sheet1 the rows that have no counterpart in sheet2 ?



  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Match and Copy Unique Values

    Quote Originally Posted by quiksilver00024 View Post
    Thanks for your help, but I receive the following error when I try to use that code:

    Method 'AutoFilter' of subject 'Range' failed.

    Any ideas on what might be causing this error?
    Post up here a zipped copy of the workbook with the macro in it so I can see what you're seeing, I'm sure it's something simple.

    Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

    SNB, I believe Nimrod's suggested macro above takes that approach.

  11. #11
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Match and Copy Unique Values

    quiksilver00024:
    To Upload the spreadsheet , that you attempted using my solution in , go to "advnaced" and then utilize the "manage attachements" button

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Match and Copy Unique Values

    I was thinking of:

    Please Login or Register  to view this content.
    or even simpler:
    Please Login or Register  to view this content.
    Last edited by snb; 03-14-2011 at 07:07 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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