Excel: Count how often employees work together in a week?

Question

in Excel i tried to count employees working together on a project (e.g. Project-A) in a whole week. 10 different people work together based on the scedule below. Every day two are are working on the same project, the next day on another.

Employee | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sund.
Bob      | Project-A | Project-C | Project-B | Project-C |           |           |
Jennifer | Project-B | Project-D | Project-A | Project-D |           | Project-C |
Tom      |           | Project-B | Project-C | Project-A | Project-A | Project-B |
Bill     | Project-D |           |           | Project-B | Project-C | Project-A |
Susan    | Project-C | Project-A |           |           | Project-B | Project-D |
Cathy    | Project-C | Project-A | Project-C | Project-A |           |           |
Jack     | Project-B | Project-C | Project-B | Project-B |           | Project-D |
Kate     |           | Project-B | Project-A | Project-D | Project-A | Project-C |
Anne     | Project-D |           |           | Project-C | Project-B | Project-A |
George   | Project-A | Project-D |           |           | Project-C | Project-B |

Now i don't know if some people are working more often together or in the worst case never.
The goal is to balance the people working together on each project equally by moving the different projects. E.g. Bob & Jennifer shall not work together every time (worst case planning).

Output:

Counts working together:
         | Bob      | Jennifer | Tom      | Bill    | Susan    | Cathy
Bob      |-         | 0        | 0        | 0       | 0        | 0        
Jennifer | 0        |-         | 0        | 0       | 0        | 0 
Tom      | 0        | 0        | -        | 0       | 0        | 2
Bill     | 0        | 0        | 0        | -       | 0        | 0
Susan    | 0        | 0        | 0        | 0       | -        | 2
Cathy    | 0        | 0        | 2        | 0       | 2        | -

Tried with vlookup or countifs, but it's a huge, huge formula. Is there a simple solution in excel to count two same expressions(e.g. Project-A) and then count +1 if e.g. Bob and Tom work on the same project?

thank you,

br

Answer 1

This is just going across the rows for each pair of employees and seeing how many times the project names match in the same day:

=SUMPRODUCT((INDEX($B$2:$G$11,MATCH($I2,$I$2:$I$11,0),0)=INDEX($B$2:$G$11,MATCH(J$1,$J$1:$S$1,0),0))
*(INDEX($B$2:$G$11,MATCH($I2,$I$2:$I$11,0),0)<>""))

Data

Results

Note

The row lookup isn't really needed because the list of names in A2:A11 is in the same order as the list of names in I2:I11, but I am assuming this might not always be the case.

Answer 2

I couldn't figure this out through a normal formula ! So here's my attempt with VBA which requires bit of setup.

You will have to create a master sheet which will keep track of the allocation by projects.

Now given that your weekly sheet is in this layout

You can run this code eui

Sub eqi()
Dim temp As String
Dim i As Integer
Dim j As Integer
Dim k As Integer

With Sheets("weekly")

For k = 1 To 4 Step 1
    For i = 2 To 8 Step 1
        For j = 2 To 11 Step 1
        If .Cells(j, i).Value = Sheets("master").Cells(1, 3 + k).Value Then
        temp = temp + .Cells(j, 1).Value
        End If
        Next j
            If Not temp = "" Then
                Call Findcombination(temp, k)
            End If
        temp = ""
    Next i
Next k

End With
End Sub

Sub Findcombination(temp As String, k As Integer)
Dim rFound As Range
    On Error Resume Next
    With Sheets("master")
        Set rFound = .Columns(1).Find(What:=temp, After:=.Cells(2, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        rFound.Offset(, k + 2).Value = rFound.Offset(, k + 2).Value + 1
        On Error GoTo 0
        If Not rFound Is Nothing Then Application.Goto rFound, True
    End With
End Sub

Once you run the code it will fill the allocated combination by projects by 1 ideally you should have 1 everywhere. If you run the code and get value 2 then it means the two have already worked together on the same project before.

If you don't want it by project then simply do a summation in column H of the master sheet if it's more than 1 then they have already worked together in the week.