Display a 1 for the first of duplicates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

noelle - 04 Oct 2007 07:54 GMT

I have a list of records that have many duplicates. I need a formula that I
can use in the adjacent column to show a 1 for the first instance of a
duplicate and a 0 for any others. The list is sorted so all dupes are
together.

Reply to this Message

Stefi - 04 Oct 2007 08:29 GMT

Try this formula:
=--(MATCH(A2,A:A,0)=ROW(A2))
and drag it down as required!

Regards,
Stefi

„noelle” ezt írta:

> I have a list of records that have many duplicates. I need a formula that I
> can use in the adjacent column to show a 1 for the first instance of a
> duplicate and a 0 for any others. The list is sorted so all dupes are
> together.

Reply to this Message

noelle - 04 Oct 2007 15:10 GMT

Great, thanks. That worked!

Would you mind explaining the components so I understand how it works? It's
really been bugging me that I can't figure this out!

Thanks again.

> Try this formula:
> =--(MATCH(A2,A:A,0)=ROW(A2))
[quoted text clipped - 9 lines]
> > duplicate and a 0 for any others. The list is sorted so all dupes are
> > together.

Reply to this Message

Excel_Learner - 04 Oct 2007 10:16 GMT

Use this formula in cell B3:

=IF(COUNTIF(C$3:C3, C3)=2, 1, 0)
Let me know if it works.

Reply to this Message

AdamV - 04 Oct 2007 12:13 GMT

Note that the first solution above will flag the first occurence of each
value (duplicate or otherwise); the second solution will flag the second
occurence (=the first duplicate, literally). Choose the one that actually
matches your need.

Signature

Adam Vero
MCP, MOS Master, MLSS, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk

> Use this formula in cell B3:
>
[quoted text clipped - 5 lines]
> > duplicate and a 0 for any others. The list is sorted so all dupes are
> > together.

Reply to this Message