Producing a summary report

xlbeginnerxl

New Member
Joined
Jul 16, 2013
Messages
8
I have many excel workbooks, each with the same title. A1, A2...are blank
ABCDEFGHI
1Display Name
Employee IDAccount Expiry TimeAccount StatusEmailDepartmentManagerComment
2John SmithA1236429/03/2013 12:00:00 AMEnabledJohnsmith@abc.comHRAlex BrownAlex brown has requested extension till 15/04/2013
3Sam JonesA2567801/04/2013 2:30:00 PMEnabledsamjones@abc.comITPaul
Hardy
Extend till 15/06/2013

<tbody>
</tbody>


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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

shawnhet

Well-known Member
Joined
Feb 12, 2011
Messages
547
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

New Member
Joined
Jul 16, 2013
Messages
8
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.

Display NameEmployee IDAccount StatusE-mailDepartmentManagerComment
15/03/2013 to 28/03/2013
28/03/2013 to 03/04/2013
03/04/2013 to 15/05/2013
15/05/2013 to 28/05/2013

<tbody>
</tbody>

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,408
Messages
5,528,603
Members
409,827
Latest member
Tmcgrew05

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top