+ Reply to Thread
Results 1 to 7 of 7

exclude a match condition between two data fields

  1. #1
    Registered User
    Join Date
    04-21-2011
    Location
    Raleigh
    MS-Off Ver
    Excel 2003
    Posts
    4

    exclude a match condition between two data fields

    As an Example, Let's say I have three columns of data. (Column A, B, and C) Column A has numbers 1 - 10 populated. In Column B, numbers 5-15. Column C would compare values in both Columns A and B; however it should not count a matching condition twice. In this example 5 would be seen in both Column A and B. When Compairing both A & B - how do I either exclude a match condition or only count a match condition as 1 data point instead of 2?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: exclude a match condition between two data fields

    Can you layout a sample of what Column A and B look like and what column C (expected results would look like?)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-21-2011
    Location
    Raleigh
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: exclude a match condition between two data fields

    I will do my best.

    First - let me add some background. I work in a call center and each customer ticket has a unique number value for tracking. Currently, they way the data is pulled each month, I get a list of tickets currently open. The value I am trying to extract from the data is new tickets versus tickets that carried over from the previous month.

    For an Example

    Column A = April and Column B = May


    The month of May will contain tickets carried over from April and new tickets that are open. So when compairing May to April, I only want to count unique/new ticket numbers and exclude tickets carried over. In addition, the cross check between columns need to look at all the data within each column because tickets would not be lined up exact. One month I could have 60 tickets and the next month 100. Line for line they would never match at the row level, which is why I need to compare between columns.

    April May Column C Value

    4-1234 4-1234 0 (4-1234 is a Matching value between April and May = 0)
    4-5432 4-0001 1 (4-0001 does not exist in April therefore count is 1)
    4-7134 4-5432 0 (4-5432 is a matching value in April)
    4-7134 0 (4-7134 is a matching value in April)
    4-1111 1 (4-1111 does not exist in April therefore count is 1)


    2 = total new tickets

    I hope this helps.

    Thanks,

    Rob

  4. #4
    Registered User
    Join Date
    04-21-2011
    Location
    Raleigh
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: exclude a match condition between two data fields

    I apologize, The text formatting did not come out liked I would have hoped when posting. So I attached a excel spreadsheet to help.
    Attached Files Attached Files

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: exclude a match condition between two data fields

    So, in essence, are we just counting items in column B that do not appear in column A?

    If so, try:

    =SUMPRODUCT(--(ISNA(MATCH(C4:C13,B4:B13,0))))

    If not, please elaborate...

  6. #6
    Registered User
    Join Date
    04-21-2011
    Location
    Raleigh
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: exclude a match condition between two data fields

    That is the concept; however, instead of specifying the exact range within C and B. How do I look specify the all data within column C and B?

    The reason I ask, so I do not have to set a new range each month to compare. Tickets volume varies.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: exclude a match condition between two data fields

    Try:

    =SUMPRODUCT(--(ISNA(MATCH(C4:INDEX(C:C,MATCH(REPT("z",255),C:C)),B4:INDEX(B:B,MATCH(REPT("z",255),C:C)),0))))

    this allows you to add to the bottom of the table... just as long as you have no unrelated data below the table...

+ 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