Setting a value to duplicate datas using countif

Araxxor

New Member
Currently facing a problem with duplicate datas.

 Invoice no. Unique No. Item Desc 1000123456 1 Pliers 1000123456 0 Nail 1000123456 0 Hammers 1222444444 1 Paper 1444222222 1 Nail 1223456789 1 Nail 1000123456 0 Planks 1112223334 1 Saw 1112223334 0 Hammer

<tbody>
</tbody>

So i would like to set those invoice numbers in the Unique No. column, those with duplicates a value of 1, (first entry of 1000123456 to 1, the rest a value of 0). Other invoice numbers with no duplicates (1222444444, 1444222222, etc to 1)

I tried using excel's sort and filter function but since my data has gaps in between, the rows without any data was set to 0 as well.

My goal is to find the number of Invoices issued without double counting the duplicates.

Any help is appreciated. Thanks in advance

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Maybe like this (feeling like formula is unnecessarily lengthy )

Excel 2010
ABCD
1Invoice no.Unique No.Item Desc
210001234561Pliers
310001234560Nail
410001234560Hammers
512224444441Paper
614442222221Nail
712234567891Nail
810001234560Planks
90
1011122233341Saw
1111122233340Hammer
Sheet2
Cell Formulas
RangeFormula
B2=IF(COUNTIF(\$A\$2:\$A\$11,A2)=1,1,IF(AND(COUNTIF(\$A\$2:\$A\$11,A2)>1,COUNTIF(\$A\$2:A2,A2)=1),1,0))

maybe something like...

Excel 2012
ABC
1Invoice no.Unique No.Item Desc
210001234561Pliers
310001234560Nail
410001234560Hammers
512224444441Paper
614442222221Nail
712234567891Nail
810001234560Planks
9
1011122233341Saw
1111122233340Hammer

</tbody>
Sheet2

Worksheet Formulas
CellFormula
B2=IF(A2="","",N(COUNTIF(\$A\$2:A2,A2)=1))

</tbody>

<tbody>
</tbody>

Replies
4
Views
136
Replies
7
Views
406
Replies
6
Views
787
Replies
1
Views
417
Replies
3
Views
390

1,221,525
Messages
6,160,329
Members
451,637
Latest member
hvp2262

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.

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