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 at http://www.excel-macros.blogspot.com > 3. Learn VBA Macros at http://www.vbamacros.blogspot.com > 4. Excel Tips and Tricks at http://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
