Duplicates

F

flow23

I have two columns..

ID MONTH
100 Jan-04 Unique
200 Jan-04 Unique
200 Jan-04
300 Feb-04 Unique
300 Feb-04
300 Feb-04


what I want is a macro to write "Unique" in the third column if " ID " is
there only once in a month. and in case its there more than once in the same
month. only one "Unique" in the third column


any help apprecaited
 
B

Bob Phillips

Pt this in C2 and copy down

=IF(SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2=B2))>1,"","Unique")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

John James

If ID number doesn't repeat in a following month, in cell C2 enter:
=IF(COUNTIF($A$2:A2,A2)=1,"Unique","")

Then copy down
 
J

John James

Otherwise, if, for example, 100 Jan is meant to be different to 100 Fe
then enter in cell C2:
=A2&B2
and enter in cell D2:
=IF(COUNTIF($C$2:C2,C2)=1,"Unique","")

Copy these formulae down and hide column C if desired
 
F

flow23

Thanks It works great.
I also added few more arrays to it and incorporated it into a macro.

but the problem is speed.
there are 12000 records

anyway to do it quicker?
 
B

Bob Phillips

You could try this but it is likely very similar

=IF(SUM(($A$2:A2=A2)*($B$2:B2=B2))>1,"","Unique")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

John James

Hi flow23,

To solve the speed issue, eliminate as many arrays as possible. They
are notoriously slow for large spreadsheets. e.g. Try entering the
formula I'd suggested as they are not arrays.
 
Top