• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

find out duplicates and put 0 or 1

S

sguna1982

Member
Dear team,
I am looking for formula to find out the duplicate data and return the result should be first duplicate is 1 and remaining duplicates will be zero

Acct no Month Result
123 jul-14 1
123 jul-14 0
456 aug-14 1
123 jul-14 0
456 aug-14 0

Thanks
guna
 
Hi ,

Your data does not make it clear as to what defines a duplicate ; is it only the account number , or is it a combination of the account number and the month ?

Narayan
 
Hi Guna ,

Try this :

=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)=1,1,0)

Enter this in the first row of data , and copy down.

I have assumed your data is in columns A and B , starting from row 2.

Narayan
 
Dear sir,
I want to include one more column additionaly and countif should run assending order.

Acct no, Month, Result, tags
123 jul-14 1 full
123 jul-14 0 part
456 aug-14 1part
123 jul-14 0 part
456 aug-14 0 part

Here the scnario is if within this duplicates any "full" tag is there then it should be "1" as assending order, if not there the old formula has to execute.
 
Hi Guna ,

Again I have not understood your explanation , nor is the data helping much ; can you explain what you mean by ascending order ? Show with data which has duplicates in plenty.

Narayan
 
The data should be countif based on account no, month and tag. If the account no and month is same then it should be check weather part or full, if the tag is part then the result should be 0(zero) otherwise 1.
 
Hi Guna ,

Since you are unable to upload a file with enough data in it to give a clear understanding of the problem , all I can say is extend the earlier formula , as in :

=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$D$2:D2,"full")=1,1,0)

I am assuming the tags are in column D.

Narayan
 
Sorry for the inconvenience. Pls find the file attached. I tried but its working for tag full only, its not working for part tag.
 
Hi Guna ,

If , as you say in an earlier post , the tag equals "part" , the result should be 0 , then how is the result in row #4 equal to 1 ?

Why can you not upload a file which has lots of data , and real-life working data ?

Narayan
 
The data should check within the duplicates, if there is any full tag is there, it should be 1, or else it should countif based on acct no and month.
 
You must log in or register to reply here.
Back
Top