• 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.

Team Matrix ?

M

MikeVBA

New Member
Am trying to create a matrix based on how long a person has known another. Let me explain:


1. Mark works in DeptA from 01.01.2010 to 30.10.2010

2. Sam works in DeptA from 01.02.2010 to 31.11.2010

3. Bill works in DeptA from 01.09.2010 to 30.09.2010


Hence, Mark and Sam know each other for 10 months. Bill knows Mark and Sam for 1 month

I have around 8000 such line items with over 2000 unique employees - need to create a matrix from this - can someone help ?
 
Hi Mike ,


Can you indicate how your input data is organized , and how you want the matrix laid out ?


For the matrix , do you want employees as the row as well as column headers , and the individual cells to have the time periods ? Should the periods be in months / days / weeks / years ?


Narayan
 
Here you go:


EmpID, Function, Department, From Dt, To Dt

Line Items are around 8,000. Unique employees are around 1800+

Some of the employees worked in different functions within the same department Example:

EmpID, Function, Department, From Dt, To Dt

12345, PettyCash, Accounts, 01.01.2010 30.01.2010

12345, Disbursement, Accounts, 01.02.2010 15.03.2010


I have been thinking of grouping this by Departments (disregarding the function) and planning a matrix like this

12345 67890

12345 45

67890 45


here is shows that 12345 and 67890 knew each other for 45 days - and hence the coordinates between 12345 and 67890 show 45 against each other
 
Hi MikeVBA,


as per your current structure,

[pre]
Code: 
=SUMPRODUCT(--ISNUMBER(MATCH(
ROW(INDIRECT(VLOOKUP(H2,$A$2:$E$21,4,0)&":"&VLOOKUP(H2,$A$2:$E$21,5,0))),
ROW(INDIRECT(VLOOKUP(H3,$A$2:$E$21,4,0)&":"&VLOOKUP(H3,$A$2:$E$21,5,0))),0)))
[/pre]

where $A$2:$E$21 is Table Area.

H2 is 1st Employee Code & H3 is 2nd Employee Code..


https://dl.dropbox.com/u/78831150/Excel/Team%20Matrix%20%28Mike%20VBA%29.xlsx


Regards,

Deb
 
Deb,


If we have a matrix, we can look up the coordinates (row/col) and find how many days one worked with the other. But to create this, we would require VBA code - not just formulas - since it needs to process each employee and check against the other employees for the dates - creating the algorithm is the toughest part - which I am racking my brains over.


Regards,

Michael
 
Hi Michael..


So you want the answer in Matrix Format..

Can you please download the file again.. :)


Regards,

Deb
 
Oof Deb ! Spped just got redefined ..


Looks good on first look - let me test it and revert with comments


many many thanks until then
 
Deb,


Could you also show much many functions within a department the 2 people worked together and also how many departments they worked together in 2 separate formula sheets ?


Would really appreciate this
 
Deb,


On Testing this, find that it would only work when there is 1 instance of each resource. Remember that there can be multiple instances of each resource - where he worked in different departments, functions.


We would require something that fetches the Emp and goes into a loop to derive relationship with other employees


Back to square one
 
Deb - Output would "exactly" be the same as the matrix output you produced. We can have 3 matrices - 1 to show the number of days, 1 to show the number of departments and another one to show the number of functions - that two people worked together in


Let me know if you still need a sample output
 
Yes.. I need Sample output.. plus your idea/thinking regarding presentation..


How you like to plot..

Employee Code, Department & Function.. and in hoe many matrix..

and how you like to compare..


Regards.

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