Finding Duplicate #'s in a column

[Neets]

Hello, I have a column containing numbers that all begin with "U", ie U9062 & U10874. what I'm trying to do is find the simplest way to identify duplicates in this column. There are about 22000 rows. I tried the function, If(A1=A2, "dup","") and that works unless there are 3 or more duplicates. I'm sure there is an easier way to go about this but I'm not VBA saavy...

Thanks in advance!

 

[JoeMo]

Maybe something like this - pull the formula in B1 down to cover all data in column A:

Excel Workbook
	A	B
1	data1
2	data2
3	data3
4	data4
5	data5
6	data6
7	data7
8	data1	dup
9	data9
10	data10
11	data11
12	data12
13	data13
14	data1	dup
15	data15
16	data16
17	data17
18	data18
19	data19
20	data20
Sheet3

 

[Rick Rothstein]

I have assumed from your posted formula that your data is sorted. Given that, does this formula do what you want...

= IF(OR(COUNTIF(A$1:A1,A1)>1,A1=A2),"dup","")

 

[Neets]

That finds some, but not all, for example:

<TABLE style="WIDTH: 118pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=158><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" span=2 width=79><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 12.75pt; BORDER-TOP: gray 0.5pt solid; BORDER-RIGHT: gray 0.5pt solid" id=td_post_2872294 class=xl63 height=17 width=79>U10007</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=79></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10007</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>dup</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10008</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>dup</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10008</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>dup</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10018</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>dup</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10019</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10020</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64></TD></TR></TBODY></TABLE>

Ideas?

 

[Rick Rothstein]

That finds some, but not all, for example:

<TABLE style="WIDTH: 118pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=158><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" span=2 width=79><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 12.75pt; BORDER-TOP: gray 0.5pt solid; BORDER-RIGHT: gray 0.5pt solid" id=td_post_2872294 class=xl63 height=17 width=79>U10007</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 width=79></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10007</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>dup</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10008</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>dup</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10008</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>dup</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10018</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64>dup</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10019</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl63 height=17>U10020</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64></TD></TR></TBODY></TABLE>

Ideas?

The formula I posted works correctly, but it is dependent on where your data starts. I assumed your data started in Row 1 and that you would put my formula in Row 1 also (and then copy it down). If your data starts in Row 2 (which I am guessing is the case), then the range addresses need to be adjusted. Put this formula on Row 2 somerwhere and then copy it down...

= IF(OR(COUNTIF(A$1:A2,A2)>1,A2=A3),"dup","")

By the way, the reason I set my original formula up assuming data started in Row 1 was because of this quoted text from your original message...

"If(A1=A2, "dup","") and that works unless..."

Your reference to A1 seemed to indicate there was data there.

 

[Charlie45]

Try Conditional formating to highlight the duplicates.

See following Link:

http://www.ozgrid.com/Excel/highlight-duplicates.htm

 

[rsulliva]

I believe this formula would work perfectly even if the data was unsorted.

Assums data starts in A2

=IF(COUNTIF(A:A,A2)>1,"Dup","")

Copy down.

 

[Neets]

Thanks for all the input, and yes, data started on A2 sorry bout that!! Got it working perfectly...thanks again!