Remember Me?

#1
February 3rd 09, 08:25 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Feb 2009 Posts: 2
Duplicates and unique values

hi,
I have the data as follow:
Month Employee IC Department
Jan Tom 123 HR
Feb Tom 123 HR
Mar Tom 123 IT

Expected result: Same department but different month is a duplicate. Same
person but NEW department is NOT a duplicate.
Month Employee IC Department Duplicates
(marked with 'x')
Jan Tom 123 HR
Feb Tom 123 HR x
Mar Tom 123 IT

To check for duplicates, I did... =IF(COUNTIF(C\$2:C20,C20)1,"x","") where
column C is the unique IC number. How do i make sure that my guy in a new
department is not treated as a duplicate because the current function will
result in all but only one to be duplicates.

#2
February 3rd 09, 09:22 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2008 Posts: 747
Duplicates and unique values

try this

Assumed Col A - Month
Col B - Employee
Col C - IC
Col D - Dept
Col E - Duplicate

in cell E2 put this formula and drag it down

=IF(COUNTIF(\$B\$2:\$B\$10,B2)1,IF(COUNTIF(\$D\$2:\$D\$10 ,D2)1,"X",""),"")

On Feb 3, 12:25*pm, sragor wrote:
hi,
I have the data as follow:
Month *Employee * * *IC * * * * *Department * * * * * *
Jan * * * Tom * * * * * * 123 * * * * HR * * * * * * * * * * * * * * * *
Feb * * * *Tom * * * * * *123 * * * * *HR * * * * * * * * * * * * * * * * *
Mar * * * Tom * * * * * * 123 * * * * *IT * *

Expected result: Same department but different month is a duplicate. Same
person but NEW department is NOT a duplicate.
Month *Employee * * * *IC * * * * * Department * * * * * * *Duplicates
(marked with 'x')
Jan * * * Tom * * * * * * * 123 * * * * HR * * * * * * * * * * * * * * * *
Feb * * * *Tom * * * * * * *123 * * * * HR * * * * * * * * * * * * * *x
Mar * * * Tom * * * * * * *123 * * * * *IT * * * * * * * * * * * *

To check for duplicates, I did... =IF(COUNTIF(C\$2:C20,C20)1,"x","") * where
column C *is the unique IC number. How do i make sure that my guy in a new
department is not treated as a duplicate because the current function will
result in all but only one to be duplicates.

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post bob Excel Worksheet Functions 4 November 10th 08 10:43 PM DOUG ECKERT[_2_] Excel Discussion (Misc queries) 0 May 8th 08 03:00 PM Janine Ball Excel Worksheet Functions 2 August 10th 07 12:54 PM Weissme Excel Discussion (Misc queries) 0 August 9th 06 04:35 PM jcpotwor Excel Discussion (Misc queries) 1 January 13th 06 02:02 AM

All times are GMT +1. The time now is 07:28 AM.