• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Productivity Report

K

kmakifl

Member
I have a new report that is imported into Excel. It provides the week's productivity with a drill down to each user's daily activity. I hope to achieve the following, but I am unsure of approach:


Create a monthly master > import weekly updates that will automatically update each user's activity and flow thru to the summary numbers. If user ba123 is in file, add to respective cell.


I have shared a sample of the worksheet and summary of what I hope to see.

http://www.2shared.com/document/WZBc5mko/ABC_Svc.html


Hope explanation is clear and thanks for your help.
 
Hi ,


Can you clarify a few more details as follows :


1. Your sample worksheet has users named BA001 through BA171 , and probably more. Does this mean you will have only one entry per user , and that a user will never repeat ?


2. Your worksheet is labelled from 1 through 15 , which is two weeks ; how do you segregate the entries week-wise ?


3. The worksheet you have shared gives the Summary that you are looking for ; how will the raw data be available , in the same worksheet or another worksheet or by importing from a text file ?


Narayan
 
Thank you for responding


1.There are actually 30 "live" users, the user names beyond that are I believe due to the drill down to the each day's productivity. So the number that you are looking at is the week's total and if you click on the +, you will find the numbers making up those numbers. I will need to remove at least 3 or 4 users as needed.


2. I am not sure if I should update weekly or bi-weekly and thought to create pay period tab.


3. Data is exported as an excel file with the data as displayed in columns A - H, thru rows 177 (the figures displayed are the totals for the period with the supporting info behind it - see + sign).


4. I think I will need to format the summary in the event I need to add a graphic format, correct?
 
Hi ,


Thanks for the clarifications. Let me check out my understanding :


1. If you say the number of users is only 30 , then , to identify a user , we need to maintain the user name , which means that when there are multiple entries for one user , the user name will repeat. At present , when you drill down under BA001 , you see the user names BA002 , BA003 , BA004 ,.... This cannot happen. The multiple entries under BA001 should all be BA001.


2. The point is that if one tab contains entries for a week , then a week wise summary is possible ; if each tab contains entries for a fortnight , then only a fortnightly summary is possible. If you have a column for the dates , then based on the dates , either a week wise summary or a fortnightly summary is possible.


3. OK. So data will be available as an Excel worksheet.


4. Depending on what the final summary , which will be presented to the end user , is likely to be like , that summary will need to be formatted attractively. This summary can use the consolidated figures from your present worksheet.


Narayan
 
1. I renamed the users to remove identifying info, so I did a copy/drag. I think because of the drill down it renamed all 177 rows in lieu of the top layer. I uploaded new file


ABC_Svc.xls

http://www.2shared.com/document/Rp1BIMqO/ABC_Svc.html
 
Hi ,


Thanks.


There's one new doubt ; the column labelled Hrs. has a number in the first row of each entry , but thereafter , there are dates. How do we treat this kind of input data ? It would be better if one column is used for dates , and another column for the hours worked on each day.


Narayan
 
Hi ,


Most of it is clear now. Just one more point :


Your first post , mentions :


Create a monthly master > import weekly updates that will automatically update each user's activity and flow thru to the summary numbers. If user ba123 is in file, add to respective cell.


Can you clarify the second sentence ? "If user BA123 is in the file , add to the respective cell". This implies that the data import happens all the time in small sets of data. If the data import is only once a week , then there will not be any entries against user BA123 for that week , and the imported data just has to be added to the monthly master as fresh data. If data will be imported several times a week , with several entries for the same date against user BA123 , then when user BA123 does not have any entries in the monthly master , a new entry will have to be made , but thereafter , any new imported data will have to be added to this new entry , as far as Visit Counts and Item Counts are concerned.


Narayan
 
Sorry if my statement is confusing. My thought is that as the data is obtained weekly or biweekly, the update for the respective user will "flow" into the matching user cell. I will have consider if a user should is added or deleted.
 
As data is added for the designated period, can I use =COUNTIFS(range, user name) on summary sheet to pull and total each users?
 
Hi ,


Is it possible you can upload a raw data Excel worksheet , as it would be imported into your Summary worksheet ?


This would give a better idea as to the kind of formulae and other processing that could be used to get what you want in your Summary.


At present , I am seeing that the total visit count for each user is just a number that has been entered ; actually , if the data is being imported , it would be better if the data is imported into a separate tab , so that the Summary tab contains only formulae , in case you want the report to be produced using only formulae ; alternatively , the Summary report could be produced by using macros , which make use of the imported data from its own tab , and put the processed data on the Summary tab. The choice is yours.


Narayan
 
Here is test file I am working with. I know you asked for an original as imported but I do not have at the moment. I have 2 single days [I do plan to run bi-weekly],in each, I have inserted column E - "Hours" this is inserted manually. I have added columns J & K. I know you asked for an original as imported but I do not have at the moment. I plan to remove all date for rows 1 - 5 as imported. I am building up to the summary sheet. Unfortunately, I do not know much about macros.


http://www.2shared.com/document/4aHGnTbt/ProdReport.html


Thanks.
 
Hi ,


Thanks for the sample worksheet. I have a few points for clarification :


1. I am not sure that what you want can be done using only formulae ; at least the totalling and averaging can be done by using formulae , but the consolidation of the different raw data worksheets into one Summary worksheet may require macros. In such a case , some kind of standardization will help. The naming of the tabs should also follow a convention ; in case October 24 is 10 24 , then October 25 should be 10 25 ; 10-25 is OK but not desirable.


2. Since every entry has only one date , why not have the user name entry and the date entry in the same row ? Or will there be multiple date entry against one user entry ? If both the single date entry and the user name entry are on the same row , it'll , at the least , avoid the divide by zero errors.


3. Against user ZL938 ( row 55 on tab 10-25 ), does not have any hours entered ; how will this be taken care of ?


Narayan
 
Hi,


1. can you direct me to macro examples that will help me achieve the summary sheet? I can probably follow that. Okay for the naming convention.


2. The file imports as you see in the example, except where I have added the total and average cells. I am not sure if I can avoid the zero errors as far as the drill down info is concerned


3. Hours will always be entered, I didn't have at the time.


Thanks.
 
Hi ,


I am rusty , and will take some time to put together the macro(s). Probably someone like Luke may be able to help , if you are in a hurry.


If you are not in a hurry , I can certainly help out with this.


Narayan
 
Hi ,


I have uploaded two files :


Incoming_data.xlsm and Summary_Report.xlsm ; the Summary_Report worksheet contains the procedure for incorporating the weekly data into the Summary_Report file.


At present there are data for two days 10/24 and 10/25 , in the two tabs labelled "10 24" and "10-25" , in the Incoming_data file.


Run the Incorporate procedure with both the files open.


You will have to change the following statement ( it occurs in two places ) :


Workbooks("Incoming_Data.xlsm").Worksheets("10 24").Activate


If it is "10 24" , then the data from the tab labelled "10 24" will be incorporated

If it is "10-25" , then the data from the tab labelled "10-25" will be incorporated


into the Summary_Report file.


Obviously , this is just a first draft and a lot more work has to be done to make the procedure robust.


Narayan


https://skydrive.live.com/?cid=754467ba13646a3f&sc=documents&event_source=CloseButton&id=754467BA13646A3F%21102#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21105


https://skydrive.live.com/?cid=754467ba13646a3f&sc=documents&event_source=CloseButton&id=754467BA13646A3F%21102#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21106
 
Thanks, Asa.


Narayan, I was able to open but did not find the instructions in the Summary Report. The links are lost.
 
Hi ,


I think there is a problem that Skydrive does not support VBAProject , or something like that. I am posting the two procedures here. Copy and paste them in ThisWorkBook in your VBAProject.


Public Sub Incorporate()

Application.ScreenUpdating = False

Start_Cell = "A7"

End_Cell = "A100"

ThisWorkbook.Worksheets("Week4").Activate

Range(Start_Cell).Select

counter = 0

Workbooks("Incoming_Data.xlsm").Worksheets("10-25").Activate

Range(Start_Cell, End_Cell).Select

For Each user_name In Selection

If Is_User_Name_Valid(user_name) Then

item_date = Range(user_name.Address).Offset(1, 1).Value

Hours_Worked = Range(user_name.Address).Offset(0, 2).Value

Visit_Count = Range(user_name.Address).Offset(0, 3).Value

Proc_Count = Range(user_name.Address).Offset(0, 6).Value

ThisWorkbook.Worksheets("Week4").Activate

added = False

For i = 0 To 100

If user_name = Range(Start_Cell).Offset(i, 0).Value Then

Range(Start_Cell).Offset(i, 2).Value = Range(Start_Cell).Offset(i, 2).Value + Hours_Worked

Range(Start_Cell).Offset(i, 3).Value = Range(Start_Cell).Offset(i, 3).Value + Visit_Count

Range(Start_Cell).Offset(i, 4).Value = Range(Start_Cell).Offset(i, 4).Value + Proc_Count

added = True

Exit For

End If

Next

If Not added Then

ActiveCell.Offset(counter, 0).Value = user_name

ActiveCell.Offset(counter, 1).Value = item_date

ActiveCell.Offset(counter, 2).Value = Hours_Worked

ActiveCell.Offset(counter, 3).Value = Visit_Count

ActiveCell.Offset(counter, 4).Value = Proc_Count

End If

counter = counter + 1

Workbooks("Incoming_Data.xlsm").Worksheets("10-25").Activate

End If

Application.ScreenUpdating = True

Next

End Sub


Function Is_User_Name_Valid(ByVal username As String)

Is_User_Name_Valid = True

If Len(username) <> 5 Then Is_User_Name_Valid = False

first_two_characters = Left(username, 2)

If Val(first_two_characters) <> 0 Then Is_User_Name_Valid = False

Last_three_characters = Right(username, 3)

If Val(Last_three_characters) = 0 Then Is_User_Name_Valid = False

End Function


Narayan
 
You must log in or register to reply here.
Back
Top