finding "Like" names in rows

  • Thread starter Thread starter joel
  • Start date Start date
J

joel

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
 
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
 
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.
 
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
 
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
Click to expand...

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
 
Back
Top