+ Reply to Thread
Results 1 to 11 of 11

Compare and exact match two columns data on two separate tabs in one spreadsheet

  1. #1
    Registered User
    Join Date
    06-29-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    7

    Compare and exact match two columns data on two separate tabs in one spreadsheet

    Hi,
    I would greatly appreciate some help.
    I am validating two separate sources of data to see if the combination of style and classification match in another tab with the same 2 column match combination.

    Tab one has two columns with the below format of numbers and dots. Example like below:

    Style Classification
    12345 1234.56.7890
    67890 2345.67.8901

    I'd like to build a formula that it would not only find the match combination of two columns in another tab, but it will alert me if there is a mismatch on the classification column. The style must be an exact match.

    So for example, going along with above 'tab 1'. Tab 2 would be something like:

    Style Classification
    67890 2345.68.8901
    52597 1235.67.7891
    12345 1234.56.7890

    The formula would detect that the combination of "12345 1234.56.7890" there is a perfect match when comparing the two column combination on two separate tabs. A note like "match" would be reflected.
    The formula will also detect that the combination "67890 2345.67.8901" has a mismatch on the classification #. Since the style is a perfect match but not the classification #. And a message like "mismatch" would be reflected.

    Thanks for your help in advance.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    Maybe like this.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    Hi chs,

    Here's a macro if you like:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Registered User
    Join Date
    06-29-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    Dear Oeldere,

    Thank you for responding.

    I've tried using the formula on your attachment and it brings me back result only for the first line, but then everyline after it shows "N/A". I triple checked everything I cannot find what may be wrong. Please help

    Thanks again,

    CHS

  5. #5
    Registered User
    Join Date
    06-29-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    Thank you Xladept,

    I am not that advanced yet, but I will certainly save it for future reference .
    Best,
    CHS

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    Please Login or Register  to view this content.
    It means it is not in the VLookup list.

    But did you also use the if statement in the beginning of the formula?

  7. #7
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    if "Style" starts in A2, and "Classification" starts in B2 on Sheet1, while your database is in Sheet2, try this in Sheet1!C2:

    Please Login or Register  to view this content.
    and drag-fill down.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  8. #8
    Registered User
    Join Date
    06-29-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    Hi, I am still having issues. I've attached a sample sheet. Thanks everyone for your help!
    Attached Files Attached Files

  9. #9
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    you have to increase the range according to your needs - see the portion highlighted in red:

    Please Login or Register  to view this content.
    in your current case, it should be:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-29-2012
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    Wonderful! it worked!! Many thanks!!!!!

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Compare and exact match two columns data on two separate tabs in one spreadsheet

    Glad I could help. Thanks for the reply.
    If the question is solved, will you mark your question as solved?
    If you have (other) questions, just ask.

+ 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