How to find certain instances of duplicates?

[cavag4]

Hi, I need to find duplicated land locations in this spreadsheet that have had low levels of phosphorus over several years (ie. find problem fields with a trend of low phos levels). Since all fields have been tested over multiple years, and because not all fields were tested in 2010, it doesn't do me much good to just find duplicates in the Land Location column. Can anyone help me with a formula/conditional format to just select one instance of the land location if it shows up multiple times in the "bad half" (red/yellow) of the P column? If we can highlight the land location, great. If we can spit out a list of "problem" land locations in a different column that would also work. This would eliminate the human error/confusion/frustration of trying to find this with my own eyes! Thanks in advance!


<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>

 

[T. Valko]

Hi, I need to find duplicated land locations in this spreadsheet that have had low levels of phosphorus over several years (ie. find problem fields with a trend of low phos levels). Since all fields have been tested over multiple years, and because not all fields were tested in 2010, it doesn't do me much good to just find duplicates in the Land Location column. Can anyone help me with a formula/conditional format to just select one instance of the land location if it shows up multiple times in the "bad half" (red/yellow) of the P column? If we can highlight the land location, great. If we can spit out a list of "problem" land locations in a different column that would also work. This would eliminate the human error/confusion/frustration of trying to find this with my own eyes! Thanks in advance!


<TABLE cellSpacing=0 cellPadding=0 width=213 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58></COLGROUP><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD class=xl70 style="WIDTH: 53pt; HEIGHT: 25.5pt" 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #f8696b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #f8796e; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #f98a71; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #f98e72; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fa9172; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fa9874; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fa9874; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fa9f75; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fa9f75; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fbaf78; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fcba7a; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fcbd7b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fcbd7b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fdc77d; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fdd17f; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fee282; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fee583; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fdeb84; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #fdeb84; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #e5e483; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #dfe283; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #cfdd82; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #cbdc81; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #c0d981; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #b7d680; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #b3d580; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #99ce7f; BORDER-LEFT: medium none; BORDER-BOTTOM: windowtext 0.5pt solid; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #67c07c; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #63be7b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #63be7b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #63be7b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #63be7b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #63be7b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; 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-RIGHT: medium none; BORDER-TOP: medium none; BACKGROUND: #63be7b; BORDER-LEFT: medium none; BORDER-BOTTOM: medium none; mso-pattern: black none">58.4</TD></TR></TBODY></TABLE>

You'll need to give a more specific definition of "bad half". For example, <7.0 ppm?

What version of Excel are you using?

 

[cavag4]

Anything under 15 ppm would be the "bad half" in this case. The number changes for other nutrients ( like potassium, sulfur, etc) so it would be nice if I could adjust this, or even just find a formula that chooses from the lowest 50% of the fields listed.

I'm just dabbling with Pivot Tables and VBA, but very much a beginner. Is there a fit for something like that here?

I'm running Excel 2010 on a windows 7 platform (hooray for upgrades!)

 

[T. Valko]

Anything under 15 ppm would be the "bad half" in this case. The number changes for other nutrients ( like potassium, sulfur, etc) so it would be nice if I could adjust this, or even just find a formula that chooses from the lowest 50% of the fields listed.

I'm just dabbling with Pivot Tables and VBA, but very much a beginner. Is there a fit for something like that here?

I'm running Excel 2010 on a windows 7 platform (hooray for upgrades!)

Here's a method to use conditional formatting.

Let's assume your data is in the range A2:C35.

Select the *entire* range A2:A35 starting from cell A2. Cell A2 will be the active cell. The active cell is the one cell in the selected range that is not shaded. The formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
• =AND(COUNTIFS(A$2:A$35,A2,C$2:C$35,"<15")>1,COUNTIF(A$2:A2,A2)=1)
• Click the Format button
• Select the desired style(s)
• OK out

That will format the first instance of duplicated land locations that are in the "bad" range.

 

[cavag4]

Thanks, that helps a lot!

 

[T. Valko]

Thanks, that helps a lot!

You're welcome. Thanks for the feedback!