+ Reply to Thread
Results 1 to 5 of 5

Simple way to compare worksheets

  1. #1
    Registered User
    Join Date
    11-21-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Simple way to compare worksheets

    Hello,

    Sorry for having a question that has no doubt been asked a hundred times, but I am very frustrated because none of the formulas that I try work. I always get an error pop up.

    My situation is this: I am looking for a fast and simple way of comparing two lists of names to see if any of the names from List 1 are in List 2. If yes, then I would like those names that are in both to be highlighted, or have an 'X' in an extra column, or something to make them easily visible.

    I have tried doing them in the same sheet with List 1 in column A, and List 2 in column B. I have also tried having each List in column A of separate sheets (List1 in Sheet1, and List 2 in Sheet2).

    The problem I keep having is that the formulas that I find online through message boards/Q&A pages similar to this one, never work. When I type them into the formula bar and hit enter, I get a pop up that says 'the formula you have typed contains an error' and it will highlight a part of the formula. I can never understand why it is wrong because the formula works for other people and doesnt contain any weird characters.

    Here is example: I had put Name List 1 on Sheet1 (column A), and Name List 2 in Sheet2 (column A). I then followed this advice I found:

    "I suggest you do this by columns. I will give the example of comparing every cell in col A, Sheet 1 with same cell on sheet 2.

    Go to sheet 3 and write the following formula in A1:
    =IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1,"")

    now (on sheet 3) in B1 write:
    =IF(Sheet1!A1<>Sheet2!A1,Sheet2!A1,"")

    Now highlight col A & B starting at A1 and B1 and highlight as far down the two cols as you want. Then do a FILL / DOWN.

    If there is no diff between sheet 1 A1 and Sheet 2 A1 the cells in sht 3 A1 & B1 will be blank. If there is a diff then sht3 A1 & B1 will show that diff."


    The first formula listed went into the formula bar fine, but the second didn't work. I do not know why.

    Can someone please help me? I know this kind of thing is really simple and there is a quick, dirty and plain old obvious way of doing it I just cannot figure out how. It is really frustrating. If someone has any simple formula I would really appreciate it.

    Thank you for your time and for reading this long post.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Simple way to compare worksheets

    If you just want to highlight the entries that are, or are not, in the other list, you could use Conditional Formatting with a COUNTIF formula.

    If the lists are on separate sheets, you'll probably need to create named ranges.

    Regards


    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results.

    To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
    To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

    On this page, below the message box, you will find a button labelled 'Manage Attachments'.
    Clicking this button will open a new window for uploading attachments.

    You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
    Alternatively you can click the Attachment Icon to open this page.

    To upload a file from your computer, click the 'Browse' button and locate the file.

    To upload a file from another URL, enter the full URL for the file in the second box on this page.
    Once you have completed one of the boxes, click 'Upload'.

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Simple way to compare worksheets

    For conditional formatting, you can name both ranges, such as S1A for the first sheet and S2A for the second, then in Sheet 1, put a conditional format formula of =Countif(S2A,A1) in A1 and copy the formatting down. If there's a match, it will be highlighted. Do the same for Sheet 2, but with S1A.

    Another way with your helper column is =IF(COUNTIF(Sheet1!A:A,A1),"X","") in sheet 2 and then its counterpart in sheet 1. hth
    Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.

    If you like a post, please rate it with the scales icon (top right).
    If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.

  4. #4
    Registered User
    Join Date
    11-21-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Simple way to compare worksheets

    Thank you for the replies so far. I will upload an example document of what I am trying to achieve.
    Attached Files Attached Files

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Simple way to compare worksheets

    Please see the attached


    Regards, TMS

+ 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