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 |
Find duplicates w/3 criteria
|
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 |
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 |
All times are GMT +1. The time now is 02:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com