Hi Everyone...I have a list of names (column A) and in column B I want a formula to flag all duplicate items in column A. I was using the formula =IF(COUNTIF($A$2:$A$10,A2)>1,1,0) where "1" would flag me to a possible duplicte (i.e. I could filter on it).
The data in column A is causing problems. Column A contains names. It could be in the format first name / last name or last name / first name. There also could be a comma or a semi-colon separating the 2. There almost always will be a space between the two. My solution was to use the last 4 or 5 charactes of the column A cell and use that as a condition in the count if. It looks something like this, but I can't get it to work. =IF(COUNTIF($A$2:$A$13,IF(ISERROR(SEARCH(RIGHT($A2,2),$A2,1))=TRUE, 0,1)), 1,0) Can someone give me some advice? I'm also open to other formulas / functions that would allow me to flag possible duplicate names based upon a partial match of the cell (e.g. a cell in column A containing "Ron Smith" would have to identify "Smith, Ron"; "Smith Ron"; "Smith; Ron"; "Ron, Smith" as a potential duplicate values). Then there's possible duplicate data that contains spelling errors (e.g. Smith vs. Smithh vs. Smyth). I don't think there's any help for that?? I'd prefer not to sort, subtotal, etc. the data and I'm using Excel 2003. I also don't want VBA solutions. Thanks Dan -- ---------------------------------------------------------------------------------- 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
