Macro to compile data from multiple excel files (all with the same format) into one spreadsheet. Please help! Overwhelmed!

[ktbug1871]

Hi!

I've never posted anything like this before but I'm totally in over my head and would be eternally grateful for any help I can get!

They sent out an excel file for people all over the company to fill out (like a form) and return with information about events they attended and who attended with them. The excel file is all the same format (eg. "Event Title" is B10, "Group Leader" is B12, etc.), they were e-mailed to me as single attachments. I now have hundreds of e-mails all with this one attachment and need to open the attached spreadsheets and copy the e-mail address of the submitter, the group leader (B12) and listed participants (their names start on A20 and employee id start on B20, I need both, and have anywhere from 2 to 20 names) onto separate spreadsheets sorted by the title of the event (B10).

Is there an easier way to do this? I would LOVE to be able to extract the data straight from Outlook but don't know if that's possible. Right now I have saved the excel files as the e-mail address they came from and have sorted them into separate folders for each event.

Please tell me if that makes any sense or if you have any suggestions as to how to do this more quickly (I tried to write a macro and failed miserably). I've been up half the night, every night, for the past few weeks (months) doing these and they just. don't. stop. coming. I was informed today that we're about to START "busy season" and had a minor nervous breakdown.

 

[XLfriend777]

Hi ktbug1871 - Don't Panic!!! Welcome to the forum. You have posted your problem in the right place and hopefully somebody will be able to help soon! I think it would be helpful to know what your ultimate goal is. Do you want one spreadsheet with everyone's name and employee ID or do you want one spreadsheet for each event and then the names and employee IDs. I'm sure there are ways that macros could help process this data, but we need to know a little more to help. DON'T PANIC.... yet...

 

[ktbug1871]

Thank you, trying not to panic and that makes me feel a little better. I need separate sheets for each event and on then have the different groups listed with the Leader's name [B12] and the submitter's email address. Right now I have the files saved in separate folders for each event and have been using the submitter's email address as the file name - [name@company.com].xlsx.

{Sheet 1}

[Submitter email address/file name]
[Group Leader 1]
[Participant 1 Name {A20}][Participant 1 Employee ID {B20}]
[Participant 2 Name {A21}][Participant 2 Employee ID {B21}]
[Participant 3 Name {A22}][Participant 3 Employee ID {B22}]

[Submitter email address/file name]
[Group Leader 2]
[Participant 1 Name {A20}][Participant 1 Employee ID {B20}]
[Participant 2 Name {A21}][Participant 2 Employee ID {B21}]
[Participant 3 Name {A22}][Participant 3 Employee ID {B22}]
[Participant 4 Name {A23}][Participant 4 Employee ID {B23}]
[Participant 5 Name {A24}][Participant 5 Employee ID {B24}]
[Participant 6 Name {A25}][Participant 3 Employee ID {B25}]

[Submitter email address/file name]
[Group Leader 3]
[Participant 1 Name {A20}][Participant 1 Employee ID {B20}]
[Participant 2 Name {A21}][Participant 2 Employee ID {B21}]

The participants' names are in column A and their employee ID is the same row in column B. Often (but not always) they only list the last 7 digits of their ID, so in Column C I've been using the CONCATENATE function to add "US01" as a prefix to the 7-digit ID. I then clean it up manually (and this is a whole separate issue but I figure I'll put it out there) because most of the time they only have the 7-digit code in the B column but sometimes they write "US01-

" or "US01 - [code]" and I need it in the "US01[code]" format because in column D I will run a vlookup to get their email address from an list of all active employees. 

I then draw a border around each group (with the crossbars) and highlight the top row with the submitter's email address and skip 2 rows and add the next group in a box with the top row highlighted. 

At the end of the groups I select the whole sheet, copy all and paste as text to get rid of the formulas. 

Any suggestions on how to improve this process are welcome (encouraged)!  These are the "easy" ones because people also e-mail forms they filled out by hand or (my personal favorite) submit through an eFax so what they completed as an excel file comes through as a grainy .jpg of typed text that produces questionable results using text recognition so I end up manually retyping what was once a type file. And I have received 60 more since my first post so I should stop complaining and get back to work.  Thank you for reading and responding!