Producing a summary report

[xlbeginnerxl]

I have many excel workbooks, each with the same title. A1, A2...are blank
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Display Name
[/TD]
[TD]Employee ID[/TD]
[TD]Account Expiry Time[/TD]
[TD]Account Status[/TD]
[TD]Email[/TD]
[TD]Department[/TD]
[TD]Manager[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]John Smith[/TD]
[TD]A12364[/TD]
[TD]29/03/2013 12:00:00 AM[/TD]
[TD]Enabled[/TD]
[TD]Johnsmith@abc.com[/TD]
[TD]HR[/TD]
[TD]Alex Brown[/TD]
[TD]Alex brown has requested extension till 15/04/2013[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Sam Jones[/TD]
[TD]A25678[/TD]
[TD]01/04/2013 2:30:00 PM[/TD]
[TD]Enabled[/TD]
[TD]samjones@abc.com[/TD]
[TD]IT[/TD]
[TD]Paul
Hardy[/TD]
[TD]Extend till 15/06/2013[/TD]
[/TR]
</tbody>[/TABLE]


Every 15 days, a new workbook is generated with the above titles and it contains the list of temporary employees in the same format. Some of these temporary staff can have their contract extended and this would show up in the next workbook generated with new account expiry time. The ones that do not have extended account, get deleted. Employee ID is unique

What I would like to do is to compare the workbooks to the previous ones for consecutive months, and produce a report where staff, whose contract has been extended can appear under the same title and the ones that don't exist don't show up on the report.

Any help on this would be very much appreciated. I've tried Index and Match function but on the new report, I can only get it to return their ID not other fields.

As it's on different workbooks, what would be the best way to produce the report in excel? would I have to copy each sheet from the workbook to a new excel workbook and have a master worksheet for the report?

What I have done is copied the worksheets from different file and pasted them in one spreadsheet. in this new spreadsheet, i have created a new worksheet called report. Here what i'm trying to do is match column C to the column C from previous month and if the match is found, return the whole row for example A2, B2, C2,...and I2) could anyone please tell me how I can start and finish this.

Thank you very much.

 

[shawnhet]

It seems to me that you've got 2 issues here 1.merge all the appropriate files into a single workbook and 2. produce a report that summarises what you are looking for.

For 1. You can try this add-in (see link) or search the forum on merging sheets.

RDBMerge, Excel Merge Add-in for Excel for Windows

For 2. PErsonally, I would suggest trying a Pivot table and seeing what that gets you.

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one | Chandoo.org - Learn Microsoft Excel Online

You can summarize by Account Expiry time and filter for everyone with more than one position on the list or play with other formats to see if you can find one that suits your needs.

Cheers,

 

[xlbeginnerxl]

Thank you for the reply.

I have used the merge facility to incoporate all the worksheets into one excel files. As they all have same heading, I have sorted the data, first by their account expirty time (oldest to newest) and then by manager (A-Z)

Pivot table is causing some hassle. What I mean by report is basically another worksheet where the heading matches the previous worksheets, and the contents of this worksheet compares column C and returns the row value of that worksheet.

For example, system report generated on 15/03/2013, 28/03/2013, 03/04/2013, 15/05/2013, 28/05/2013 and so on and these are sheet names too. What I want is one worksheet called report with the same heading as mentioned in my first post and return values where Column C in 28/03/2013 is compared to 15/03/2013 and if the employee ID matches than return the whole row of data for the report.

the next query would then be for finding employee ID in 03/04/2013 and comparing it with 28/03/2013 worksheet and returning the matched ID in report worksheet.

This is the layout of the report worksheet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Display Name[/TD]
[TD]Employee ID[/TD]
[TD]Account Status[/TD]
[TD]E-mail[/TD]
[TD]Department[/TD]
[TD]Manager[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]15/03/2013 to 28/03/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28/03/2013 to 03/04/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/04/2013 to 15/05/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/05/2013 to 28/05/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so for the above report worksheet, if employee id matches the two compared worksheet (15/03/2013 to 28/03/2013) return all value (Display name, employee id, account status, email, department, manager, comment) from the 28/03/2013 and so on.

if this can be achieved through formula then please suggest how and if through vba than please explain the code if possible as I woudn't understand without any comments.

Thank you.