Removing duplicates in a row

gmassey

New Member
Joined
Jan 13, 2009
Messages
18
Can someone look at the sheet sample? I am trying to turn duplicates into a zero like Ive done in record 1 so that the same tax bill is not counted twice. The records are in rows and if I do a transpose and try to do it by hand it will take forever cause I have hundreds of records. Thanks for looking.

Galen


Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>BQ</th><th>BR</th><th>BS</th><th>BT</th><th>BU</th><th>BV</th><th>BW</th><th>BX</th><th>BY</th><th>BZ</th><th>CA</th><th>CB</th><th>CC</th><th>CD</th><th>CE</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">txt_nme_taxpayer</td><td style="font-weight: bold;;">txt_nme_street</td><td style="font-weight: bold;;">city state </td><td style="font-weight: bold;;">txt_addr_1</td><td style="font-weight: bold;;">txt_parcel</td><td style="font-weight: bold;;">Year 10</td><td style="font-weight: bold;;">Year 9</td><td style="font-weight: bold;;">Year 8</td><td style="font-weight: bold;;">Year 7</td><td style="font-weight: bold;;">Year 6</td><td style="font-weight: bold;;">Year 5</td><td style="font-weight: bold;;">Year 4</td><td style="font-weight: bold;;">Year 3</td><td style="font-weight: bold;;">Year 2</td><td style="font-weight: bold;;">Year 1</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">QUINCY'S REALTY INC % RASH & A</td><td style=";">19900 HOLIDAY LN</td><td style=";">CHARLOTTE, NC</td><td style=";">PO BOX 1600</td><td style="text-align: right;;">319217</td><td style="text-align: right;;">0</td><td style="text-align: right;;">500.55</td><td style="text-align: right;;">516.22</td><td style="text-align: right;;">712.71</td><td style="text-align: right;;">768.86</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">780.71</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">FERGUSON, BENJAMIN</td><td style=";">10733 MONTECARLO DR</td><td style=";">CHARLOTTE, NC</td><td style=";">3508 SARGEANT DR</td><td style="text-align: right;;">1518407</td><td style="text-align: right;;">20.44</td><td style="text-align: right;;">23.51</td><td style="text-align: right;;">80.3</td><td style="text-align: right;;">81.9</td><td style="text-align: right;;">88.23</td><td style="text-align: right;;">87.6</td><td style="text-align: right;;">89.17</td><td style="text-align: right;;">89.17</td><td style="text-align: right;;">89.17</td><td style="text-align: right;;">89.17</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">HARVEY, MARIE H</td><td style=";">12011 HILLCREST LN</td><td style=";">HUNTERSVILLE, NC</td><td style=";">PO BOX 555</td><td style="text-align: right;;">1724517</td><td style="text-align: right;;">97.24</td><td style="text-align: right;;">97.24</td><td style="text-align: right;;">198.2</td><td style="text-align: right;;">202.16</td><td style="text-align: right;;">217.78</td><td style="text-align: right;;">216.24</td><td style="text-align: right;;">220.1</td><td style="text-align: right;;">220.1</td><td style="text-align: right;;">220.1</td><td style="text-align: right;;">220.1</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">EVANS, KENNETH RAY</td><td style=";">11922 LAKEWOOD DR</td><td style=";">HUNTERSVILLE, NC</td><td style=";">11922 LAKEWOOD DR</td><td style="text-align: right;;">1724813</td><td style="text-align: right;;">134.35</td><td style="text-align: right;;">134.35</td><td style="text-align: right;;">304.92</td><td style="text-align: right;;">311.01</td><td style="text-align: right;;">335.04</td><td style="text-align: right;;">332.67</td><td style="text-align: right;;">338.61</td><td style="text-align: right;;">338.61</td><td style="text-align: right;;">338.61</td><td style="text-align: right;;">338.61</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">GREEN TREE SERVICING LLC .</td><td style=";">12101 GIBRALTER DR</td><td style=";">CHARLOTTE, NC</td><td style=";">7031 ALBERT PICK RD</td><td style="text-align: right;;">1729409</td><td style="text-align: right;;">280.32</td><td style="text-align: right;;">280.32</td><td style="text-align: right;;">322.94</td><td style="text-align: right;;">322.94</td><td style="text-align: right;;">539.13</td><td style="text-align: right;;">535.4</td><td style="text-align: right;;">580.75</td><td style="text-align: right;;">601.75</td><td style="text-align: right;;">601.75</td><td style="text-align: right;;">601.75</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">HARRON, THOMAS</td><td style=";">SEAGLE ST</td><td style=";">HUNTERSVILLE, NC</td><td style=";">PO BOX 262</td><td style=";">01901518B</td><td style="text-align: right;;">304.89</td><td style="text-align: right;;">304.89</td><td style="text-align: right;;">361.64</td><td style="text-align: right;;">368.62</td><td style="text-align: right;;">396.18</td><td style="text-align: right;;">393.46</td><td style="text-align: right;;">436.27</td><td style="text-align: right;;">457.27</td><td style="text-align: right;;">457.27</td><td style="text-align: right;;">457.27</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">LIPSCOMB, MARY</td><td style=";">300 CENTRAL AV</td><td style=";">CHARLOTTE, NC</td><td style=";">131 MOORE STREET</td><td style="text-align: right;;">1907418</td><td style="text-align: right;;">17.13</td><td style="text-align: right;;">18.25</td><td style="text-align: right;;">18.25</td><td style="text-align: right;;">20.99</td><td style="text-align: right;;">20.99</td><td style="text-align: right;;">70.13</td><td style="text-align: right;;">77.88</td><td style="text-align: right;;">77.88</td><td style="text-align: right;;">77.88</td><td style="text-align: right;;">77.88</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">BEAVER, JOHNSIE MAE</td><td style=";">103 CENTRAL AV</td><td style=";">CHARLOTTE, NC</td><td style=";">2025 OAKLAWN AVE</td><td style="text-align: right;;">1908114</td><td style="text-align: right;;">202.21</td><td style="text-align: right;;">233.09</td><td style="text-align: right;;">233.09</td><td style="text-align: right;;">364.69</td><td style="text-align: right;;">371.73</td><td style="text-align: right;;">399.53</td><td style="text-align: right;;">396.79</td><td style="text-align: right;;">439.66</td><td style="text-align: right;;">460.66</td><td style="text-align: right;;">460.66</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">HOUSTON, ANNIE MAE</td><td style=";">13339 NEW HAVEN DR</td><td style=";">HUNTERSVILLE, NC</td><td style=";">13339 NEW HAVEN DR</td><td style="text-align: right;;">1908305</td><td style="text-align: right;;">258.81</td><td style="text-align: right;;">323.07</td><td style="text-align: right;;">535.59</td><td style="text-align: right;;">544.28</td><td style="text-align: right;;">598.88</td><td style="text-align: right;;">581.5</td><td style="text-align: right;;">599.63</td><td style="text-align: right;;">604.14</td><td style="text-align: right;;">604.14</td><td style="text-align: right;;">619.07</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">STONEHAVEN DEVELOPMENT INC</td><td style=";">12403 BRAVINGTON RD</td><td style=";">CHARLOTTE, NC</td><td style=";">PO BOX 17073</td><td style="text-align: right;;">1926536</td><td style="text-align: right;;">176.34</td><td style="text-align: right;;">176.34</td><td style="text-align: right;;">254.1</td><td style="text-align: right;;">259.18</td><td style="text-align: right;;">279.2</td><td style="text-align: right;;">277.23</td><td style="text-align: right;;">282.18</td><td style="text-align: right;;">282.18</td><td style="text-align: right;;">282.18</td><td style="text-align: right;;">282.18</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">BLACK, NORMAN D</td><td style=";">4218 EAGLE CHASE DR</td><td style=";">CHARLOTTE, NC</td><td style=";">8088 GRAHAM RD</td><td style="text-align: right;;">2311140</td><td style="text-align: right;;">506.18</td><td style="text-align: right;;">538.74</td><td style="text-align: right;;">669.38</td><td style="text-align: right;;">669.38</td><td style="text-align: right;;">883.04</td><td style="text-align: right;;">953.81</td><td style="text-align: right;;">967.71</td><td style="text-align: right;;">970.71</td><td style="text-align: right;;">970.71</td><td style="text-align: right;;">970.71</td></tr></tbody></table>
10 Years
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
How would you know whether a number was a duplicate or actually another "tax bill" in the same amount? Why do you need to change these to zeros? It is for purposes of display or do you really want the numbers changed to zero?
 
Upvote 0
How about something like:

=IF(CE2=BV2:CD2,CE2,"0")

Then copy it across to the other cells

That may work
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top