andby
New Member
- Joined
- Jun 4, 2012
- Messages
- 22
I have been trying for weeks to get a solution to this so I am really hoping someone out there can help me THANK YOU
Here is a sample of the data. I want to select rows/columns based on duplicates in the Street column and get a value based on the Macro I titled RDUP ( see below data)
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {color:windowtext; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:0; text-align:center;} .xl64 {color:windowtext; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center;} .xl65 {mso-number-format:0; text-align:center;} .xl66 {text-align:center;} .xl67 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:none; border-bottom:none; border-left:.5pt solid windowtext;} .xl68 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:none; border-bottom:none; border-left:none;} .xl69 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl70 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid windowtext;} .xl71 {mso-number-format:0; text-align:center; border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl72 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} .xl73 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none;} .xl74 {mso-number-format:0; text-align:center; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;} --> </style> <table style="border-collapse: collapse; width: 207px; height: 260px;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="width:65pt" span="3" width="65"> <col style="mso-width-source:userset;mso-width-alt:1877;width:44pt" width="44"> <col style="width:65pt" width="65"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;width:65pt" height="15" width="65">A</td> <td class="xl63" style="width:65pt" width="65">B</td> <td class="xl63" style="width:65pt" width="65">Status</td> <td class="xl64" style="width:44pt" width="44">Street</td> <td class="xl63" style="width:65pt" width="65">New Status</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt" height="15">0</td> <td class="xl68">0</td> <td class="xl69">1</td> <td class="xl66">w</td> <td class="xl65">#VALUE!</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl70" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl71">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl72" style="height:15.0pt" height="15">0</td> <td class="xl73">0</td> <td class="xl74">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">e</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">e</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">1</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">u</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">u</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">11</td> <td class="xl65">0</td> <td class="xl65">2</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> </tbody></table>
Here is the Macro:
Function RDUP(A, B, Status)
If B > 0 Then
RDUP = 3
ElseIf Status = 22 Then
RDUP = 3
ElseIf Status = 4 Then
RDUP = 4
ElseIf A >= 6 Then
RDUP = 2
ElseIf 0 < A And A < 6 Then
RDUP = 1
ElseIf A = 0 Then
RDUP = 0
Else: RDUP = 5
End If
End Function
Here is a sample of the data. I want to select rows/columns based on duplicates in the Street column and get a value based on the Macro I titled RDUP ( see below data)
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {color:windowtext; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; mso-number-format:0; text-align:center;} .xl64 {color:windowtext; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; text-align:center;} .xl65 {mso-number-format:0; text-align:center;} .xl66 {text-align:center;} .xl67 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:none; border-bottom:none; border-left:.5pt solid windowtext;} .xl68 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:none; border-bottom:none; border-left:none;} .xl69 {mso-number-format:0; text-align:center; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl70 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid windowtext;} .xl71 {mso-number-format:0; text-align:center; border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl72 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} .xl73 {mso-number-format:0; text-align:center; border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none;} .xl74 {mso-number-format:0; text-align:center; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;} --> </style> <table style="border-collapse: collapse; width: 207px; height: 260px;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="width:65pt" span="3" width="65"> <col style="mso-width-source:userset;mso-width-alt:1877;width:44pt" width="44"> <col style="width:65pt" width="65"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;width:65pt" height="15" width="65">A</td> <td class="xl63" style="width:65pt" width="65">B</td> <td class="xl63" style="width:65pt" width="65">Status</td> <td class="xl64" style="width:44pt" width="44">Street</td> <td class="xl63" style="width:65pt" width="65">New Status</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt" height="15">0</td> <td class="xl68">0</td> <td class="xl69">1</td> <td class="xl66">w</td> <td class="xl65">#VALUE!</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl70" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl71">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl72" style="height:15.0pt" height="15">0</td> <td class="xl73">0</td> <td class="xl74">1</td> <td class="xl66">w</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">e</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">e</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">1</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">u</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">u</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">11</td> <td class="xl65">0</td> <td class="xl65">2</td> <td class="xl66">b</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl65" style="height:15.0pt" height="15">0</td> <td class="xl65">0</td> <td class="xl65">1</td> <td class="xl66">x</td> <td class="xl66">
</td> </tr> </tbody></table>
Here is the Macro:
Function RDUP(A, B, Status)
If B > 0 Then
RDUP = 3
ElseIf Status = 22 Then
RDUP = 3
ElseIf Status = 4 Then
RDUP = 4
ElseIf A >= 6 Then
RDUP = 2
ElseIf 0 < A And A < 6 Then
RDUP = 1
ElseIf A = 0 Then
RDUP = 0
Else: RDUP = 5
End If
End Function