Private Sub Worksheet_Change(ByVal Target As Range) 'Calculate in every worksheet change
If (Not Intersect(Target, Range("E:E")) Is Nothing And Target.Offset(0, 1).Value <> "") Or (Not Intersect(Target, Range("F:F")) Is Nothing And Target.Offset(0, -1).Value <> "") Then
'If change has been made in Column E or F and adjancent cell has a value.
Application.EnableEvents = False 'Stop listening change events for now
Dim repNames() As String
Dim tempNames() As String
Dim lRow As Integer
lRow = Cells(Rows.Count, 6).End(xlUp).Row 'Get the last row for Reps
Dim ii As Integer
Dim iii As Integer
Range("H2:J" & Cells(Rows.Count, 10).End(xlUp).Row).Clear 'Clear previous calculation
ii = 0
For i = 2 To lRow
tempNames = Split(Cells(i, 6).Value, "&") 'Fetch each rep row to a temporary array
For Each tempName In tempNames
ReDim Preserve repNames(ii)
repNames(ii) = Trim(tempName) 'Insert each rep in main array from temporary array
ii = ii + 1
Next
Next
'Clear dublicate names
For i = ii - 1 To 1 Step -1
If repNames(i) <> "" Then
For iii = i - 1 To 0 Step -1
If repNames(i) = repNames(iii) Then 'Clear rep name if found in other position of array
repNames(i) = ""
End If
Next
End If
Next
iii = 2
For i = 0 To ii - 1
If repNames(i) <> "" Then 'Write each rep name to cell if the name is not empty.
Cells(iii, 8).Value = repNames(i)
iii = iii + 1
End If
Next
For i = 2 To iii - 1 'For each unique rep name in Column H
For ii = 2 To lRow 'For each comm. and rep in column E&F
If InStr(Cells(ii, 6).Value, Cells(i, 8).Value) > 0 Then 'If Column F contains rep name in Column H
'Divide commission amount to number of "&" sign+1 and add to previous cell value
Cells(i, 9).Value = Cells(i, 9).Value + (Cells(ii, 5).Value / ((Len(Cells(ii, 6).Value) - Len(Replace(Cells(ii, 6).Value, "&", ""))) + 1))
'Add score to column J with same logic instead of using comm. value, using 1.
Cells(i, 10).Value = Cells(i, 10).Value + (1 / ((Len(Cells(ii, 6).Value) - Len(Replace(Cells(ii, 6).Value, "&", ""))) + 1))
End If
Next
Next
Application.EnableEvents = True 'Start to listen changes again
End If
End Sub