+ Reply to Thread
Results 1 to 5 of 5

How to extract non-matching data of two columns into another column

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    Phils
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    46

    How to extract non-matching data of two columns into another column

    Hi,

    I have a data of agents and their tests taken. I have a column of "required tests" which I want to compare to the tests taken. Then all tests in the "required tests" per agent that are NOT listed under tests taken must be listed under another column called "tests to take".

    I was able to use an array formula I found in this forum to extract the needed data. But the problem is, the extraction is done per agent. Therefore, I cannot drag down to copy the formula since the index and match ranges are pointing per agent.

    Can you please help me modify the formula to cover the entire range and just do the comparison per agent? Please see the screen shot below:

    excel-problem-screengrab.png

    I also attached my excel file for your reference. I hope you can help me fix my formula.
    Here is the link of my attachment:
    http://www.excelforum.com/attachment...1&d=1462980009

    Or I hope this one will work:

    extract-non-matching-data-in-columns.xlsx

    Thank you.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,874

    Re: How to extract non-matching data of two columns into another column

    The attached uses the following logic: The combination of the agent's name and test are what make a "record" "unique." So I made some "composite keys" from the agent's name and test taken (Composite 1) and the agent's name and the tests required (Composite 2). Then I used MATCH and ISNUMBER to determine if the agent had taken the indicated required test. I had to wrap and if statement around the formula to take care of the cases where the required test cell was blank.

    Filter on False (meaning required test was not taken) to get a list of tests by agent still to be taken.

    I converted the information to an Excel table, if you copy and paste in new data into columns A:F (copy in data not headers) the formulas will copy down automatically. Clear any filters before removing data and copy and pasting.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    04-25-2013
    Location
    Phils
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    46

    Re: How to extract non-matching data of two columns into another column

    Hi, thank you for your input. Unfortunately, I need the results to be listed in a separate column. The only problem I have with my initial formula is that, the ranges are for specific agent only and I have more than 500 agents.

    I am looking for a way to modify my formula that will work per agent but the range is global, that is, the range should be B2:B35 instead of B2:B15.

  4. #4
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: How to extract non-matching data of two columns into another column

    In G2:

    =IF(SUMPRODUCT((ISNA(MATCH(A$2:A$35&E$2:E$35,A$2:A$35&B$2:B$35,0)))*(A$2:A$35=A2)*(E$2:E$35<>""))>=COUNTIF(A$2:A2,A2),INDEX(E:E,SMALL(IF(A$2:A$35=A2,IF(ISNA(MATCH(A$2:A$35&E$2:E$35,A$2:A$35&B$2:B$35,0)),ROW(A$2:A$35))),COUNTIF(A$2:A2,A2))),"")

    Array formula: Press Ctrl+Shift+Enter, not just Enter
    copy down

  5. #5
    Registered User
    Join Date
    04-25-2013
    Location
    Phils
    MS-Off Ver
    Office Professional Plus 2016
    Posts
    46

    Re: How to extract non-matching data of two columns into another column

    Quote Originally Posted by Teethless mama View Post
    In G2:

    =IF(SUMPRODUCT((ISNA(MATCH(A$2:A$35&E$2:E$35,A$2:A$35&B$2:B$35,0)))*(A$2:A$35=A2)*(E$2:E$35<>""))>=COUNTIF(A$2:A2,A2),INDEX(E:E,SMALL(IF(A$2:A$35=A2,IF(ISNA(MATCH(A$2:A$35&E$2:E$35,A$2:A$35&B$2:B$35,0)),ROW(A$2:A$35))),COUNTIF(A$2:A2,A2))),"")

    Array formula: Press Ctrl+Shift+Enter, not just Enter
    copy down
    Hi Teethless mama,

    Thank you for your help. Indeed, the formula you provided worked the way I want the problem to be solved! Thank you very much for sharing your Excel knowledge!

    The formula is very very very complicated and I don't actually understand its logic, but nevertheless, I will take time to study it to understand it for future reference.

    Best regards,
    Kolokoy

+ 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. [SOLVED] Extract similar data in columns and different data in another column
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-20-2015, 08:06 AM
  2. Replies: 1
    Last Post: 05-20-2014, 04:11 PM
  3. [SOLVED] Extract data from third column where data in two other columns match
    By redimp in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-16-2014, 10:01 AM
  4. Replies: 7
    Last Post: 09-15-2013, 12:09 PM
  5. Replies: 2
    Last Post: 04-18-2013, 05:56 PM
  6. [SOLVED] Extract data from column to make new columns
    By cinco5 in forum Excel General
    Replies: 2
    Last Post: 10-10-2012, 04:18 PM
  7. Replies: 7
    Last Post: 09-26-2012, 04:54 AM

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