zip codes and sort.... grrrrr

  • Thread starter Thread starter billrebels4
  • Start date Start date
B

billrebels4

Ken- Ok, it seems i am back to square one. I have taken all of my zip
codes and turned them into 5 digit (22202).. I used a formula to take
the last four digits off of all the zips that had it. I then copy and
pasted special/values into a new column... now some of the zips line up
on the left(the ones i changed with the formula and the ones that were
already 5 digit line up on the right.)

if i click on the value bar at the top of my screen and then press
enter the cell contents will shift to the right.. do i need to do this
for all of them or can i tell excel to do it automatically?????
 
hehe - The ones you have changed with a formula are now text, and the others are numeric.
To do what you want though, simply copy an empty cell, select your entire range of data
and do Edit / Paste Special / Add.

What did you do to the 4 digit ones to make them 5 digit, just out of interest. Just in
terms of was it adding a 0 at the end, or what?
 
KEn- I used a formula someone posted earlier... i guess it looked at the
cells and deleted the last four digits and the hyphen.... i have found
lots of repeated people in my sheet.. is there anyway to automatically
delete the repeated people?
 
Sure, but you need to be real careful about what constitutes repeated. FIRST AND
FOREMOST - make a backup of your data - NOW!!!

Is it possible that the names are the same, but other data is different?? How many
columns of data need to match before you can be sure it is a duplicate?

The easiest way to get rid of those kind of duplicates is to use Data / Filter / Advanced
Filter / 'Unique records only' and 'copy to another location' Debra Dalgleish has
pictures and instructions here:-

http://www.contextures.com/xladvfilter01.html#FilterUR

By the way, given the pain you have already been through over the last couple of days, you
are more than welcome to send me some data if you would like it fixed. You would need to
take the NOSPAM out of my email address though, and any large files should be zipped.
 
Back
Top