Excel Spreadsheet Manipulation

[Baffy]

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?

 

[Pete_UK]

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

 

[Pete_UK]

Hi Roger,

I don't understand. How come you posted before me, but your time says
10:44 when mine says 10:17 and it is only 10;20 now?

Pete

 

[Roger Govier]

Hi

One way (work on a copy of your data, just in case you get something
wrong!!)

Insert a new column at B, so you have a blank column to the right of the
Name.
Mark column A, Data>Text to Columns>Delimited>mark Comma as
Delimiter>Finish.
Delete column B

Mark your range of data, Data>Filter>Advanced Filter>Copy to new
location and choose a column beyond the end of your existing data>click
Unique values only>Finish.
Delete the original columns

 

[Roger Govier]

Hi Pete

I'm ahead of myself!!!<bg>
Twice recently, my time clock has got out of sync, and set itself ahead
of current time.
It was reading 11:52. I forced it to do a time sync with
time.windows.com and it has returned it to the correct time.

Apologies for the confusion.

 

[Pete_UK]

Ah, so the time of posting is picked up from each poster's system
time, not from the time maintained by the newsgroups? Learn something
new everyday ...

Thanks Roger.

Pete

 

[Baffy]

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]

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

 

[Baffy]

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?

 

[Baffy]

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]

Yes. Something is really screwed up. I keep filtering it, I notice names are
disappearing, but the file count remains the same on the before and after
filter
databases. Frustrating.

 

[Pete_UK]

When I do Data | Filter | Advanced Filter I usually select in the pull-
down to filter to another location (eg M1 in the same sheet). That way
the original data remains and if I'm happy with the reduced data set
then I can delete the original columns of data and use File | Save As
to save the new dataset with a new name.

As regards one of your earlier postings, I presume you discovered how
to fix the values with Edit | Paste Special before deleting the column
with the combined names in.

Hope this helps.

Pete

 

[Baffy]

Here's how it looks/what I do:

I click on Copy to Another Location
The List Range box is auto-filled
The Criteria Range box is blank
In the Copy To box I type in E
Note that I have reduced the spreadsheet to four columns A - B - C - D
I click on UNIQUE
I get this message: Not a valid record......

 

[Baffy]

Actually what the message says prciselyis "not a valid reference or defined
name"

 

[Pete_UK]

You need a cell reference in the Copy To box - leave yourself a blank
column and make it F1.

Hope this helps.

Pete

 

[Baffy]

I'm going to throw in the towel on this one Pete. It just is not
transferring to a new column no matter what I do.
But I do have one question which continues to baffle me. If I have 920
records before I execute the formula, how can I have 920 reocords AFTER I
execute the "date/Filter etc" formula. After I execute the formula that
duplicate names have disappeared. And so, how on earth
can the record number count be the same?

 

[Pete_UK]

I'm not sure where you are getting the record number count from. How
many rows did you have before, and how many rows do you have after
applying the advanced filter? If they are the same then either you
didn't apply the filter correctly or you had no duplicates.

Hope this helps.

Pete

 

[Baffy]

Upon closer look, there are few rows after the "advanced filter". I followed
the numbering down the left had side and noticed that some numbers were
missing, such as 1-2-4-7-8-11- and so forth. It was just that, once you get
to the bottom,
the total number was the same.

I'll let you know if I ever figure out how to successfully transport the
"advanced filter results" to a different column. Thanks for all your help
and your patience.

 

[Pete_UK]

One final attempt to describe it to you, then...

Suppose you have data in columns A to D extending to row 4000 with a
header row in row 1. You need to highlight all the data including the
header (i.e. A14000) then click on Data | Filter | Advanced Filter.

In the pop-up, the "List range" should already be filled in from your
highlighted range (NOTE: not always, if you have used advanced filter
on that sheet before). You need to click "Unique records only" and
"Copy to another location", and in this case you must tell Excel where
- just type F1 in the "Copy to" panel.

Then when you click OK you should see a reduced set of data from A1
onwards - you might only have 2000 records in this set.

You can then delete the original columns A to E to leave you with your
reduced set.

Hope this helps.

Pete

 

[Baffy]

Any chance of emailing it to you as an attachment?