Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding "Like" names in rows
I am trying to filter a list of 5000 names of different items, where there
are "like" names in different rows. I need to be able to show/delete the like duplicate ones. So in the names below I would like to search on first name and if same, then delete the dups. i.e. INGRAM BARGE CO INC INGRAM BOOK CO INGRAM BOOK GROUP INC Ingram Industries INGRAM INDUSTRIES INC INGRAM MICRO INC International Paper INTERNATIONAL PAPER CO After this search, I would like to see only 2 entries. I |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding "Like" names in rows
Joel,
AutoFilter using the custom option, and the "Contains" and then make a decision based on what you get for each filtering - select the visible cells and enter just your preferred version. HTH, Bernie MS Excel MVP "joel" wrote in message ... I am trying to filter a list of 5000 names of different items, where there are "like" names in different rows. I need to be able to show/delete the like duplicate ones. So in the names below I would like to search on first name and if same, then delete the dups. i.e. INGRAM BARGE CO INC INGRAM BOOK CO INGRAM BOOK GROUP INC Ingram Industries INGRAM INDUSTRIES INC INGRAM MICRO INC International Paper INTERNATIONAL PAPER CO After this search, I would like to see only 2 entries. I |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding "Like" names in rows
Thanks for the post. In this xls, there are 5000 entries, where as there
might be 3000 unique entries, and 2000 duplicates. I need to be able to sort and delete based on first name. "Bernie Deitrick" wrote: Joel, AutoFilter using the custom option, and the "Contains" and then make a decision based on what you get for each filtering - select the visible cells and enter just your preferred version. HTH, Bernie MS Excel MVP "joel" wrote in message ... I am trying to filter a list of 5000 names of different items, where there are "like" names in different rows. I need to be able to show/delete the like duplicate ones. So in the names below I would like to search on first name and if same, then delete the dups. i.e. INGRAM BARGE CO INC INGRAM BOOK CO INGRAM BOOK GROUP INC Ingram Industries INGRAM INDUSTRIES INC INGRAM MICRO INC International Paper INTERNATIONAL PAPER CO After this search, I would like to see only 2 entries. I |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding "Like" names in rows
Joel,
Ahhh. Use another column, with a formula like =LEFT(A2,FIND(" ",A2)-1) filled down, to find the "first" name of each entry in your column. HTH, Bernie MS Excel MVP "joel" wrote in message ... Thanks for the post. In this xls, there are 5000 entries, where as there might be 3000 unique entries, and 2000 duplicates. I need to be able to sort and delete based on first name. "Bernie Deitrick" wrote: Joel, AutoFilter using the custom option, and the "Contains" and then make a decision based on what you get for each filtering - select the visible cells and enter just your preferred version. HTH, Bernie MS Excel MVP "joel" wrote in message ... I am trying to filter a list of 5000 names of different items, where there are "like" names in different rows. I need to be able to show/delete the like duplicate ones. So in the names below I would like to search on first name and if same, then delete the dups. i.e. INGRAM BARGE CO INC INGRAM BOOK CO INGRAM BOOK GROUP INC Ingram Industries INGRAM INDUSTRIES INC INGRAM MICRO INC International Paper INTERNATIONAL PAPER CO After this search, I would like to see only 2 entries. I |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding "Like" names in rows
On Thu, 9 Apr 2009 09:05:01 -0700, joel wrote:
I am trying to filter a list of 5000 names of different items, where there are "like" names in different rows. I need to be able to show/delete the like duplicate ones. So in the names below I would like to search on first name and if same, then delete the dups. i.e. INGRAM BARGE CO INC INGRAM BOOK CO INGRAM BOOK GROUP INC Ingram Industries INGRAM INDUSTRIES INC INGRAM MICRO INC International Paper INTERNATIONAL PAPER CO After this search, I would like to see only 2 entries. I Here is one method. (Takes much longer to type than to do): Assume your list is in column A. Move your list to start in A11 Enter the following labels: A1: TF A10: Company B10: FN C10: TF Enter the following formulas: A2: TRUE B11: =LEFT(A11,FIND(" ",A11)-1) C11: =COUNTIF(B11:$B$18,B11)=1 Go to bottom of list in column A by selecting some cell in Column A then typing <ctrl+down_arrow (hold down ctrl while typing down arrow). Select bottom cells in Cols B&C with <right_arrow <shift+right_arrow Select all the cells in Cols B&C up to and including the above formulas by typing: <ctrl+shift+up_arrow Copy all the formulas down by typing <ctrl+D Then Data/Advanced Filter List Range: A10:C5000 (or whatever) Criteria Range: A1:A2 Copy to: Your choice --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |