Hi again:

I figured the last one out. I was entering the equation on the wrong line.
However, I have yet another strange outcome. I can plainly see that many
of the entries have been deleted once I do the Data - Filter - Advanced
Filter etc.

However, when I compare the number of entries (files?) on the pre-filtered
database and the post filtered database, they equal one another.....23,760
to be exact. How can this possibly be?

"Baffy" wrote:

Hi Pete:

Believe it or not, I actually figured it out before you got back to me. I'm
a little
slow on the switch, but the switch does eventually get pulled. This time I
have
a rather unusual an unexpected problem. When I double click on that little
black box to which you referred, all the last names immediately appear in the
column.
I don't have to go to EDIT etc. It all happens immediately. The problem
is, however, I can't get rid of the column with both the first and last names
in it.
If I try to delete it, it also deletes the new column with all the last
names in it
AND replaces all the names with #REF. What do you think that is all about?

"Pete_UK" wrote:

I assumed from your example that the names were in column A, but if
they are actually in column B then you will need to change the formula
to:

=LEFT(B2,FIND(",",B2)-1)

and put it in C2. With the cursor in C2 once you have entered the
formula, then you can double-click the fill-handle and the formula
will be copied down column C for as many entries as you have in column
B (assuming contiguous data).

I'm not sure what you have in column A, but the rest of it should work
if you remember to transpose the columns in my description to your
reality.

Hope this helps.

Pete


On Jul 7, 12:06 am, Baffy wrote:
Thanks Pete. But nothing is moving I get the column copied all the way down
and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and
a small
outline of a cross appears and nothing happens.

Possibly it has something to do with the fact that when I insert a column
between the names and the address which creates a new column C. The names
continue
to appear under column B and the addresses shift to column D when I create
the new column C. Do you suppose the equation needs to be changed based on
this fact?



"Pete_UK" wrote:
Insert a new column B between the name and address columns and put
this formula in B2:

=LEFT(A2,FIND(",",A2)-1)

This will give you the name to the left of the first comma. Copy this
formula down column B by double-clicking the fill handle (the small
black square at the bottom right of the cursor), then fix the values
by <copy then Edit | Paste Special | Values (check) | OK and <Enter.
You can then delete column A and put "Name" as a header in A1.

Now highlight all the data and headings from A1 to D-whatever and
click on Data | Filter | Advanced filter and in the pop-up you can
check Unique Records only (and I prefer to select a different location
- eg F1). Click OK and you will have your reduced list (no duplicates)
in columns F onwards. If you are happy with this then you can delete
columns A to E and then do File | Save As to save the file with a
different name (so you still have the original if you want to get back
to it).

Hope this helps.

Pete

On Jul 6, 9:52 am, Baffy wrote:
An Excel Spreadsheet is set up like this with hundreds of duplicate names and
addresses:

Name Address City Zip
Etc.
Smith, Joe 10 S. Oak
Smith, Mary 10 S. Oak

QUESTIONS:
1: How do I eliminate the comma and everything to the right of the comma for
all records, i.e. ,Joe and , Mary.
2: How do I eliminate duplicate addresses.
My goal is to have one entry as follows:
Name Address
Smith 10 S. Oak
And eliminate duplicate addresses and first name leaving only one last name
to each address. Can it be done?- Hide quoted text -

- Show quoted text -