# Formula showing duplicates in large data set

#### MustaphaOj

##### New Member
Hi All I would like help with making a formula that helps identify duplicates in a very large data set.
So for example the below tables shows
The end result of what I would ideally want the error check to look like. If I have duplicates of the same code and activity name together I’d like it to show in the next column. Thanks in advance

 Activity ID Activity Name Duplicates 12344 Plates 3 12344 Plates 3 12355 Spoons 0 12344 Plates 3

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Peter_SSs

##### MrExcel MVP, Moderator
Could you use something like this, with 1 meaning no duplicates?
Not sure how big your data is so there could be a performance issue?

20 07 13.xlsm
ABC
1Activity IDActivity NameDuplicates
212344Plates3
312344Plates3
412355Spoons1
512344Plates3
Dupes
Cell Formulas
RangeFormula
C2:C5C2=COUNTIFS(A\$2:A\$10,A2,B\$2:B\$10,B2)

If you really need the zero, then the following but it significantly increases the processing required.
=IF(COUNTIFS(A\$2:A\$10,A2,B\$2:B\$10,B2)=1,0,COUNTIFS(A\$2:A\$10,A2,B\$2:B\$10,B2))

#### MustaphaOj

##### New Member
Thank you so much. The first option works perfectly. God bless you!

#### Peter_SSs

##### MrExcel MVP, Moderator
You are very welcome. Glad it worked for you. Thanks for letting us know.

Replies
4
Views
171
Replies
1
Views
738
Replies
5
Views
89
Replies
1
Views
76
Replies
19
Views
286

1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

### 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