Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for duplicates - think this is simple | Excel Discussion (Misc queries) | |||
i need to find duplicates! ASAP | Excel Worksheet Functions | |||
Marking Duplicates | Excel Worksheet Functions | |||
Duplicates | Excel Discussion (Misc queries) | |||
Finding Duplicates | Excel Worksheet Functions |