Finding unique values in excel column

[kimsalz]

Hello,

I'm working with a data set of 14,000+ rows, 21 columns.

Since pivot tables and deeper analysis needs to be done, conditional formatting won't don't do

I'd like to be able to identify the first unique item, ideally with a 1 (0 would indicate it's a duplicate ).

I thought I had it with: =(COUNTIF($A$1:$A$15,$A13)=1)+0 (I found this on some site, but can't find it now.)

However, at a closer look, it's not quite working; 0 is all duplicates (even the first instance).

If I use =COUNTIF($A$1:$A$15,$A13), I get 1's for non duplicated items, and a number >1 for items that are duplicated, which indicates the number of instances it occurs in the column.

I need a mixture of this (I think).
Below is a subset of the data (just 15 rows and one column
[TABLE="width: 781"]
<tbody>[TR]
[TD]Item
[/TD]
[TD]1st try
[/TD]
[TD]2nd try
[/TD]
[TD]Ideally I want[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1SOI Switch
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1SPD channels; Fixed custom width
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Staggered
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Submitted
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Thawed
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Thawed
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Thawing main form so that Ann can uxxxx 124ABC.
[/TD]
[TD]#VALUE!
[/TD]
[TD]#VALUE!
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Untested Wafer
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Wirebond
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1X1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1X-FAB (Essonnes, France) - 200mm
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Y
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Yes
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Yes
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12345671_XX0022_X1Yes
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As you can see I have an error in red that I need to figure out, and there are a handful of errors in the 14,000+ rows of data.

Is there any chance you can help me??

As I said there's more analysis to do, but I think this step is a step closer to the end. (I hope anyway).

Thanks,
Kim

 

[Fluff]

How about

Excel 2013/2016
	A	B
1	Item
2	12345671_XX0022_X1SOI Switch	1
3	12345671_XX0022_X1SPD channels; Fixed custom width	1
4	12345671_XX0022_X1Staggered	1
5	12345671_XX0022_X1Submitted	1
6	12345671_XX0022_X1Thawed	1
7	12345671_XX0022_X1Thawed	0
8	12345671_XX0022_X1Thawing main form so that Ann can uxxxx 124ABC.	1
9	12345671_XX0022_X1Untested Wafer	1
10	12345671_XX0022_X1Wirebond	1
11	12345671_XX0022_X1X1	1
12	12345671_XX0022_X1X-FAB (Essonnes, France) - 200mm	1
13	12345671_XX0022_X1Y	1
14	12345671_XX0022_X1Yes	1
15	12345671_XX0022_X1Yes	0
16	12345671_XX0022_X1Yes	0
sheet2

Cell Formulas
Range	Formula
B2	=IF(COUNTIF(A$2:A2,A2)=1,1,0)

 

[kimsalz]

Thank you. I believe this worked!

Kim

 

[Fluff]

Glad to help & thanks for the feedback