Combine each sales rep individually

greekexcel

New Member
Joined
Oct 31, 2022
Messages
32
Office Version
  1. 2019
Platform
  1. Windows
I have a table which look like this.

I want excel to understand each "SALES REP" as person.
For example to calculate all sales by KEVIN including the one with collaboration with other sales rep (KEVIN&JOHN) and split the Commission in half or 1/3 depending the situation
Also every time that i input 2 names in the same cell to can excel recognize them as individuals.

Thank you




demo.png
 
Hello again and thank you very much :)
I pasted the code(deleting the red text) but unfortunately it does not work.
What am i doing wrong ?Should i combine both of them?
Thank you in advance
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You should completely delete the code that I sent previously. And paste this code as fresh :)
 
Upvote 0
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
 
Upvote 0
U mean this one without description(redtext)?
i create a module and run it
 
Upvote 0
Please find the sample file below. You may run the code in two ways:
1. Pressing "Recalculate" button will run the calculateCommission function manually.
2. Any change in column E or F will trigger recalculation.

 
Upvote 0

Forum statistics

Threads
1,226,329
Messages
6,190,321
Members
453,604
Latest member
ADJ2RGJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top