# Formula for checking duplicates?

#### Audiman

##### New Member
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
Chip Peason has a Macro that will do that here
You can also use Advanced Filter

lenze

#### aysam

##### Board Regular
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"
Action:=xlFilterCopy, CopyToRange:=Rang("A1"),Unique:=True
'Add extra Column, "B" becomes "A"
Columns(2).EntireColumn.Delete
[B]End Sub[/B]
</PRE>``````

Last edited:

#### Audiman

##### New Member
You guys are simply awesome

#### mgirvin

##### Well-known Member
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))))

Replies
7
Views
534
Replies
13
Views
893
Replies
3
Views
248
Replies
3
Views
172
Replies
1
Views
237

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.

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.

### Which adblocker are you using?

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

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