Good solution Dililp, The data scenario sure presents the conflict you have mentioned. What can be done to work around it?
Warm regards, Anand Kumar anand...@gmail.com On Oct 27, 11:26 am, SAJID MANSOOR <sajidmansooral...@gmail.com> wrote: > Good! > > On Sun, Dec 13, 2009 at 6:01 PM, Dilip Pandey <dilipan...@gmail.com> wrote: > > Ok, The solution is revised as per your needs. One thing the data scenario > > given by you has some conflict. Explained below:- > > > 11/1/09 123456 -> duplicate > > 10/19/09 123456 -> original > > 12/1/09 123456 -> duplicate > > Let me know if I am correct in understanding. Thanks. > > > -- > > DILIP KUMAR PANDEY > > MBA-HR,B COM(Hons.),BCA > > Mobile: +91 9810929744 > > dilipan...@gmail.com > > dilipan...@yahoo.com > > New Delhi - 110062 > > > On 12/11/09, azim...@gmail.com <azim...@gmail.com> wrote: > > >> Thanks Dilip sir for your help. however what i am looking for is a > >> logic which will get duplicates. there are two condition for this. > >> first telephone numbers should be repeated. secondly, only those > >> entries will be duplicate where date is later one. say for example 123 > >> is a telephone number which is repeated more than once, then we have > >> to mark the most earlier earlier date as original and all later dates > >> as duplicate. data here is unorganized for which earlier date may have > >> been recorded after the later date. continuing with the above example. > > >> 11/1/09 123456 > >> 10/19/09 123456 > > >> 12/1/09 123456 > >> 11/1/09 456789 > >> 12/1/09 456789 > >> 10/1/09 456789 > > >> additional information: i may not be able to play with source data > >> which means and i can't sort it. we use excel 2003 version and i am > >> looking for solution in function. as i am not well versed with VBA, i > >> may not be able to reproduce it. so solution in function will help me > >> in future also. > > >> Regards, > > >> Azim > > >> On Dec 11, 12:41 pm, Dilip Pandey <dilipan...@gmail.com> wrote: > >> > Dear Azimullah, > > >> > Choose any of the below formual and paste it in third column i.e, column > >> C > >> > Row 2 onwards and you are done. > > >> > =IF(SUMPRODUCT(($B$1:B2=B3)*($A$1:A2<A3))>0,"Duplicate","Unique") > >> > OR, > >> > =IF(COUNTIF($B$1:B1,B2)=0,"Unique","Duplicate") > > >> > solved workbook is also attached. > > >> > Best Regards, > >> > -- > >> > DILIP KUMAR PANDEY > >> > MBA-HR,B COM(Hons.),BCA > >> > Mobile: +91 9810929744 > >> > dilipan...@gmail.com > >> > dilipan...@yahoo.com > >> > New Delhi - 110062 > > >> > On 12/10/09, azim...@gmail.com <azim...@gmail.com> wrote: > > >> > > Hi i have market research data that i need to clean in terms of > >> > > finding out duplicate surveys. there are two criteria for this. > >> > > firstly telephone number from which it was done. if it is repeated > >> > > more han once then it means its duplicate, its the first criteria. > >> > > second criteria is date. so if there is a single phone number repeated > >> > > twice, then the earlier date/time should be original survey and later > >> > > date/time to be counted as duplicate. > > >> > > example > > >> > > Date Phone number > >> > > 11/1/09 123456 > >> > > 12/1/09 123456 > >> > > 11/1/09 456789 > >> > > 12/1/09 456789 > > >> > > Thanks in advance! > > >> > > -- > > >> --------------------------------------------------------------------------- > >> ------- > >> > > Some important links for excel users: > >> > > 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > >> > >http://www.excelitems.com > >> > > 2. Excel tutorials athttp://www.excel-macros.blogspot.com > >> > > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com > >> > > 4. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > >> > > To post to this group, send email to excel-macros@googlegroups.com > >> > > If you find any spam message in the group, please send an email to: > >> > > Ayush Jain @ jainayus...@gmail.com or > >> > > Ashish Jain @ 26may.1...@gmail.com > > >> > > <><><><><><><><><><><><><><><><><><><><><><> > >> > > HELP US GROW !! > > >> > > We reach over 6,500 subscribers worldwide and receive many nice notes > >> about > >> > > the learning and support from the group. Our goal is to have 10,000 > >> > > subscribers by the end of 2009. Let friends and co-workers know they > >> can > >> > > subscribe to group at > >> > >http://groups.google.com/group/excel-macros/subscribe > > >> > Identifying repeated survey - By DILipandey.xls > >> > 18KViewDownload > > > -- > > > ---------------------------------------------------------------------------------- > > Some important links for excel users: > > 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > >http://www.excelitems.com > > 2. Excel tutorials athttp://www.excel-macros.blogspot.com > > 3. Learn VBA Macros athttp://www.vbamacros.blogspot.com > > 4. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > To post to this group, send email to excel-macros@googlegroups.com > > If you find any spam message in the group, please send an email to: > > Ayush Jain @ jainayus...@gmail.com or > > Ashish Jain @ 26may.1...@gmail.com > > <><><><><><><><><><><><><><><><><><><><><><> > > HELP US GROW !! > > > We reach over 6,500 subscribers worldwide and receive many nice notes about > > the learning and support from the group. Our goal is to have 10,000 > > subscribers by the end of 2009. Let friends and co-workers know they can > > subscribe to group at > >http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
