Count Unique Values but have data so it can be used in pivots/lookups

[Cpl]

Hi everyone,
I am new to this forum, so please be patient. I have been searching for an answer to this question but finding it impossible to get a full answer.
I have added an example of my data below the question so hopefully that it will make sense.

I want to if there is duplicate invoice numbers only count the number of unique invoices and have this result in Column D. So for example invoice 30002227 is in there 3 times in cells C3-C5, I just want a 1 to appear in D3, and then zeros in D4 & D5. I want to have it this way so I can then use it in pivot tables and also for lookups etc.

This data list will be updated daily by our sales team, so I am hoping to get a formula that I can copy down and doesn't need manipulating daily to get it to work.

Hope that makes sense

Any help would be muchly appreciated.
Cheers
Carmel

Columns:
A B C D
<TABLE style="WIDTH: 238pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=316 border=0><COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black 1pt solid; WIDTH: 30pt; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: black" width=40 height=20>Whse</TD><TD class=xl69 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: #bfbfbf; WIDTH: 55pt; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: black" width=73>Processing</TD><TD class=xl70 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: #bfbfbf; WIDTH: 58pt; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: black" width=77>Invoice</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 95pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=126>Unique trans count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>301</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002226</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>300</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002227</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>300</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002227</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>300</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002227</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>201</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002228</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>300</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002230</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>200</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002233</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>300</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002232</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>302</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002234</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>200</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002237</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>301</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002235</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>150</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002239</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: black 1pt solid; BORDER-BOTTOM: #bfbfbf 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ffffcc" height=20>300</TD><TD class=xl72 style="BORDER-RIGHT: #bfbfbf 0.5pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc" align=right>25-Feb-11</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #bfbfbf; BORDER-LEFT: #bfbfbf; BORDER-BOTTOM: #bfbfbf 0.5pt solid; BACKGROUND-COLOR: #ffffcc">30002240</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[VoG]

Try in D2, copied down

=IF(COUNTIF(C$2:C2,C2)=1,1,0)