Duplicates
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 |
Duplicates
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 |
Duplicates
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 |
Duplicates
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 |
Duplicates
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 |
Duplicates
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 |
Duplicates
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 |
All times are GMT +1. The time now is 12:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com