Producing a summary report

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Producing a summary report

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Producing a summary report

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


    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.

  2. #2
    Board Regular
    Join Date
    Feb 2011
    Posts
    547
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Producing a summary report

    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,

  3. #3
    New Member
    Join Date
    Jul 2013
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Producing a summary report

      
    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 Name Employee ID Account Status E-mail Department Manager Comment
    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

    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com