How to Fix this issue: Remove Duplicates

T

tahir573

New Member
Joined
Dec 31, 2008
Messages
44
Hello all,

I want to remove duplicates from my worksheet which is looking something like this:-

Sajjad Ali

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:215px;"><col style="width:110px;"><col style="width:145px;"><col style="width:75px;"><col style="width:75px;"><col style="width:1055px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="font-weight:bold; text-align:center; ">Name</td><td style="font-weight:bold; text-align:center; ">MSISDN</td><td style="font-weight:bold; text-align:center; ">Date</td><td style="font-weight:bold; text-align:center; ">Date</td><td style="font-weight:bold; text-align:center; ">Time</td><td style="font-weight:bold; text-align:left; ">Location</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 8:35 AM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">8:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 8:35 AM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">8:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 10:36 AM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">10:36 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 11:36 AM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">11:36 AM</td><td>Plot No. 14 Jamna Das Colony No. II Mirpur Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 11:36 AM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">11:36 AM</td><td>Plot No. 14 Jamna Das Colony No. II Mirpur Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 1:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">1:36 PM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 1:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">1:36 PM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 2:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">2:36 PM</td><td>Market Area Mirpurkhas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 2:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">2:36 PM</td><td>Market Area Mirpurkhas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 3:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">3:36 PM</td><td>Plot No. A/8-80 Rava Chand Garden Deh 109 Taluka Mirpru Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 3:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">3:36 PM</td><td>Plot No. A/8-80 Rava Chand Garden Deh 109 Taluka Mirpru Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 5:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">5:36 PM</td><td>Plot No. 19 Near Gulberg Lawn Mirpur Khas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 5:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">5:36 PM</td><td>Plot No. 19 Near Gulberg Lawn Mirpur Khas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 6:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">6:36 PM</td><td>Survey No: 9-1 Jaryan No: 180 Deh Aabri Taluka/District Hyderabad Sindh. Kahsana Moree</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">01/10/2011 6:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">6:36 PM</td><td>Survey No: 9-1 Jaryan No: 180 Deh Aabri Taluka/District Hyderabad Sindh. Kahsana Moree</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 12:35 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">12:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 12:35 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">12:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 1:35 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">1:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 2:35 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">2:35 AM</td><td>Plot No. 14 Jamna Das Colony No. II Mirpur Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 2:35 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">2:35 AM</td><td>Plot No. 14 Jamna Das Colony No. II Mirpur Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 4:35 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">4:35 AM</td><td>Plot No. 7/53 Muslimabad Sir Syed Road Mirpur Khas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 8:36 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">8:36 AM</td><td>Gharibabad Mirpur Khas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">27</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 8:36 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">8:36 AM</td><td>Gharibabad Mirpur Khas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">28</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 10:36 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">10:36 AM</td><td>Jaryan No: 188 Deh Hinjoi Tappo Sonaheen Taluka District Umerkot Sindh Mirpurkhas-Umerkot Highway</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">29</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 10:36 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">10:36 AM</td><td>Jaryan No: 188 Deh Hinjoi Tappo Sonaheen Taluka District Umerkot Sindh Mirpurkhas-Umerkot Highway</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">30</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 11:38 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">11:38 AM</td><td>Sameja Mohalla UmerKot</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">31</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">03/10/2011 11:38 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">11:38 AM</td><td>Sameja Mohalla UmerKot</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">32</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 2:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">2:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">33</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 3:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">3:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">34</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 3:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">3:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">35</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 4:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">4:35 AM</td><td>Plot No. 14 Jamna Das Colony No. II Mirpur Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">36</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 4:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">4:35 AM</td><td>Plot No. 14 Jamna Das Colony No. II Mirpur Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">37</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 5:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">5:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">38</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 5:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">5:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">39</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 6:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">6:35 AM</td><td>Plot No. 14 Jamna Das Colony No. II Mirpur Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">40</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 6:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">6:35 AM</td><td>Plot No. 14 Jamna Das Colony No. II Mirpur Khas. Mirpur Khas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">41</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 7:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">7:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">42</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 7:35 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">7:35 AM</td><td>THAMSABAD MIRPUR KHASE Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">43</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 8:36 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">8:36 AM</td><td>Market Area Mirpurkhas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">44</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 8:36 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">8:36 AM</td><td>Market Area Mirpurkhas Mirpurkhas</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">45</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 9:36 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">9:36 AM</td><td>Jiriyan No: 435 Survey No: 150 Deh Lucky Tappo Kamro Talika & Distt Tando Alayaar Sultanabad</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">46</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 9:36 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">9:36 AM</td><td>Jiriyan No: 435 Survey No: 150 Deh Lucky Tappo Kamro Talika & Distt Tando Alayaar Sultanabad</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">47</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 10:36 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">10:36 AM</td><td>PTCL EXCHANGE Tando Jam</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">48</td><td>SPO General (Mirpur Khas) Sajjad Ali</td><td style="text-align:right; ">+923362550516</td><td style="text-align:right; ">04/10/2011 10:36 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">10:36 AM</td><td>PTCL EXCHANGE Tando Jam</td></tr></tbody></table>
on 1st oct he visit:

Thamsabad Blablabla
Thamsabad Blablabla
Thamsabad Blablabla
Plot # blablalblablbl
Plot # blablalblablbl
Thamsabad Blablabla
Market Area MPK
Market ARea MPK
Plot # 19 Near Gulberg
Plot # 19 Near Gulberg
Plot # 19 Near Gulberg

on 3rd he visit:
Thamsabad Blablabla
Thamsabad Blablabla
Thamsabad Blablabla
Plot # 14 Jamna Das
Plot # 14 Jamna Das
Plot # 14 Jamna Das
Gharibabad MPK
Gharibabad MPK

on 4th he visit:
Thamsabad Blablabla
Thamsabad Blablabla
Thamsabad Blablabla
Plot # 14 Jamna Das
Plot # 14 Jamna Das
Plot # 14 Jamna Das
Market Area MPK
Market Area MPK
PTCL Exchange Tando Jam
PTCL Exchange Tando Jam
PTCL Exchange Tando Jam

i want to remove duplicates from locations not date.. i mean..

On 1st
Thamsabad Blablabla
Plot # blablalblablbl
Market Area MPK
Plot # 19 Near Gulberg
(Values that are repeating is removed)

On 3rd
Thamsabad Blablabla
Plot # 14 Jamna Das
Gharibabad MPK

and so on.. I Hope u got it.. please help
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
If you are using Excel 2007

Then Do like this..

DATA-->REMOVE DUPLICATES-->SELECT ONLY LOCATION COLUMN FROM DIALOGUE BOX-->OK

HOPE IT WILL WORK
 
Upvote 0
This will remove any row that is a duplicate.
That means :- If the entire Data any row (columns "A to F") are the same then the duplicate will be removed.
If you want the duplicates based on Specific columns only, please state which columns.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG23Nov29
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    txt = Join(Application.Transpose(Application.Transpose(Dn.Resize(, 6).Value)))
    [COLOR="Navy"]If[/COLOR] Not .Exists(txt) [COLOR="Navy"]Then[/COLOR]
        .Add txt, ""
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
I want to remove duplicates from column F, and i have excel 2003

but there's a problem.. it will remove all duplicates.. means

Thamsabad is visited on date 1, 3 and 4... this will remove all thamsabad entries

i want to remove duplicates from column F and with Dated for example:

on 1st oct he visit:

Thamsabad Blablabla
Thamsabad Blablabla
Thamsabad Blablabla
Plot # blablalblablbl
Plot # blablalblablbl
Thamsabad Blablabla
Market Area MPK
Market ARea MPK
Plot # 19 Near Gulberg
Plot # 19 Near Gulberg
Plot # 19 Near Gulberg

on 3rd he visit:
Thamsabad Blablabla
Thamsabad Blablabla
Thamsabad Blablabla
Plot # 14 Jamna Das
Plot # 14 Jamna Das
Plot # 14 Jamna Das
Gharibabad MPK
Gharibabad MPK

on 4th he visit:
Thamsabad Blablabla
Thamsabad Blablabla
Thamsabad Blablabla
Plot # 14 Jamna Das
Plot # 14 Jamna Das
Plot # 14 Jamna Das
Market Area MPK
Market Area MPK
PTCL Exchange Tando Jam
PTCL Exchange Tando Jam
PTCL Exchange Tando Jam

i want to remove duplicates from locations not date.. i mean..

On 1st
Thamsabad Blablabla
Plot # blablalblablbl
Market Area MPK
Plot # 19 Near Gulberg
(Values that are repeating is removed)

On 3rd
Thamsabad Blablabla
Plot # 14 Jamna Das
Gharibabad MPK

and so on.. I Hope u got it.. please help
 
Upvote 0
Perhaps This !!!
Code: 
[COLOR="Navy"]Sub[/COLOR] MG24Nov56
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("F1"), Range("F" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, ""
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG it did'nt work my dear...

Sheet1

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:215px;"><col style="width:110px;"><col style="width:145px;"><col style="width:75px;"><col style="width:75px;"><col style="width:1055px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">01/10/2011 3:36 PM</td><td style="text-align:center; ">01/10/2011</td><td style="text-align:center; ">3:36 PM</td><td>Khasra No. 9277/4829 Khatooni No. 185 Khewat No. 92 situated at village Daftu Feroz Pur Daftu Road District Kasoor. Daftoo</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">03/10/2011 1:35 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">1:35 AM</td><td>Khasra No. 1642/1 Khatooni No. 998 Khewat No. 662 situated at Arorah Near Haweli Haji Bashir Ahmed Tehsil & District Kasoor Daftoo - Rao Khan Wala</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">03/10/2011 10:36 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">10:36 AM</td><td>PTCL EXCHANGE Rao Khan Wala</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">03/10/2011 11:38 AM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">11:38 AM</td><td>Property# Khasra# 160/1994 Khatooni# 640 Khewat# 219 Haveli Teekha Vali P/O Khas Raja Jang Tehsil & District Kasur. Bullay Shah Paper Mills</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">03/10/2011 1:36 PM</td><td style="text-align:center; ">03/10/2011</td><td style="text-align:center; ">1:36 PM</td><td>Khattoni #1160 Khewat #667/627 Khasra #3892 Moza Khara Tehsil & Dist Kasur khara</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">04/10/2011 11:36 AM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">11:36 AM</td><td>Khasra No. 11/2365 Khewat No. 604-608 Khatooni No. 197 situated at Mouza Handaal Tehsil Kot Rada Kishan District Kasoor. Handal</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">04/10/2011 1:36 PM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">1:36 PM</td><td>Khasra# 2997 Khewat# 355 Khatooni# 734 Situated at Mouza Matta Tehsil Kot Radha Kishan Distt Kasur Metta</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">04/10/2011 2:36 PM</td><td style="text-align:center; ">04/10/2011</td><td style="text-align:center; ">2:36 PM</td><td>Basti Noor Shah Kasur Kasur</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">05/10/2011 8:36 AM</td><td style="text-align:center; ">05/10/2011</td><td style="text-align:center; ">8:36 AM</td><td>Khasra No. 1196/1110 Khatooni No. 2546 situated at Grav Yard Road Munir Shaheed Colony District Kasur. Kasur</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">06/10/2011 9:36 PM</td><td style="text-align:center; ">06/10/2011</td><td style="text-align:center; ">9:36 PM</td><td>Location Temporarily Unavailable</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">07/10/2011 9:36 AM</td><td style="text-align:center; ">07/10/2011</td><td style="text-align:center; ">9:36 AM</td><td>Plot No. 31 Al Qadduous Colony Near Lalazar Colony Lahore. Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">07/10/2011 3:37 PM</td><td style="text-align:center; ">07/10/2011</td><td style="text-align:center; ">3:37 PM</td><td>Kachi Kothi Nawab Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">07/10/2011 7:36 PM</td><td style="text-align:center; ">07/10/2011</td><td style="text-align:center; ">7:36 PM</td><td>Qita# 17 Khewat# 16 Khatooni# 55 to 58 Kot Islampura Tehsil Cantt Distt Lahore Lahore -Kasur Road</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">10/10/2011 1:36 PM</td><td style="text-align:center; ">10/10/2011</td><td style="text-align:center; ">1:36 PM</td><td>Jan Muhammad Road Anarkali Lahore Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">10/10/2011 4:36 PM</td><td style="text-align:center; ">10/10/2011</td><td style="text-align:center; ">4:36 PM</td><td>Khasra No. 2384 situated at Retigun Road Haijveri Park Lahore Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">23</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">10/10/2011 5:36 PM</td><td style="text-align:center; ">10/10/2011</td><td style="text-align:center; ">5:36 PM</td><td>Property No. S-93-R-30/A Syman Tower 25B Lower Mal Lahore Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">24</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">10/10/2011 6:36 PM</td><td style="text-align:center; ">10/10/2011</td><td style="text-align:center; ">6:36 PM</td><td>6-A Begum Road Link Lytton Road Mozang Lahore. Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">25</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">10/10/2011 7:36 PM</td><td style="text-align:center; ">10/10/2011</td><td style="text-align:center; ">7:36 PM</td><td>Property Plot # 23-A Khata# 65 Khasra # 190 Khatooni # 75 situated at Moza Doloon Kallan Teshil Cantt & District Lahore Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">26</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">11/10/2011 12:36 PM</td><td style="text-align:center; ">11/10/2011</td><td style="text-align:center; ">12:36 PM</td><td>Property Khasra# 7254 Khatooni# 162 Khewat# 122 Situated at Delay Wala P/O Chunian Tehsil Chunian District Kasur Kasur -Chunian Road</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">27</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">12/10/2011 11:36 AM</td><td style="text-align:center; ">12/10/2011</td><td style="text-align:center; ">11:36 AM</td><td>Khasra No.1583 10/195 khewat No. 109 Khatoni No. 126 Mauza Jhulkay Lahore. Lahore-Kasur</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">28</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">12/10/2011 1:36 PM</td><td style="text-align:center; ">12/10/2011</td><td style="text-align:center; ">1:36 PM</td><td>Bandianwala Pull Lahore Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">29</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">17/10/2011 10:36 AM</td><td style="text-align:center; ">17/10/2011</td><td style="text-align:center; ">10:36 AM</td><td>Mental Hospital Shadman Lahore Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">30</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">17/10/2011 11:36 AM</td><td style="text-align:center; ">17/10/2011</td><td style="text-align:center; ">11:36 AM</td><td>Apwa College Lahore Lahore</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">31</td><td>SSE Farm (Kasur) Shahzad Anwar</td><td style="text-align:right; ">+923362550541</td><td style="text-align:right; ">17/10/2011 12:36 PM</td><td style="text-align:center; ">17/10/2011</td><td style="text-align:center; ">12:36 PM</td><td>Plot 62 Main Market Shadman Shargah Center Lahore Lahore</td></tr></tbody></table>

u can see it removes many entries...

Dated 1, 3, 4, 5, 6, 7, then 10 (it skip 8 and 9)
Dated 11, 12 then 17 (skip 13, 14, 15, 16)
and so on..

please help
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
489
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
A
  • Locked
  • Question
values across tables found by name
Replies
2
Views
400
Fluff
Fluff
R
  • Question
Random
Replies
1
Views
477
Fluff
Fluff
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,216,783
Messages
6,132,684
Members
449,748
Latest member
freestuffman

Share this page

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