Remember Me?

#1
May 11th 15, 05:10 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2015 Posts: 8
Find duplicates w/3 criteria

I have tried various formulas to make this work and I'm spinning my wheels and hoping for help!
I would like in column D a Y or N to indicate the cells in that row are duplicates because they show up elsewhere in that column.
ROW Col A ColB ColC ColD
Dt Tmstamp Item ID Work Order Result
1 5/7/2015 14:20 142532 775502 N
2 5/7/2015 14:24 142416 775502 N
3 5/7/2015 14:25 142416 775502 Y
4 5/7/2015 14:25 142416 775502 Y
5 5/7/2015 14:25 142416 775502 Y
6 5/7/2015 14:48 171071 N

So rows 3, 4 and 5 are duplicates in my example would be the desired result of the formula which I'm trying to put into Column D. I have tried {=IF(ISNA(MATCH(1,(\$B145=sheet1!\$B\$1:\$B\$1000)*(\$D1 45=sheet1!\$D\$1:\$D\$1000)*(\$H145=sheet1!\$H\$1:\$H\$1000 ),0)),"n","Y")}, but all I get are Y's

#2
May 11th 15, 05:24 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,872
Find duplicates w/3 criteria

#3
May 11th 15, 05:28 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,872
Find duplicates w/3 criteria

Hi,

Am Mon, 11 May 2015 18:24:16 +0200 schrieb Claus Busch:

in D2 try:
=IF(MATCH(B2&C2,B\$1:B\$100&C\$1:C\$100,0)=ROW(),"N"," Y")

if you have Excel versio n 2007 or later you can also try in D2:
=IF(COUNTIFS(\$B\$2:B2,B2,\$C\$2:C2,C2)=1,"N","Y")
and copy down

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
#4
May 11th 15, 05:39 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,872
Find duplicates w/3 criteria

Hi again,

Am Mon, 11 May 2015 18:28:36 +0200 schrieb Claus Busch:

in D2 try:
=IF(MATCH(B2&C2,B\$1:B\$100&C\$1:C\$100,0)=ROW(),"N"," Y")

if you have Excel versio n 2007 or later you can also try in D2:
=IF(COUNTIFS(\$B\$2:B2,B2,\$C\$2:C2,C2)=1,"N","Y")

sorry, but you wanted the column with teh timestamp as criteria also:
Then try in D2:
=IF(MATCH(A2&B2&C2,A\$1:A\$100&B\$1:B\$100&C\$1:C\$100,0 )=ROW(),"N","Y")
and enter with CTRL+Shift+Enter.
The first occurance is not signed as duplicate! Only the following
entries.

Or:
=IF(COUNTIFS(\$A\$2:A2,A2,\$B\$2:B2,B2,\$C\$2:C2,C2)=1," N","Y")
This formula writes a Y to all duplicates

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

 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 Sam.D Excel Discussion (Misc queries) 7 February 10th 10 02:36 PM Linda \(RQ\) New Users to Excel 0 April 10th 09 02:38 AM aileen Excel Programming 3 December 11th 08 06:03 PM Kevin Excel Discussion (Misc queries) 2 February 14th 08 12:59 PM Danielle Excel Discussion (Misc queries) 1 December 14th 07 04:56 PM

All times are GMT +1. The time now is 03:49 PM.