Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying unique values among duplicates | Excel Worksheet Functions | |||
Counting Unique Values with Duplicates | Excel Discussion (Misc queries) | |||
How do I sum unique values among duplicates horizontally | Excel Worksheet Functions | |||
check for duplicates, then sum unique values | Excel Discussion (Misc queries) | |||
Count Unique Values In A Filtered Row with Duplicates | Excel Discussion (Misc queries) |