Is a Macro possible?

[absthechatter]

I have a little (big!) problem trying to get email addresses.

What I have is 100 spreadsheets with details of 80,000 people in my organisation. These spreadsheets contain lots of information including email addresses.

I have another spreadsheet with approximately 1000 people in (all of whom are included within the 80,000). I have all of their details apart from the email addresses. Is there a Macro I can use to search the 100 spreadsheets, find the email addresses and transfer them to my single spreadsheet with a 100 people on, or do I have to work through manually?

Many thanks, any help or suggestions of an easier method would be much appreciated.

Abs

[patrickp]

VLOOKUP is what you want if you're using Excel. A pretty complete explanation will be given in Help; if you can't get your head round it, come back and ask.

A useful thing to remember if you're going to write a formula to use a function like this: once you've written it for one row, you'll want to copy it to all the other rows. When you do this, the some of the numbers in the formula will increment, and some that do, you won't want to.

For instance, the cell reference to the person you want to copy details for, you'll want that to increment, so that's Ok. But the cell references that describe the range you want to search (unless you name it and use the name) will also increment, and you don't want them to. So put $ signs in front of each component of the cell references - eg $A$1:$G$100. The number that indicates how many columns the one you want data from is from the one with the person's reference doesn't increment: annoying when you're copying the formula across columns and you want it to reference a later column as well. You'll understand this gibberish once you've read the description of VLOOKUP.

The other point is that getting Excel to look for the nearest match (the TRUE switch) rather than an exact match (the FALSE switch) never works: use the FALSE switch and make sure that the matches _are_ exact. You'll find then that ones that don't match will return an error and you'll be able to check why, rather than allowing Excel to look for the nearest match (it always makes the wrong choice) and never knowing it's wrong until it's too late! ;-)

Edit: just noticed you're looking for the information from 100 different sheets. Hmm. You could use VLOOKUP for this, but it'll be a bit complex. Will have to think about this. Initial thought is that you could do this one sheet at a time, and copy the results to a different column each time, but this will still be tedious. Would work, though. Best way might be, as you suggested, using a macro that will use VLOOKUP on each sheet sequentially. Are the ranges in each sheet the same size? Probably not...

[absthechatter]

Thanks for that. I have not used VLOOKUP or Macros much before and am a real novice. I will read through and give it a try.

Do you think it would be easier if I transferred the 100 spreadsheets to something like Access?

Thanks again for your help.

Abs

[Graham M]

or an SQL database

[patrickp]

Originally Posted by absthechatter

Thanks for that. I have not used VLOOKUP or Macros much before and am a real novice. I will read through and give it a try.

Do you think it would be easier if I transferred the 100 spreadsheets to something like Access?

Thanks again for your help.

Abs

Very possibly; or,as Zeph mentions, an SQL database. My experience is mostly with Excel, though.

How about copying all the spreadsheets on to one - how big would that be? You could search it in one hit, then.

The other thing that occurs to me is that you're working for a very large organisation. Do they not have the resources to do this for you?

[absthechatter]

That's an idea, I will try putting them all into one first.


Thanks

[patrickp]

Ok, make sure all the columns from each correspond, and miss out the headings from all but the one you copy them to. That should work. You may find the resultant sheet useful for other things, too.

[TimmyB]

Originally Posted by absthechatter

That's an idea, I will try putting them all into one first.

You are going to hit a problem with this - Excel has a limit of about 65,000 rows so you'll 2 worksheets for 80,000.

This is a job more suited to a database than Excel. If you load your sheets with the email addresses all into a single table - you can copy and paste from Excel to Access if the columns match. Then load your list of names without the emails into another table. You then use a query to join the two tables together - there is a wizard to guide you through this.

You need a some piece of information in both tables which uniquely identifies each record - something like an emplyee number is ideal. If names is all you have you can do the join based on the combination of Forename + Surname.

http://www.w3schools.com/sql/default.asp has good tutorials on how databases work - the section about joins in particular would be useful reading if you go this way.

[Graham M]

Yeah a limit of 65,536 rows, could always split it into two sheets of 40k, as i stated before you could always import it to a database, such as SQL

[patrickp]

Excel has usually served my purposes better, and I've found that getting into using databases is by no means as easy as getting into using Excel. But a database such as TimmyB and Zeph suggest is certainly the better way to do this.

[absthechatter]

Thanks everyone


I now have one spreadsheet with a name in column A, staff number (unique identifyer) in column B and email address in column C. I have 65,000 people on this.

My second spreadsheet has lots of other information, so I need to keep it in order. But the important bits are, column G has name, column H has staff number and column i is blank and waiting for email address. There are about 1500 on this spreadsheet, all of whom are included on the first spreadsheet.

Before I start reading through tutorials, should I be looking to put this on a database, or would it be better for me to read a tutorial on how to write a formula to compare column H on spreadsheet 2 to column B in spreadsheet one. Where a match is found, transfer email from column c on spreadsheet 1 to column i on spreadsheet 2.

I do not have a clue whether this is possible or not, but would like to know whether it is before I start Googling for hours.

Many thanks

Abs

[patrickp]

Originally Posted by absthechatter

Thanks everyone


I now have one spreadsheet with a name in column A, staff number (unique identifyer) in column B and email address in column C. I have 65,000 people on this.

My second spreadsheet has lots of other information, so I need to keep it in order. But the important bits are, column G has name, column H has staff number and column i is blank and waiting for email address. There are about 1500 on this spreadsheet, all of whom are included on the first spreadsheet.

Before I start reading through tutorials, should I be looking to put this on a database, or would it be better for me to read a tutorial on how to write a formula to compare column H on spreadsheet 2 to column B in spreadsheet one. Where a match is found, transfer email from column c on spreadsheet 1 to column i on spreadsheet 2.

I do not have a clue whether this is possible or not, but would like to know whether it is before I start Googling for hours.

That's exactly what VLOOKUP will do, Abs. As others have said, generally for a list this large a database is the best way to handle it. However, that's quite a complex thing to get into, and for this particular task, if you've all the rows you want to search in one worksheet (what happened to the other 15000? ;-D) I'd say VLOOKUP will be the easiest way to do it. In fact, from the information you've given, I should be able to write formula for you - you may have to tinker with it a bit, but give me a few minutes. Excellent that you have a staff number as identifier.

Many thanks

Abs

[SMHarman]

Does your employers data controler know about this information? Are you storing it securely, lah de dah, or else your organisation will be in breach of the Data Protection Act.

[patrickp]

Right. The syntax for VLOOKUP is VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup).

This is what you do (where I've indicated actual characters to type, they're in red):

First, open both spreadsheets. Check what the actual number of the last row in the spreadsheet you're going to search is. Then go to the first row in column I in the sheet you're copying to - if you have headers, this will be row 2. Type in this cell (I'm assuming this is row 2; if not substitute the actual row number for 2) =VLOOKUP(H2, and then click on B2 (again, if you have a header and 2 is the first row) in the searchable sheet. This will put the name of this workbook and sheet into the formula (in inverted commas and with an exclamation mark) along with B2 as the cell. Then insert $ signs immediately before the B and the 2. Now type :$C$ and the number of the last row in the searchable sheet. Then type ,2,FALSE) and press Return. Don't miss the punctuation marks I've included.

That should enter the formula in this cell: see if it returns the correct email address for you. If it doesn't, post back here with the exact formula you entered; it's all too easy to enter something incorrectly. There should be no spaces, of course.

If it works, then copy it to the rest of the column and one of your parents has a brother called Robert. Just in case you're not sure how to do this, select the cell where you've entered the formula. If you hold the cursor over the bottom right corner of the cell it should change to a +. When it does, click and hold your left mouse button and drag down the column: the sheet should scroll as you reach the bottom. Be careful of scrolling speed: it's easy to zoom past the end of the column. If you do, keep the button pressed and move the mouse up to the top: the sheet will scroll back. Release it when it's over the last cell in the column; that'll enter the formula, correctly incremented if you've entered it as instructed, into each cell of the column.

Remember, if you mess up, use the back button. You'll have to repeat the operation, but you won't lose data.

Check your results now; if every cell in the column is showing an email address, great! If you have errors, you'll need to chase them down: if the formula has worked for most cells, it won't be that that's incorrect. The most likely reason is discrepancies in the way the numbers were originally entered: unless they were automatically entered, someone may have included something like a space, that wouldn't show until you looked for it.

Lastly, close the worksheet you're searching. The formulae in column I should now change to include the path to that worksheet. Now you can save and close: each time you open the sheet, it will open the searchable one and query it. If you're happy with the result and don't want it to search afresh each time, highlight the column and click Copy. Then click Edit, Paste Special, select Values and click OK. You'll find the resultant values (i.e. the addresses) have now replaced the formulae in that column.

Edit: By the by, don't ignore SHarman's comment. Data Protection is being taken much more seriously now.

2nd Edit: LOL!

[absthechatter]

Originally Posted by SMHarman

Does your employers data controler know about this information? Are you storing it securely, lah de dah, or else your organisation will be in breach of the Data Protection Act.

You are completely correct of course. They are fully aware I am playing with it. I am accessing it through my employers server, linked in using GemSafe smart card. The computer I am using here for the internet has none of this information stored on it. The secure computer has no links to the internet at all.

Many thanks anyway

Abs