• 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.

eliminating Multiple duplicate entries with condition

S

shankar_iyer83

Member
DPIS LOCATION DESC

17-Jan-06 Ambala-Ambala 0

22-Dec-05 Ambala-Ambala 0

22-Dec-05 Ambala-Ambala 1

30-Nov-05 Ambala-Ambala 0

31-Jan-06 Ambala-Ambala 0

31-Jan-06 Ambala-Ambala 1


I need to filter out duplicate entries, like I have to eliminate , same LOcation Desc different dpis, like here Ambala-ambala , but different DPIS (17,22,30,31) , here the thing is I used the condition-=IF(AND(A4=A3,B4=B3),"1","0")


I should get zeros(0) everywhere but as 31,22 repeated twice , i get 1,


how to solve this? Its a huge file with lots of such data


Thanks

Shankar
 
You would be better to do something like

=COUNTIFS(A:A,A2,B:B,B2)

If a line is unique it will have a value of 1

If a line is not-unique it will have a value of > 1


You can use the same formula in a Conditional Format to highlight the duplicates


Chandoo has written lots of posts on Finding and dealing with Duplicates

Have a look through the Search Box
 
I have assumed your data is in 3 columns starting in A1

If that is the case put the formula in the first blank column in Row 2 and copy it down
 
Hi hui,


It seems my problem hasent been intreperted correctly..

see..


I have 2 columns one with DPIS , and other with Location Desc


I have dates in DPIS and location names in location desc


So, now I have to take out those values were location desc is same , but different dates..


Here, the before formula works if there are no identical dates...


But as u can see one location desc ambala-ambala has different dates(17,22,30, 31)


NOW, THING IS had there been no repition of dates like 17,22,30,31 occurs only once..then ur formula was fine...but as 22 and 31 repeated twice..so it gives 1 and 2 ..but ideally it shud be 1 in all columns...as same location and Dpis


So , in nutsheell I have to segregate such values.....there are many such values where DPIS are diff..but at the same time some of DPIS values are repeated..but overall.if i see..we have same location and dif DPIs


Hope i made myself clear!
 
Can you post the file somewhere?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
@Shankar_iyer83

I think you forgot to paste something?


In C2: =COUNTIFS(A:A,A2,B:B,B2)-1

If a line is unique it will have a value of 0

If a line is not-unique it will have a value of > 0
 
In C2: =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1

Copy down

This will put True in the first Unique entry

and False in all duplicates


or you can change it as required

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


ps: Also with 700,000+ records be careful with using formulas like

=sumproduct((a:a=x),(b:b=y),(c:c=z)) as that will result in Billions of calculations

and probably slow your pc to a crawl
 
Hi ninja,


this formula stil doesnt works.if u see the file..


1-Feb-10 Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba TRUE

1-Feb-10 Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba TRUE

1-Feb-10 Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba TRUE

3-Feb-10 Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba TRUE

3-Feb-10 Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba TRUE

3-Feb-10 Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba TRUE


this is not true...whereas formula puts it as true


thanks


shankar
 
I get

[pre]
Code: 
1-Feb-10	Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba	TRUE
1-Feb-10	Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba	FALSE
1-Feb-10	Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba	FALSE
3-Feb-10	Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba	TRUE
3-Feb-10	Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba	FALSE
3-Feb-10	Ambala-ASB-1113,Kori Mandi,Ambala Sadar,,,,Amba	FALSE[/pre]
So

1. Did you paste =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1
into C2 ?

2. What language version of Excel are you using ?

3. What version of Excel are you using ?
 
Hi ninja,


Even in that Case..as u pointed out..it doesnt gives what I want..see here...we have diff DPIS(1 feb, 3 feb) same loc(Ambala...) so.it should show as false everyehere.


This is the problem I face..some Dates are reapeated..like 1 feb and 3 feb are repeated again again..so I am not able to segregate data..and the formulas(as you did above) doesnt shows me the req result
 
I have no idea what you want?

Please post say the first 20 rows with manual answers next to the data

to show what you want
 
http://hotfile.com/dl/115372313/274a18f/extracts.xlsx.html


Hi ninja..i have shared the file with what i want...


I have taken first 10 rows of the sheet
 
hi


http://hotfile.com/dl/115372313/274a18f/extracts.xlsx.html


Hi ninja..i have shared the file with what i want...


I have taken first 10 rows of the sheet
 
So it should only be 1 when it has a unique Date and Location ?

There are no entries in the original Audit file which have a unique Date and Location
 
Hi hui,


you are right..but we have to scroll the whole sheet..to see this..I had 10 such files..its tedious..I did it this way only..


So i wanted some way to see this better...for future..


what i wanted, I explained in the attached file..zeors etc...


thanks
 
You must log in or register to reply here.
Back
Top