Formula for checking duplicates?

[Audiman]

Hey Guys.. Back again to check with the experts.

I have a list of 43,000+ part numbers. Some are duplicated, some are not. It would take me hours to go through and remove the duplicates and even then it may still contain duplicates. Is there some formula using arrays that I can use that will only take 1 of the part numbers in Cell A?

 

[lenze]

Chip Peason has a Macro that will do that here
You can also use Advanced Filter

lenze

 

[aysam]

You may use this code (NOT MINE)

[B]Sub RemoveDupes()[/B]
    'Add extra Column, "A" becomes "B"
    Columns(1).EntireColumn.Insert 
   'Filter out duplicates and copy unique list to "A"  
  Range("B1", Range("B65536").End(xlUp)).AdvancedFilter_ 
   Action:=xlFilterCopy, CopyToRange:=Rang("A1"),Unique:=True 
   'Add extra Column, "B" becomes "A"
    Columns(2).EntireColumn.Delete
[B]End Sub[/B]
</PRE>

 

[Audiman]

You guys are simply awesome

 

[mgirvin]

Advanced Filter, Unique Records is a great method.

If you needed a formula:

Here's the set up:

Excel Workbook
	A	B	C	D
1	Part #		Count UniquePart #	States
2	43-444		5	43-444
3	44-584			44-584
4	43-444			38-dsw
5	38-dsw			323-312
6	323-312			3333
7	3333
8	38-dsw
9
10
2003

Excel 2010

Here's the formula in cell C2 that is entered with keystrokes Ctrl + Shift + Enter:

=SUM(IF(FREQUENCY(IF(A2:A8<>"",MATCH("~"&A2:A8,A2:A8&"",0)),ROW(A2:A8)-ROW(A2)+1),1))

Here's the formula in cell D2 that is entered with keystrokes Ctrl + Shift + Enter and then copied down:

=IF(ROWS(D$2:D2)>C$2,"",INDEX($A$2:$A$8,SMALL(IF(FREQUENCY(IF($A$2:$A$8<>"",MATCH("~"&$A$2:$A$8,$A$2:$A$8&"",0)),ROW($A$2:$A$8)-ROW($A$2)+1),ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(D$2:D2))))