+ Reply to Thread
Results 1 to 12 of 12

Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet 3

  1. #1
    Registered User
    Join Date
    05-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 365
    Posts
    27

    Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet 3

    Hi,

    I am pretty new to programming within Excel and require some assistance please.

    I am currently working on a project where by we are migrating 8000 GroupWise users to Exchange 2010. The users have seperate GroupWise ID's and AD accounts. While I work on the back-end migration, I have a team of tech's going out doing the migrations on the desktops. Each day I receive a spreadsheet of user details from the tech's of users we will migrate the next day. Apart from this data I also have a dump of all users from GroupWise.

    I have put the data on 2 sheets in an Excel spreadsheet and have been unsuccessfully trying to match the email address in sheet 1 to the email address in sheet 2 and if the emails match, copy entire Row from sheet 2 to sheet 3.

    I have attached a copy of the spreadsheet that I am working with, in the hope that some out in the community would assist with my query. All help will be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Make a copy of your workbook and test the following on the copy.

    Please Login or Register  to view this content.
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

  3. #3
    Registered User
    Join Date
    05-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 365
    Posts
    27

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Thanks for the prompt response. Your assistance is greatly appreciated :-)

    Works 100%

  4. #4
    Registered User
    Join Date
    05-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 365
    Posts
    27

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Just had a thought is it possible to highlight the cell in sheet 1 if it doesnt match the cell in sheet 2???

  5. #5
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Quote Originally Posted by Guji View Post
    Just had a thought is it possible to highlight the cell in sheet 1 if it doesnt match the cell in sheet 2???
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    If you don't want to use a macro, here is another solution

    Put this formula in Sheet 3 cell A1 and copy right to Column Q and down as many rows as you need to:

    =OFFSET(Sheet2!$A$1,MATCH(Sheet1!$F7,Sheet2!$B$2:$B$6234,0),COLUMN()-1)

    The MATCH(Sheet1!$F7,Sheet2!$B$2:$B$6234,0) part of the equation finds the row number if the email addresses in Sheets 1 and Sheet 2 match

    The OFFSET(Sheet2!$A$1,MATCH(Sheet1!$F7,Sheet2!$B$2:$B$6234,0),COLUMN()-1) returns the value in column A that matches the criteria
    By copying the equation to the right to column Q, you copy the rest of the columns on that row

    NOTE:
    This approach does not pick up if an email is entered more than once (For example, naidoodenashree@durban.gov.za



    Kirk
    Last edited by K m; 05-18-2012 at 02:27 PM.

  7. #7
    Registered User
    Join Date
    05-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 365
    Posts
    27

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    @ AlvaroSiza

    Thanks for this works 100%. Your assistance is really appreciated
    Last edited by Cutter; 10-14-2012 at 01:36 PM. Reason: Removed whole post quote

  8. #8
    Registered User
    Join Date
    05-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 365
    Posts
    27

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Hi AlvaroSiza

    I wonder if you could assist with my query! The assistance you provide previously with above has been proven invaluable, I however do face a little bit of a problem. I sometimes get a false response and was wondering if there is anyway of overcoming this.

    eg:
    users with email address kistenm@xxx.com and balakistenm@xxx.com. I need to match kistenm@xxx.com but I have found that because the second address appears as the first entry in my lookup sheet, I am returned that entry which is incorrect.

    Is there a way to get around this?
    Last edited by Guji; 05-29-2012 at 02:32 PM.

  9. #9
    Registered User
    Join Date
    05-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 365
    Posts
    27

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Somebody help me please!!! Getting more of the false results .... driving me crazy!!

  10. #10
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Within this line of code
    Please Login or Register  to view this content.
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    See if that fixes it and report back.

  11. #11
    Registered User
    Join Date
    05-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 365
    Posts
    27

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Thanks!! You are an absolute star!!! Really Really appreciate the assistance. If there was a way to repay I would.

  12. #12
    Registered User
    Join Date
    05-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 365
    Posts
    27

    Re: Lookup email address in sheet 1, match with sheet 2 and copy row from sheet 2 to sheet

    Hi,

    I require some assistance please. I have been using this for a while now and it has been absolutely great. I have however found a small problem, the script seems to be CASE sensitive. If i have and email address xyz@abc.com and my control data is XYZ@ABC.com, I do not get a match. Is there a way around this??

+ 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