|
check for duplicates
Hi all,
I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
Hi,
Check CPearson web http://www.cpearson.com/excel/Duplicates.aspx "Rod" wrote: Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
Paste the below formula in C1 cell
=IF(OR(A1="",B1=""),"",IF(SUMPRODUCT(($A$1:$A1=$A1 )*($B$1:$B1=$B1))1,"DUPLICATES",IF(SUMPRODUCT(($A $1:$A1=$A1)*($B$1:$B1=$B1))=1,"NO DUPLICATES",""))) Copy the C1 cell formula and paste it for the remaining cells of C Column. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Rod" wrote: Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
Thanks for the reply, but i could not able to get from that.
I need to find duplicates comparing with 2 columns as said 1st and 4th rows duplicated Please help me once again thanks in advance "Eduardo" wrote: Hi, Check CPearson web http://www.cpearson.com/excel/Duplicates.aspx "Rod" wrote: Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
Thanks, but I need a formula like this
=IF(MAX(COUNTIF(A2:A11,A2:A11))1,"Duplicates","No Duplicates") but it will check two columns A and B Help me Thanks in advance "Ms-Exl-Learner" wrote: Paste the below formula in C1 cell =IF(OR(A1="",B1=""),"",IF(SUMPRODUCT(($A$1:$A1=$A1 )*($B$1:$B1=$B1))1,"DUPLICATES",IF(SUMPRODUCT(($A $1:$A1=$A1)*($B$1:$B1=$B1))=1,"NO DUPLICATES",""))) Copy the C1 cell formula and paste it for the remaining cells of C Column. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Rod" wrote: Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
May be this...
Paste the below formula in C1 cell =IF(COUNTIF(A:A,A1)=1,"NO DUPLICATES",IF(COUNTIF(A:A,A1)1,"DUPLICATES","")) Copy the C1 cell formula and paste it for the remaining cells of C Column. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Rod" wrote: Thanks, but I need a formula like this =IF(MAX(COUNTIF(A2:A11,A2:A11))1,"Duplicates","No Duplicates") but it will check two columns A and B Help me Thanks in advance "Ms-Exl-Learner" wrote: Paste the below formula in C1 cell =IF(OR(A1="",B1=""),"",IF(SUMPRODUCT(($A$1:$A1=$A1 )*($B$1:$B1=$B1))1,"DUPLICATES",IF(SUMPRODUCT(($A $1:$A1=$A1)*($B$1:$B1=$B1))=1,"NO DUPLICATES",""))) Copy the C1 cell formula and paste it for the remaining cells of C Column. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Rod" wrote: Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
Assuming no empty cells in either range.
Try this array formula** : =IF(SUM(IF(FREQUENCY(MATCH(A2:A7&B2:B7,A2:A7&B2:B7 ,0),ROW(A2:A7)-ROW(A2)+1),1))=COUNTA(A2:A7),"No Dupes","Dupes") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Rod" wrote in message ... Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
Sorry, may be I could not able to explain breifly
Say in column B are the folders and in column A are the files I need to find whether are there any duplicates files(cloumn A) in any of the folder(column B) Thanks in advance "Ms-Exl-Learner" wrote: May be this... Paste the below formula in C1 cell =IF(COUNTIF(A:A,A1)=1,"NO DUPLICATES",IF(COUNTIF(A:A,A1)1,"DUPLICATES","")) Copy the C1 cell formula and paste it for the remaining cells of C Column. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Rod" wrote: Thanks, but I need a formula like this =IF(MAX(COUNTIF(A2:A11,A2:A11))1,"Duplicates","No Duplicates") but it will check two columns A and B Help me Thanks in advance "Ms-Exl-Learner" wrote: Paste the below formula in C1 cell =IF(OR(A1="",B1=""),"",IF(SUMPRODUCT(($A$1:$A1=$A1 )*($B$1:$B1=$B1))1,"DUPLICATES",IF(SUMPRODUCT(($A $1:$A1=$A1)*($B$1:$B1=$B1))=1,"NO DUPLICATES",""))) Copy the C1 cell formula and paste it for the remaining cells of C Column. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Rod" wrote: Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
Thank you for responce. You got my point
it is showing as duplicates even though i change as no duplicates( A4 changes as 4) Help me thanks in advance "מיכאל (מיקי) אבידן" wrote: I did n't check all the combinations but it seems as this one should work: {=IF(ISNA(INDEX(A1:A6&B1:B6,MODE(MATCH(A1:A6&B1:B6 ,A1:A6&B1:B6)))),"No Duplicates","Duplicates")} *** This is an array formula, and is to be entered with CTRL+SHIFT+ENTER rather than with simply ENTER. The curly brackets {} are not to be typed manually, those are entered by the Excel, when the formula is entered as an Array formula. *** Micky "Rod" wrote: Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
awesome....micky..... many thanks
it is working but i put 0 in match function =IF(ISNA(INDEX(A1:A6&B1:B6,MODE(MATCH(A1:A6&B1:B6, A1:A6&B1:B6,0)))),"No Duplicates","Duplicates") Actually I have large data of 60000 records Could you please explain the logic behind it Thanks in advance "מיכאל (מיקי) אבידן" wrote: I did n't check all the combinations but it seems as this one should work: {=IF(ISNA(INDEX(A1:A6&B1:B6,MODE(MATCH(A1:A6&B1:B6 ,A1:A6&B1:B6)))),"No Duplicates","Duplicates")} *** This is an array formula, and is to be entered with CTRL+SHIFT+ENTER rather than with simply ENTER. The curly brackets {} are not to be typed manually, those are entered by the Excel, when the formula is entered as an Array formula. *** Micky "Rod" wrote: Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance |
check for duplicates
Wonder.... many more thanks
please explain the logic behind it thanks in advance "T. Valko" wrote: Assuming no empty cells in either range. Try this array formula** : =IF(SUM(IF(FREQUENCY(MATCH(A2:A7&B2:B7,A2:A7&B2:B7 ,0),ROW(A2:A7)-ROW(A2)+1),1))=COUNTA(A2:A7),"No Dupes","Dupes") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Rod" wrote in message ... Hi all, I have the data as shown below col A col B 1 a 2 a 3 a 1 a 1 b 2 b Is there any formula to find duplicates( 1st and 4th rows are duplicates) are there or not? I am using the sumproduct formula in supporting column C But I need the formula in C1 which displays "Duplicates" if there are or else "No duplicates" Thanks in advance . |
| All times are GMT +1. The time now is 07:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com