Removing Duplicates with formula

[Helix242]

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?

=IF(W4="","",IF(COUNTIF(W4:W9999,W4)>1,"",W4))

=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

 

[MARK858]

Are your values numbers or text?

 

[Helix242]

Are your values numbers or text?

Hi there. They are and will always be text.

thanks

 

[MARK858]

Actually the below should work for both (adjust the ranges to suit)...

Excel 2016 (Windows) 64 bit
	A	B
1	Name
2	Ann	Ann
3	Ann	Betty
4	Betty	Carol
5	Carol
6	Carol
9	Ann
10	Ann
11	Betty
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX($A$2:$A$11, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$11), 0, 0), 0)), "")
B3	=IFERROR(INDEX($A$2:$A$11, MATCH(0, INDEX(COUNTIF($B$1:B2, $A$2:$A$11), 0, 0), 0)), "")
B4	=IFERROR(INDEX($A$2:$A$11, MATCH(0, INDEX(COUNTIF($B$1:B3, $A$2:$A$11), 0, 0), 0)), "")
B5	=IFERROR(INDEX($A$2:$A$11, MATCH(0, INDEX(COUNTIF($B$1:B4, $A$2:$A$11), 0, 0), 0)), "")

 

[Helix242]

Actually the below should work for both (adjust the ranges to suit)...

Excel 2016 (Windows) 64 bit

	A	B
Name
Ann	Ann
Ann	Betty
Betty	Carol
Carol
Carol
Ann
Ann
Betty

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]B2[/TH]
[TD="align: left"]=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])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]B3[/TH]
[TD="align: left"]=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])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]B4[/TH]
[TD="align: left"]=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])[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F0E0E0]#F0E0E0[/URL] "]B5[/TH]
[TD="align: left"]=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])[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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!