title: Match duplicated rows in excel within a range
body: <p>We have a large excel file with 120000 rows of data where we need to find duplicated records.</p>
  
  <p>The first records is the Master record to match against the list of duplicated records.  We need to confirm whether the duplicate are true and highlight them.</p>
  
  <p>Each record has a number of rows depending on the number of email addresses on file and also physical addresses. </p>
  
  <p>Desired results are:
  1.  If the name in the Master record matches the name in the duplicated record, then find a matching any addresses from the master record with any of the addresses in the duplicated records or matching the email addresses from master record with duplicated email addresses in the duplicated records.  Then put a YES in the duplicates column for either matches.</p>
  
  <ol>
  <li>Highlight the matching addresses in a colour, highlight the matching names with another colour, highlight the matching email addresses with another colour.</li>
  </ol>
  
  <p>There may be more than one duplicated record found and this is shown in column A</p>
  
  <p>If the names do not match, but everything else matches, we still want to put a NO so that we can do a manual check when going through the excel document.</p>
  
  <p>This is the formula that is being used in column E. It only works if the first address in the master record has a match in the duplicated records. If it is the 2nd address in the Master record that matches, then it doesn't work.</p>
  
  <p>=IF(OR(C2&amp;B2=B2,C2&amp;B2=C2,D2&amp;C2=D2, D2&amp;C2=C2,B2="Addresses",C2="Name"),"",IF(SUM(--(C2&amp;B2&amp;D2=C:C&amp;B:B&amp;D:D))>1,"YES","NO"))</p>
  
  <p>Can you help with fixing the formula and also assisting with highlighting the duplicates?</p>
  
  <pre><code>A            B                    C                       D                    E                            
  Master   Addresses            Name                   Email address        Duplicate
  255812   1 test rd Sydney     John Doe               john.doe@gmail.com   YES
           1 test rd Sydney                            john.doe@gmail.com 
           PO BOX 2 Sydney            
  
           Addresses            Name                   Email address  
  421504   1 test rd Sydney     John Doe               john.doe@gmail.com   YES
  Duplicate
  records
  found: 1                
  
  Master  Addresses             Name                    Email address 
  455444  1111 House rd Sydney  Steven Jacks            sjacks@gmail.com     NO
          134 Smart St Sydney         
  
          Addresses             Name                    Email address 
  53421   134 Smart St Sydney   Steven Jacks            sjacks@gmail.com     NO
          1111 House rd Sydney                          stephenj@hotmail.com  
          Level 5, Kings St Syd                       
          134 Smart St Sydney         
  
          Addresses              Name                   Email address 
  13232   Level 5, Kings St Syd  Steven R Jacks         stephenj@hotmail.com  NO
  
  Duplicate
  records 
  found: 2
  </code></pre>