Removing Duplicates with formula

Helix242

Board Regular
Joined
Jun 28, 2016
Messages
76
Evening everyone,

Please can yyou help with an issue. I am trying to remove any duplicates from a sheet using a formula so this does not have to be done manually. However the 2 formulas I have used, do not work when there is more than one entry. Can anyone help?

Code:
=IF(W4="","",IF(COUNTIF(W4:W9999,W4)>1,"",W4))
Code:
=IF(W4="","",IF(COUNTIF(W4:W9999,W4)=1,"",W4))
These are the 2 I have used, but I am at a loss! :)

Many thanks in advance
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,724
Office Version
365, 2010
Platform
Windows, Mobile
Are your values numbers or text?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,724
Office Version
365, 2010
Platform
Windows, Mobile
Actually the below should work for both (adjust the ranges to suit)...

<b>Excel 2016 (Windows) 64 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255, 255, 255);border: 1px solid;border-collapse: collapse; border-color: rgb(182, 170, 166)"><colgroup><col width="25px" style="background-color: rgb(240, 224, 224)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(240, 224, 224);text-align: center;color: rgb(32, 17, 22)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Name</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Ann</td><td style="border-left: 1px solid black;;">Ann</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Ann</td><td style="border-left: 1px solid black;;">Betty</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Betty</td><td style="border-left: 1px solid black;;">Carol</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Carol</td><td style="border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Carol</td><td style="border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Ann</td><td style="border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Ann</td><td style="border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: rgb(51, 51, 51);;">Betty</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(182, 170, 166);border-top:none;text-align: center;background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255, 255, 255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255, 255, 255);border-collapse: collapse; border-color: rgb(182, 170, 166)"><thead><tr style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">B2</th><td style="text-align:left">=IFERROR(<font color="rgb(255">INDEX(<font color=" 0">$A$2:$A$11, MATCH(<font color=" 0)">0, INDEX(<font color="rgb(0">COUNTIF(<font color=" 0">$B$1:B1, $A$2:$A$11</font>), 0, 0</font>), 0</font>)</font>), ""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">B3</th><td style="text-align:left">=IFERROR(<font color="rgb(255">INDEX(<font color=" 0">$A$2:$A$11, MATCH(<font color=" 0)">0, INDEX(<font color="rgb(0">COUNTIF(<font color=" 0">$B$1:B2, $A$2:$A$11</font>), 0, 0</font>), 0</font>)</font>), ""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">B4</th><td style="text-align:left">=IFERROR(<font color="rgb(255">INDEX(<font color=" 0">$A$2:$A$11, MATCH(<font color=" 0)">0, INDEX(<font color="rgb(0">COUNTIF(<font color=" 0">$B$1:B3, $A$2:$A$11</font>), 0, 0</font>), 0</font>)</font>), ""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">B5</th><td style="text-align:left">=IFERROR(<font color="rgb(255">INDEX(<font color=" 0">$A$2:$A$11, MATCH(<font color=" 0)">0, INDEX(<font color="rgb(0">COUNTIF(<font color=" 0">$B$1:B4, $A$2:$A$11</font>), 0, 0</font>), 0</font>)</font>), ""</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Helix242

Board Regular
Joined
Jun 28, 2016
Messages
76
Actually the below should work for both (adjust the ranges to suit)...

Excel 2016 (Windows) 64 bit
AB
1Name
2AnnAnn
3AnnBetty
4BettyCarol
5Carol
6Carol
9Ann
10Ann
11Betty

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B2=IFERROR([COLOR=rgb(255]INDEX($A$2:$A$11, MATCH([COLOR=0)]0, INDEX([COLOR=rgb(0]COUNTIF($B$1:B1, $A$2:$A$11), 0, 0[/COLOR]), 0[/COLOR])), ""[/COLOR])
B3=IFERROR([COLOR=rgb(255]INDEX($A$2:$A$11, MATCH([COLOR=0)]0, INDEX([COLOR=rgb(0]COUNTIF($B$1:B2, $A$2:$A$11), 0, 0[/COLOR]), 0[/COLOR])), ""[/COLOR])
B4=IFERROR([COLOR=rgb(255]INDEX($A$2:$A$11, MATCH([COLOR=0)]0, INDEX([COLOR=rgb(0]COUNTIF($B$1:B3, $A$2:$A$11), 0, 0[/COLOR]), 0[/COLOR])), ""[/COLOR])
B5=IFERROR([COLOR=rgb(255]INDEX($A$2:$A$11, MATCH([COLOR=0)]0, INDEX([COLOR=rgb(0]COUNTIF($B$1:B4, $A$2:$A$11), 0, 0[/COLOR]), 0[/COLOR])), ""[/COLOR])

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
thank you very much for this. Something I should have asked initially is, is it possible to leave a blank where the duplicate one is?

many thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,100,139
Messages
5,472,747
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top