+ Reply to Thread
Results 1 to 12 of 12

Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

  1. #1
    Registered User
    Join Date
    07-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Question Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    This is a question I'm sure has been asked numerous times before. I have two worksheets and I need to compare data. The first worksheet has 910 items on it, the second has 578. I need to do a compare and have the difference of 332 items highlighted. How do I go about doing so?

    Then part 2. Just to satisfy my curiosity. How would I go about displaying that difference of 332 on a third worksheet?

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

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    What type of data is it? Is it text? Numeric? Could be both? Something else?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    07-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    Quote Originally Posted by Tony Valko View Post
    What type of data is it? Is it text? Numeric? Could be both? Something else?
    The main field is numeric. Other cells have text values, but the compare can simply be on one numeric column

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

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    What is the main field? Is that the sheet with the 910 items on it?

    Let me make sure I understand what you want to do...

    On Sheet1 you have a list of 910 items (you just said these are numbers?).

    On Sheet2 you have a list of 578 items.

    You want to highlight the items on Sheet1 that DO NOT appear on Sheet2. In addition, you want the items on Sheet1 that do not appear on Sheet2 to be listed on Sheet3.

    Is that correct?

    Also, what version of Excel does this have to work in? Your profile data says you're using Excel 2003. Is that correct?

  5. #5
    Registered User
    Join Date
    07-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    Yes, you are correct. I didn't explain that well, but you have a pretty firm grasp of what I'm trying to do.

    This is the thing. I have multiple columns with numeric values and text (approximately 27 in the first sheet, 14 in the second). To complicate things they have different column names as well...which I suppose I could change.

    To simplify things I was going to just compare the one column I know that is on both sheets. It is a unique 13 digit number located on Column E in worksheet 1, and Column C in worksheet 2.

    And I am using Excel 2007.

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

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    Hmmm...

    Well, the sound of the data structure changes things dramatically!

    I can get the data highlighted but I don't know how to get the new list created. You'll have to get that done with a VBA macro.

    I'm going to be away for few hours but I'll get back to you.

  7. #7
    Registered User
    Join Date
    07-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    The highlighting would be perfect. I don't necessarily need the third sheet...I was just curious. Sure I could do a sort of some kind to extract the data.

    Thank you for your assistance. I appreciate it!

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

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    OK, try something like this...

    You'll have to create a named range for the data range on Sheet2.

    Let's assume the range of interest on Sheet2 is A2:E5.

    Goto the Formulas tab>Define Name
    Name: Range1 (or whatever name you want to use)
    Refers to: =Sheet2!$A$2:$E$5
    OK out

    Now, setup the conditional formatting...

    Let's assume the range to format is Sheet1 A2:J10.

    Select the *entire* range A2:J10 starting from cell A2.
    Cell A2 will be the active cell. The active cell is the
    one cell in the selected range that is not shaded. The
    formula will be relative to the active cell.

    Goto the Home tab>Styles>Conditional Formatting>
    Manage rules>New rule>Use a formula to determine
    which cells to format

    Enter this formula in the box below:

    =COUNTIF(Range1,A2)=0

    Click the Format button
    Select the desired style(s)
    OK out

  9. #9
    Registered User
    Join Date
    07-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    Another dumb question that just came to mind. I may have a way to extract data the same way. In other words, the columns in both sheets would be the same. The only difference would be the # of data items (e.g. 910, 578). Would that make the compare easier?

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

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    If the 2 lists are in 1 dimensional arrays (single rows or single columns) then I can get the 3rd list using formulas. Just need to know the locations of the 2 lists and the exact destination for the 3rd list.

  11. #11
    Registered User
    Join Date
    07-22-2013
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    Quote Originally Posted by Tony Valko View Post
    If the 2 lists are in 1 dimensional arrays (single rows or single columns) then I can get the 3rd list using formulas. Just need to know the locations of the 2 lists and the exact destination for the 3rd list.
    I think I have it figured out using these steps...

    In the sheet OLD_DATA, type CalcCriteria in cell N1 and the following formula in cell N2 (or in two empty adjacent cells):

    N2-> =COUNTIFS(NEW_DATA!$C$2:$C$579,$C2)=0

    When the result is TRUE means that there is no find record in the sheet NEW_DATA.

    Now, in the sheet OLD_DATA, use Conditional Formatting with the formula =COUNTIFS(NEW_DATA!$C$2:$C$579,$C2)=0 to highlighted the 332 items.

    In the sheet OMITTED_DATA (source sheet), select a empty cell (A1, for example), have a click in Advanced, in the Sort & Filter group of the Data tab.


    In the dialog (Advanced Filter), do the following:

    Select the option Copy to Another Location

    In the List Range box, type OLD_DATA!$A$1:$L$911

    In the Criteria Range box, type OLD_DATA!$N$1:$N$2

    In the Copy To box, type OMITTED_DATA!A1


    Finally, press OK.

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

    Re: Compare data from two worksheets, highlight difference and/or display on 3rd worksheet

    Good deal. Thanks for the feedback!

+ 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: 2
    Last Post: 04-12-2013, 06:20 PM
  2. Compare and highlight column data across two worksheets
    By bgontarski in forum Excel General
    Replies: 1
    Last Post: 03-26-2012, 10:33 AM
  3. Compare strings in cells and highlight difference
    By Odin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-28-2006, 05:20 AM
  4. RE: How do you compare 2 list of numbers and highlight the difference
    By Tom Ogilvy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2006, 10:55 AM
  5. Compare two worksheets and highlight the one sheet’s difference from the other one
    By minrufeng in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-24-2006, 06:28 PM

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