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

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)

"flow23" wrote in message
...
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

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


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531468

Otherwise, if, for example, 100 Jan is meant to be different to 100 Feb
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.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531468

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?

"Bob Phillips" wrote:

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)

"flow23" wrote in message
...
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

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)

"flow23" wrote in message
...
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?

"Bob Phillips" wrote:

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)

"flow23" wrote in message
...
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

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.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531468