Collate multiple excelsheet into one XL

**nkdv_04** · 05-10-2011, 06:45 AM

Hi ,
I am new XL world and i need help collating multipl XL into one XL sheet.
My team mates will send me one XL each detailing their project details.
e.g.
Employee1.xls
Name Date of joining current assignment
ABC 02-may-10 XYZ

Employee2.xls
Name Date of joining current assignment
OPQ 25-june-01 PQR

I need to combine these 50+ XL in to one XL like below.

Team info.xls
Name Date of joining current assignment
ABC 02-may-10 XYZ
OPQ 25-june-01 PQR

Can anyone please help?

Thanks
nkdv

**alansidman** · 05-10-2011, 08:08 AM

You have two options to do this from my perspective.

You could create a Union Query employing MS Query which is a part of the Excel package.

or you could import your spreadsheets into Access. Run a Union Query in Access on your two tables (imported spreadsheets) and then export the results back to Excel for further analysis.

I personally prefer the Access method. It is very quick and efficient.

Alan

**nkdv_04** · 05-10-2011, 09:26 AM

Thanks alansidman for instant reply. But i tried that but problem is that every XL has 12 workbooks in it.
e.g.
Employee1.xls has June-10, July-10, Aug-10....till May-11 sheet, so i am not able to import it into Access.

Is there any alternate way to do this?

Thanks
nkdv.

**alansidman** · 05-10-2011, 10:09 AM

Perhaps someone else will chime in with a VBA solution to cut and paste. I don't have one.

Alan

**alansidman** · 05-10-2011, 03:46 PM

As I was browsing another forum, this item just appeared which may be an answer you are seeking for importing multiple files into Access.

http://www.mrexcel.com/forum/showthread.php?t=227965

Alan