Duplicates using macro

[weskyleo]

Somewhere I found the following to figure out the duplicates in a col but don't quite understand how it works
=IF(MAX(COUNTIF(K2:K9,K2:K9))>1,"Duplicate","No Duplicate")

Here the data is in the range K2:K9 which might have some duplicate entries. I guess I need help understanding COUNTIF's second parameter as a range of reference

Any help is appreciated

thanks

 

[Nalani]

Not sure what you want accomplished without some sort of data layout of your sheet.

Here's my take on it:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Duplicate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Duplicate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No Duplicate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No Duplicate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No Duplicate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Duplicate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Duplicate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No Duplicate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">No Duplicate</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Formula in L1 copied down:
=IF((COUNTIF($K$1:$K$9,$K$1:$K$9))>1,"Duplicate","No Duplicate")

Edit: Ooops - just saw that your data starts in row 2. Just adjust the formula to suit.

 

[weskyleo]

That still doesnt explain what is meant by the cell reference in the second parameter of COUNTIF
=IF(MAX(COUNTIF(K2:K9,K2:K9))>1,"Duplicate","No Duplicate")

The formula tells you the entries that are duplicated in a col(sortof macro version of remove duplicates which tells you whats duplicated and gives you and opportunity to view and manually remove)

You should see the following result

shaa Duplicate
shaa No Duplicate
yuu No Duplicate
yii No Duplicate
timm Duplicate
timm Duplicate
tim No Duplicate
him No Duplicate
timm No Duplicate

 

[snoopyhr]

Somewhere I found the following to figure out the duplicates in a col but don't quite understand how it works
=IF(MAX(COUNTIF(K2:K9,K2:K9))>1,"Duplicate","No Duplicate")

Here the data is in the range K2:K9 which might have some duplicate entries. I guess I need help understanding COUNTIF's second parameter as a range of reference

Any help is appreciated

thanks

Hope this helps

Excel Workbook
	K	L	M	N	O
1			Brokendown to components
2	List	Formula	counting	check for Max count	Result
3	Car	Duplicate	2	TRUE	Duplicate
4	House	Duplicate	2	TRUE	Duplicate
5	Boat	Duplicate	2	TRUE	Duplicate
6	Car	Duplicate	2	TRUE	Duplicate
7	House	Duplicate	2	TRUE	Duplicate
8	Boat	Duplicate	2	TRUE	Duplicate
9	Computer	No duplicate	1	FALSE	No Duplicate
10	Book	No duplicate	1	FALSE	No Duplicate
Sheet1

 

[weskyleo]

This is not the answer I'm getting. Removing duplicates from your answer will get rid of Car, House and Boat completely. My solution removes only the duplicate ones. So one time those entries should be present
Like the ans below

Car Duplicate
House Duplicate
Boat Duplicate
Car No Duplicate
House No Duplicate
Boat No Duplicate
computer No Duplicate
Book No Duplicate

Sorting and filtering on No Duplicates results in Car, House, Boat, Computer and Book . Did you miss something?

 

[snoopyhr]

Sorry can not help you.

 

[VoG]

Have you tries Advanced Filter?

 

[weskyleo]

The goal is to write a macro and identify and remove duplicates. Using advanced filter/remove duplicates features are inbuilt . The idea is not to use them

Regardless of what I have found (listed above) , can someone answer how to write this macro?