Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Identifying unique values among duplicates bob Excel Worksheet Functions 4 November 10th 08 10:43 PM
Counting Unique Values with Duplicates DOUG ECKERT[_2_] Excel Discussion (Misc queries) 0 May 8th 08 03:00 PM
How do I sum unique values among duplicates horizontally Janine Ball Excel Worksheet Functions 2 August 10th 07 12:54 PM
check for duplicates, then sum unique values Weissme Excel Discussion (Misc queries) 0 August 9th 06 04:35 PM
Count Unique Values In A Filtered Row with Duplicates jcpotwor Excel Discussion (Misc queries) 1 January 13th 06 02:02 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017