Formula for checking duplicates?

Audiman

New Member
Joined
Sep 10, 2009
Messages
31
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Chip Peason has a Macro that will do that here
You can also use Advanced Filter

lenze
 

aysam

Board Regular
Joined
Jun 24, 2009
Messages
217
You may use this code (NOT MINE)
Code:
[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>
 
Last edited:

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
Advanced Filter, Unique Records is a great method.

If you needed a formula:

Here's the set up:
Excel Workbook
ABCD
1Part #Count UniquePart #States
243-444543-444
344-58444-584
443-44438-dsw
538-dsw323-312
6323-3123333
73333
838-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))))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,918
Members
430,244
Latest member
Ireland1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top