another excel question...

[bapprill]

i have two databases of basically the same info. one is a database of members, one is a list of e-mails used to send a mailing. i need to update the list to the mailing list. there are approx. 1500 items in each list. i would like the fastest, most automated way to do this. i was thinking enter a formula that would test the contents of cell one in list one against all the entries in list two. it could write a true, false, or 1,0 to a third field. then i could sort by the third field to determine which fields need to be added/removed. any ideas, suggestions, or comments would be greatly appreciated. thank you.

~~ Laziness is the mother of invention ~~

 

[Dreamboat]

Do they have one column that's exactly the same?

If so, you could bring an email address over from one sheet to the other using a vlookup.

See the free MyVlookup download at

http://www.theofficeexperts.com/downloads.htm

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[bapprill]

i dont know if this would work. basically, i have two huge columns. i need to update one to the other. ie, add missing e-mails, and remove unnecessary e-mails. the lists are too long to do this by hand, so i was looking to automate the process. i also need to be able to track the changes made. im not sure exactly how vlookup works, so i dont know if it would work or not, but from what i've read i dont think that it will.

~~ Laziness is the mother of invention ~~

 

[KenWright]

Is the data in the same format in each list, ie email address in one list if found in the other will be identical?

If so then copy one list straight under the other, then use Data / Filter / Advanced Filter / select 'Copy to another range' choose another range (next column will do), check 'unique values only' and hit enter.

Piccies if you want them to be found at Debra Dalgleish's site:-

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

Regards
Ken...............

 

[bapprill]

will i be able to track the changes made?

~~ Laziness is the mother of invention ~~

 

[KenWright]

Not really - The function wasn't dseigned for that. You could always put a helper column in alongside each list. Assume you have named your list ranges List1 and List2, and then assuming List1 is in A1:A1000 and List2 is in D1800, to the right of the first entry in List1 (ie cell B1) put =COUNTIF(List2,A1) and copy down. Then against the right of the first entry in List2 (ie cell E1) put the formula =COUNTIF(List1,D1) and copy down.

A 0 means the record does not appear in the other list, a 1 means it does, and a 2 or more means it is duplicated.

Regards
Ken.................

 

[bapprill]

what about making one set of data a different color? also, how exactly does the filter work? if data is in set a and not set b, it needs to be added to set b. but if data is in set b and not set a, it needs to be removed from set b.

~~ Laziness is the mother of invention ~~

 

[Greatgazzoo]

Is this on one Excel spreadsheet? or more than one?

It sounds like the management of the data is the real problem.

 

[bapprill]

it is a a data management problem. i started a new job, and i got thrown into this project. heres some background info... i have a mailing list derived from our member database (not up to date), and a list of the updated mailing list. I need to determine what data needs to be added/removed from the old list based on the new list. I think i have it figured out, but any suggestions are still more than welcome.

~~ Laziness is the mother of invention ~~

 

[SkipVought]

Hi,
If you have a database with related tables and one of them is "up to date" meaning that there is at least one row for each major entity, then you could perform an outer join of the "complete" table with the "incomplete" table which would include ALL the rows from the complete table and any associated row from the incomplete table. The SQL would look something like this...

Select Table1.Field1, Table1.Field2, Table2.Field3
From Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1;

Then everywhere that Field3 is empty is a nonexistent row in Table2.

This can be done with MS Query -- menu item Data/Import external Data/New Database Query

The next specifics will depend on how your data is arranged. Just "call back" if you decide to go this route.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Access, Access, Access
Has standard Find Dupes and Find Unmatched queries
It's gonna be the easiest and fastest way in th long run

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bapprill]

well, if i had my choice, it would be in access and it would be done. but, i just started this job and they threw me in this project about halfway through. so now, i have to finish the way it was already started....in excel. :-(

~~ Laziness is the mother of invention ~~

 

[SkipVought]

Can be done in Excel using MA Query

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bapprill]

i ended up using the sort method. i used 2 separate sorts. one to sort what was in set a and not in set b, and one to determine what was in set b and not set a. this worked very well because i needed to be able to keep track of the changes that had to be made.

~~ Laziness is the mother of invention ~~