+ Reply to Thread
Results 1 to 2 of 2

Find Duplicates across 2 pairs of columns (2 columns against 2 columns)

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    Dallas
    MS-Off Ver
    2013
    Posts
    10

    Find Duplicates across 2 pairs of columns (2 columns against 2 columns)

    I am familiar with how to find duplicates between only 2 columns, but what about between 2 pairs of columns?

    I am comparing an old report against a new one, which I have combined onto one sheet. In Column A there is an IP address, in Column B there is an error code, and the refreshed data in columns C and D, respectively. Some error codes may come up on a new IP address, so I can't simply look for duplicates of error codes, I have to see if the duplicate is coming up on the same machine.

    Right now, I'm working with this formula between just two columns;
    =IF(ISERROR(MATCH(B5,$D$5:$D$501,0)),"",B5)

    Having a less-than-thorough understanding of excel formulas, is there some complex way I can find duplicates between these pairs?

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Find Duplicates across 2 pairs of columns (2 columns against 2 columns)

    I'm partial to SUMPRODUCT. You can use this to count the number of times all criteria are met.

    =IF(SUMPRODUCT(($D$5:$D$501=B5)*($C$5:$C$501=A5))>0,B5,"") and drag down
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

+ 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] Macro to Compare Columns A & B and dispaly any duplicates in Columns C & D
    By robertguy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-12-2013, 02:24 AM
  2. Replies: 6
    Last Post: 05-30-2013, 04:58 PM
  3. Replies: 6
    Last Post: 12-26-2012, 01:43 PM
  4. How to find duplicates on multiple columns (3 Columns )
    By Kaleemuddin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2012, 07:04 AM
  5. Replies: 1
    Last Post: 02-13-2012, 08:08 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