Find Duplicates with Criteria?

[cavag4]

Hi, I need a way to find duplicates in a spreadsheet based on criteria in other columns. I've tried countif, and conditional formatting and it's not getting me what I want. I've pasted the spreadsheet below to give a better idea of what I'm talking about.

I've got soil sampling data from several years, and am trying to find problem fields where the phosphorus level has been low for a long time. If the land location shows up more than once in the "bad half" (red/yellow) of the sheet, that means it's a problem field, and I need to highlight that land location. Now, highlighting duplicates doesn't do any good, because the fields are almost always tested more than once - I need to just find the ones with 2 or more tests in the low zone.

<table width="213" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> </colgroup><tbody><tr style="mso-height-source:userset;height:25.5pt" height="34"> <td class="xl70" style="height:25.5pt;width:53pt" width="71" height="34">Land Location
</td> <td class="xl67" style="width:63pt" width="84">Year Tested
</td> <td class="xl67" style="width:44pt" width="58">P (ppm)</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">06-60-18</td> <td class="xl65"> 2007</td> <td class="xl65" style="border:none;background:#F8696B;mso-pattern:black none">2.7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">36-60-18</td> <td class="xl65"> 2007</td> <td class="xl65" style="border:none;background:#F8796E;mso-pattern:black none">3.2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">35-59-18</td> <td class="xl65"> 2008</td> <td class="xl65" style="border:none;background:#F98A71;mso-pattern:black none">3.7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">29-59-17</td> <td class="xl65"> 2007</td> <td class="xl65" style="border:none;background:#F98E72;mso-pattern:black none">3.8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">12-60-18</td> <td class="xl65"> 2008</td> <td class="xl65" style="border:none;background:#FA9172;mso-pattern:black none">3.9</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">06-60-17</td> <td class="xl65"> 2008</td> <td class="xl65" style="border:none;background:#FA9874;mso-pattern:black none">4.1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">35-60-18</td> <td class="xl65"> 2007</td> <td class="xl65" style="border:none;background:#FA9874;mso-pattern:black none">4.1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">04-59-17</td> <td class="xl65"> 2008</td> <td class="xl65" style="border:none;background:#FA9F75;mso-pattern:black none">4.3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">36-59-18</td> <td class="xl65"> 2008</td> <td class="xl65" style="border:none;background:#FA9F75;mso-pattern:black none">4.3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">07-60-18</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#FBAF78;mso-pattern:black none">4.8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">07-59-17</td> <td class="xl65"> 2009</td> <td class="xl65" style="border:none;background:#FCBA7A;mso-pattern:black none">5.1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">06-59-17</td> <td class="xl65"> 2007</td> <td class="xl65" style="border:none;background:#FCBD7B;mso-pattern:black none">5.2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">06-60-18</td> <td class="xl65"> 2008</td> <td class="xl65" style="border:none;background:#FCBD7B;mso-pattern:black none">5.2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">06-60-18</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#FDC77D;mso-pattern:black none">5.5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">26-60-18</td> <td class="xl65"> 2007</td> <td class="xl65" style="border:none;background:#FDD17F;mso-pattern:black none">5.8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">07-59-17</td> <td class="xl65"> 2008</td> <td class="xl65" style="border:none;background:#FEE282;mso-pattern:black none">6.3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">26-59-18</td> <td class="xl65"> 2008</td> <td class="xl65" style="border:none;background:#FEE583;mso-pattern:black none">6.4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">07-60-18</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#FDEB84;mso-pattern:black none">6.7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">36-59-18</td> <td class="xl65"> 2009</td> <td class="xl65" style="border:none;background:#FDEB84;mso-pattern:black none">6.7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">26-59-18</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#E5E483;mso-pattern:black none">8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">12-60-18</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#DFE283;mso-pattern:black none">8.3</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">01-60-18</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#CFDD82;mso-pattern:black none">9.2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">08-60-17</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#CBDC81;mso-pattern:black none">9.4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">35-59-18</td> <td class="xl65"> 2009</td> <td class="xl65" style="border:none;background:#C0D981;mso-pattern:black none">10</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">04-59-17</td> <td class="xl65"> 2009</td> <td class="xl65" style="border:none;background:#B7D680;mso-pattern:black none">10.5</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">08-60-17</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#B3D580;mso-pattern:black none">10.7</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">23-60-18</td> <td class="xl66"> 2009</td> <td class="xl66" style="border-top:none;border-right:none;border-bottom:.5pt solid windowtext; border-left:none;background:#99CE7F;mso-pattern:black none">12.1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">06-59-17</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#67C07C;mso-pattern:black none">14.8</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">22-60-18</td> <td class="xl65"> 2009</td> <td class="xl65" style="border:none;background:#63BE7B;mso-pattern:black none">16.9</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">23-60-18</td> <td class="xl65"> 2007</td> <td class="xl65" style="border:none;background:#63BE7B;mso-pattern:black none">17</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">35-60-18</td> <td class="xl65"> 2007</td> <td class="xl65" style="border:none;background:#63BE7B;mso-pattern:black none">17.9</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">08-60-17</td> <td class="xl65"> 2010</td> <td class="xl65" style="border:none;background:#63BE7B;mso-pattern:black none">20.9</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">22-60-18</td> <td class="xl65"> 2009</td> <td class="xl65" style="border:none;background:#63BE7B;mso-pattern:black none">45.6</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl68" style="height:12.75pt" height="17">35-60-18
</td> <td class="xl65"> 2009</td> <td class="xl65" style="border:none;background:#63BE7B;mso-pattern:black none">58.4</td> </tr> </tbody></table>

If I could highlight just one instance of the land location, that would be great. If I could use a vLookup or something like that to create a new column listing the land locations of problem fields, that would work too. Open to ideas, and thanks in advance!

 

[shg]

How about like this?

       ---A---- -B-- --C-- -D--
   1     Loc     Yr  P ppm Bad?
   2   06-59-17 2007  5.2      
   3   06-60-18 2007  2.7      
   4   23-60-18 2007 17.0      
   5   26-60-18 2007  5.8      
   6   29-59-17 2007  3.8      
   7   35-60-18 2007  4.1      
   8   35-60-18 2007 17.9      
   9   36-60-18 2007  3.2      
  10   04-59-17 2008  4.3      
  11   06-60-17 2008  4.1      
  12   06-60-18 2008  5.2  Bad 
  13   07-59-17 2008  6.3      
  14   12-60-18 2008  3.9      
  15   26-59-18 2008  6.4      
  16   35-59-18 2008  3.7      
  17   36-59-18 2008  4.3      
  18   04-59-17 2009 10.5      
  19   07-59-17 2009  5.1  Bad 
  20   22-60-18 2009 16.9      
  21   22-60-18 2009 45.6      
  22   23-60-18 2009 12.1      
  23   35-59-18 2009 10.0      
  24   35-60-18 2009 58.4      
  25   36-59-18 2009  6.7  Bad 
  26   01-60-18 2010  9.2      
  27   06-59-17 2010 14.8      
  28   06-60-18 2010  5.5  Bad 
  29   07-60-18 2010  4.8      
  30   07-60-18 2010  6.7  Bad 
  31   08-60-17 2010  9.4      
  32   08-60-17 2010 10.7      
  33   08-60-17 2010 20.9      
  34   12-60-18 2010  8.3      
  35   26-59-18 2010  8.0

The formula in D2 and copied down is

=IF(COUNTIFS(A$1:A2, A2, C$1:C2, "<7") > 1, "Bad", "")

 

[cavag4]

That could work just fine, thanks a lot!

 

[cavag4]

Ok, I lied, it's not working for me, lol! Could be that I've been looking at the same bloody thing forever.

Does the data need to be sorted by year, like you have it? If so, this isn't going to work - the color scale is a visual I need to show my customers.

 

[shg]

It doesn't need to be sorted by anything, but the formula only shows Bad on the second occurrence of a low reading, so it just seemed to make sense to sort by year.

If you don't want that, then perhaps

       ---A---- -B-- --C-- -D-
   1     Loc     Yr  P ppm    
   2   06-60-18 2007  2.7  Bad
   3   36-60-18 2007  3.2     
   4   35-59-18 2008  3.7     
   5   29-59-17 2007  3.8     
   6   12-60-18 2008  3.9     
   7   06-60-17 2008  4.1     
   8   35-60-18 2007  4.1     
   9   04-59-17 2008  4.3     
  10   36-59-18 2008  4.3  Bad
  11   07-60-18 2010  4.8  Bad
  12   07-59-17 2009  5.1  Bad
  13   06-59-17 2007  5.2     
  14   06-60-18 2008  5.2  Bad
  15   06-60-18 2010  5.5  Bad
  16   26-60-18 2007  5.8     
  17   07-59-17 2008  6.3  Bad
  18   26-59-18 2008  6.4     
  19   07-60-18 2010  6.7  Bad
  20   36-59-18 2009  6.7  Bad
  21   26-59-18 2010  8.0     
  22   12-60-18 2010  8.3     
  23   01-60-18 2010  9.2     
  24   08-60-17 2010  9.4     
  25   35-59-18 2009 10.0     
  26   04-59-17 2009 10.5     
  27   08-60-17 2010 10.7     
  28   23-60-18 2009 12.1     
  29   06-59-17 2010 14.8     
  30   22-60-18 2009 16.9     
  31   23-60-18 2007 17.0     
  32   35-60-18 2007 17.9     
  33   08-60-17 2010 20.9     
  34   22-60-18 2009 45.6     
  35   35-60-18 2009 58.4

=IF(COUNTIFS($A$2:$A$35, A2, $C$2:$C$35, "<7") > 1, "Bad", "")