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
<tbody>
</tbody> 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
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
| Item | 1st try | 2nd try | Ideally I want |
| 12345671_XX0022_X1SOI Switch | 1 | 1 | 1 |
| 12345671_XX0022_X1SPD channels; Fixed custom width | 1 | 1 | 1 |
| 12345671_XX0022_X1Staggered | 1 | 1 | 1 |
| 12345671_XX0022_X1Submitted | 1 | 1 | 1 |
| 12345671_XX0022_X1Thawed | 0 | 2 | 1 |
| 12345671_XX0022_X1Thawed | 0 | 2 | 0 |
| 12345671_XX0022_X1Thawing main form so that Ann can uxxxx 124ABC. | #VALUE! | #VALUE! | 1 |
| 12345671_XX0022_X1Untested Wafer | 1 | 1 | 1 |
| 12345671_XX0022_X1Wirebond | 1 | 1 | 1 |
| 12345671_XX0022_X1X1 | 1 | 1 | 1 |
| 12345671_XX0022_X1X-FAB (Essonnes, France) - 200mm | 1 | 1 | 1 |
| 12345671_XX0022_X1Y | 1 | 1 | 1 |
| 12345671_XX0022_X1Yes | 0 | 3 | 1 |
| 12345671_XX0022_X1Yes | 0 | 3 | 0 |
| 12345671_XX0022_X1Yes | 0 | 3 | 0 |
<tbody>
</tbody>
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
