Excel - Please read/help!

[mviltan]

I've got 2 excel files one conataing peoples name and address's one containing some of the same names but with telephone numbers eg:

File 1 File 2

Paul 20 High Street Paul 555 9999

Dave 14 Road Frank 654 5869

Stacey 13 Street Stacey 654 6665


etc

there's also multiple sheets in each file i want to copy the phone numebers from file 2 to file 1 but i need them to end up at the right name! Any ideas?

 

[SimonDavis]

The data would have to be very clean on both sheets to get it 100% right. I would think about importing them into a database first - you can clean up and export to a new spreadsheet.

Does this sound feasible? Do you need tips for that?

 

[snyderj]

You could paste the data to new tables in Access, then do your comparisons there.

 

[Dreamboat]

I don't like the "user another application" answer myself. No offense to those who do. I know Access enough now to use it for a purpose such as this, but there was a time I didn't, and it certainly wasn't the time I wanted to start learning even a LITTLE bit about it.

First of all mviltan, I'm willing to do this for you because it's something I've done a gazillion times and could probably do it for you lots faster.

However, here's how I'd do it:

Start by saving your file to a new name so your original stays intact in case you screw up.

Take the smallest spreadsheet and make it conform the the largest one. In other words, insert columns, move them around, etc., to look just like the bigger spreadsheet. If names are in two columns in one sheet and in one column on others, you'll have to deal with this--let me know if you need help with that.

When you've got all the smallest sheets conforming with the columns in the biggest sheet, select all of the data and copy it onto the big sheet. Do this until all data is copied onto the big sheet.

Start sorting by such things as name, city, etc. You may have Philadelphia, and Phila., etc. If you're lucky, your data is similar and you only have CA and Cal. Then, choose which one you want, do an Edit-Replace and replace Cal with CA. The name column will be the one you want to concentrate on however.

Remember, that you won't only be deleting duplicates here but you'll be able to start with a "clean" file in which you can maintain standard entries, i.e., all have two-letter states, nobody gets a middle initial, whatever. Consider this work to be towards completing both goals.

Once you've cleaned up the names pretty good, sort by the name column. The following assumes Row 1 contains column headings and Row 2 contains your first person. Go to the first blank column (we'll pretend it's column H and we'll assume your names are in column A for the purpose of the formula). In H2, enter the following formula:

=A2

In H3, enter the following formula:

=if(A3=A2,"","dupe&quot

Copy the formula down all rows.

Copy H1 to H(whatever the last row is). Hit Edit-Paste special-Values-OK.
Now, sort by column H (descending I think) to bring the dupes to the top. Now, select all the dupe rows and make them colored text. Sort by name again and you'll be able to double-check all of your dupes or don't sort, just delete all the rows that say "dupe".
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[Dreamboat]

Of course, that'll work for a few hundred records or so... if you've got thousands, man. I dunno!
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[JVFriederick]

I agree with your advice Dreamboat. Not much one can do with data that is so "casual", except get out the elbow grease.

If mviltan has thousands of records, it's just gonna take longer. But if the data shown in the example is representative, there may not be more than a few hundred records.