Re: $$Excel-Macros$$ Flagging duplicate values with Countif and Search functions re partial matches
Check the file. On Sun, Nov 15, 2009 at 6:39 PM, Dilip Pandey wrote: > Dear Dan, > > I have tried to build up the data as mentioned by you and able to get the > desired result. > Same spreadsheet is attached herewith for your review. > > Best Regards, > -- > DILIP KUMAR PANDEY >MBA-HR,B COM(Hons.),BCA > Mobile: +91 9810929744 > dilipan...@gmail.com > dilipan...@yahoo.com > New Delhi - 110062 > > > On 11/15/09, dsulliva wrote: >> >> 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 > > > > -- > > -- > 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. 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 Flagging duplicate values.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Flagging duplicate values with Countif and Search functions re partial matches
Dear Dan, I have tried to build up the data as mentioned by you and able to get the desired result. Same spreadsheet is attached herewith for your review. Best Regards, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On 11/15/09, dsulliva wrote: > > 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 -- -- 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 Flagging duplicate values - by DILipandey.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Flagging duplicate values with Countif and Search functions re partial matches
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
