Jasper
|
| posted on 14/2/06 at 05:04 PM |
|
|
More Excel help needed
OK, so we're nearly there. I've got a 1200 customer address database, and I know I've got some duplications.
Best way is to see if there are any duplicate post codes, as with a database as small as mine it's likely to be unique.
So how do I sort it to easily remove the duplicate addresses?
|
|
|
|
|
esn163
|
| posted on 14/2/06 at 05:23 PM |
|
|
Hi, if the data is in a kind of table like structure, you should be able to sort the data by a certain column and then manually go through and look
for duplicates however, this could take some time. To do this simply select all the data go to data on the top toolbar and select sort. Then on this
menu select the column you want the data to be sorted by in your case postcode.
I think there is probably some better way to do this automatically but cant think of one at the moment.
HTH Ed
Indy Build Photos
** Build pages **
Photo Bucket Pics
|
|
|
britishtrident
|
| posted on 14/2/06 at 05:30 PM |
|
|
Start by Using the right tool for the job M$ Access or something better.
|
|
|
ecosse
|
| posted on 14/2/06 at 06:18 PM |
|
|
yep, access would be better than excel for this kind of work
Alex
PS
If you want find duplicates, just use "find" and delete 
|
|
|
Mansfield
|
| posted on 14/2/06 at 08:47 PM |
|
|
If I get a list like that I sort it alphbetically and set up cell that asks 'am I the same as the one above, if so then "flag word"
or otherwise no word at all'. Manually delete until "flag word" gone.
Not exactly what you want, but it does work, if a bit basic.
See XL help for IF commands.
|
|
|
GParkes
|
| posted on 14/2/06 at 09:35 PM |
|
|
Once you have sorted by the postcode as described above, under the data menu is also a subtotal option where you will be able to tell it to subtotal
at every change in postcode, if you select 'count' instead of 'sum', it will then count any postcode matches. Once you have
got the subtotals you should have a column with mostly 1's in it. If you then sort by this column you'll get all the duplicates at the
top or bottom of the list as the subtotal will show a count of 2 if the postcode is duplicated.
Its hard to explain by e-mail!
Goodluck
|
|
|
the_fbi
|
| posted on 14/2/06 at 10:02 PM |
|
|
http://office.microsoft.com/en-us/assistance/HA011366161033.aspx
|
|
|
dl_peabody
|
| posted on 15/2/06 at 07:00 AM |
|
|
FIRST RULE
Never work on the orginal
Save as....somewhere else and work on that.
So open "book1.xls" and select all the data....
Edit >>> select all select all your data
then
Data >>>> sort
If your colums are named you should be able to sort by address ( street address) city post code will have too many commons.
Sort by columns "A" then Colmun "D"...what ever your fancy..........names rather common...cities rather common...post codes
rather common
Street addresses the by names is my suggrestion.
If you screw up? That is why you made a copy....make another copy and try again.
Remember...make a COPY..select all Data...then sort by column.
Once you have done that sort again similar names looking for different addresses...some time I have thing sent t my WORK.
your data, your business, your choice...my advice is free but your decissions are yours.....
Good Luck!
[Edited on 15/2/06 by dl_peabody]
|
|
|
Jasper
|
| posted on 16/2/06 at 01:51 PM |
|
|
quote:
Originally posted by the_fbi
http://office.microsoft.com/en-us/assistance/HA011366161033.aspx
That got it, the COUNTIF formating thingy.
Access - yes I know, but this is the last time as I have to do as I'm having a proper bespoke customer database added to my web site which will
look after all of this.
Thanks again for all the help.
|
|
|
