Client list to not move order

Jberkhoff 

Let's see if I understand this situation by checking a few points:

1. The master Client List contains one row per client.
2. If the client name is not unique (e.g., there are multiple "John Smith" clients), the master Client List row contains a column containing some other identifier that is unique.
3. The employee-specific Client Lists currently contain just one row per client -- for clients either currently assigned or previously assigned. (But see my later note on my implementation.)
4. The rows in the employee-specific Client List contain the client's name/identifier only as the result of your FILTER formula(s).
5. All client-related data in the employee lists other than the name/identifier is entered manually.
6. You want the order of entries (rows) in each employee's Client List to be retained as-now, with newly-assigned/reassigned clients to be added at the bottom.

If any of those statements are incorrect, let us know.

It seems that you need to segregate the uniqueness of the clients (master Client List) from their possible recurrence because of the employee reassignments. I propose an Assignment History List, on its own worksheet. This sheet would contain at a minimum columns for Client Name (or ID) and Employee Name and a Flag, but preferably include also Assignment Date (values do not have to be precise); rows would be ordered by Assignment Date. The employees' lists can be extended based on the content of this new list. The order of entries on the Assignment History list controls the order of entries on the employees' lists; the order of entries on the master list becomes irrelevant.

You did not identify which version of Excel you are using. VBA code is ~required, to automatically generate rows in the Assignment History List as you make reassignments, and VBA requires a desktop version of Excel. The Worksheet_Change event handler is the key asset with which to implement this.

Of course, using VBA introduces potential security issues. If yours is a single-user workbook, then that's less of a concern.

Note that in my implementation (code shown below, and in the attached workbook):

⦁ Upon reassignment, an indicator will appear for the client's row in the Client List for the previously-assigned employee. That's making use of the Flag column noted earlier. I use conditional formatting to highlight those indicators for the reassigned clients.
⦁ If a client were assigned to an employee, that employee's Client List would get an entry (as you would expect). If that client were reassigned to another employee, that client row would show a reassignment indicator (as noted above). And if that client were reassigned back to the employee again, a second row the client row would be added. If that's not acceptable, more VBA coding (which I probably will not supply) and different row management would be required.
⦁ If the client changes their name, you'll need to consider whether to update the name across existing data, or to create a new row in the master list as though this were a new client, and "de-assign" the old client name for the employee that is currently assigned.

If that solution appears good to you, you can try out the attached workbook. But if you are suspicious of the content (fair enough):

1. Copy the code shown below into an editor (e.g., Notepad, WordPad, Word).
2. Review the code to satisfy yourself that it does what you expect, and that it does not contain malware. (The code does not access other files or workbooks, and makes no API calls.) Get assistance if needed.
3. Make a test copy of your workbook.
4. Based on your existing data, create and populate the Assignment History worksheet; its name is your choice. Save your work.
5. Back to the code, in the Const[ant] lines at the top, change the worksheet name literals, column literals, and row literals as appropriate.
6. Right-click the tab of the worksheet that contains your master client list. Select View Code. This open the VBA editor. Paste all of the edited code into the code editor. (If this results in two "Option Explicit" statements, remove one.) Again, save the copy.

Then you are ready to test/experiment

Option Explicit


    '====   CONSTANTS
    
    '----   Workbook structure:
    Private Const strWORKSHEET_CLIENT_MASTER = "Clients"
    Private Const strCM_COL_CLIENT_NAME_OR_ID = "A"
    Private Const strCM_COL_ASSIGNED_EMPEE = "C"
    Private Const in4CM_ROW_FIRST_CLIENT As Long = 2
    '
    Private Const strWORKSHEET_ASSIGNMENT_HISTORY = "AssignmentHistory"
    Private Const strAH_COL_CLIENT_NAME_OR_ID = "A"
    Private Const strAH_COL_ASSIGNED_EMPEE = "B"
    Private Const strAH_COL_ASSIGNMT_DATE = "C"
    Private Const strAH_COL_FLAG = "D"
    Private Const in4AH_ROW_FIRST_CLIENT As Long = 2


Private Sub Worksheet_Change(ByVal Target As Range)

    Dim objThisWorksheet    As Worksheet
    Dim strRangeForAssignedEmpee    As String
    Dim objRangeForAssignedEmpee    As Range
    Dim objEditedAssignments    As Range
    '
    Dim objCell     As Range
    
    '----   Populate an object for this worksheet.
    Set objThisWorksheet = ActiveSheet
    '...which likely works; but because one sheet's code can change
    '   another sheet, this is better:
    Set objThisWorksheet = Sheets(strWORKSHEET_CLIENT_MASTER)
    
CheckForChangesInAssignedEmpee:
    '----   Determine the range of possible Assigned Employee cells.
    strRangeForAssignedEmpee = strCM_COL_ASSIGNED_EMPEE & ":" _
            & strCM_COL_ASSIGNED_EMPEE  'We don't need to exclude rows for _
            the column header or empty rows, because they have no corres- _
            ponding client, and later code will handle them differently.
    Set objRangeForAssignedEmpee = objThisWorksheet.Range( _
        strRangeForAssignedEmpee)
    
    '----   Identify the Assigned Employee cells that were changed...
    Set objEditedAssignments = Intersect(objRangeForAssignedEmpee _
            , Target)
    If objEditedAssignments Is Nothing Then
        GoTo CheckForChangesInNextSetOfData
    End If
    '  --   ...and process them individually.
    For Each objCell In objEditedAssignments
        Call ProcessPossibleReassignment(objCell)
    Next objCell
    
CheckForChangesInNextSetOfData:
    '...as needed
    
WkshtChange_Exit:
    Set objCell = Nothing
    Set objEditedAssignments = Nothing
    Set objRangeForAssignedEmpee = Nothing
    Set objThisWorksheet = Nothing
    Exit Sub

End Sub


Private Sub ProcessPossibleReassignment(ByVal EditedCell As Range)
'   This procedure does the processing for changes in the value of the
'       specified spreadsheet cell, which should/will contain Assigned
'       Employee identifiers (presumed to be names).  The changes will
'       typically be initial assignments, but may also be reassignments
'       or de-assignments (including removal of junk data).

    Dim in4CMRow    As Long
    Dim strNewAssignee  As String
    Dim strClientName   As String
    Dim objAssigneeWksht    As Worksheet
    '
    Dim objAssignmtHistWksht    As Worksheet
    Dim in4AHRow    As Long
    Dim strAHRow    As String
    Dim in4AHLastRow    As Long
    Dim in4AHLatestRowForClient As Long
    Dim strPriorAssignee    As String
    '
    Dim strMessage  As String
    Dim in4UserResponse As VbMsgBoxResult
    
    '----   Capture info from/about this cell...
    in4CMRow = EditedCell.Row
    If in4CMRow < in4CM_ROW_FIRST_CLIENT Then
        '...a column header is being changed.  Allow that.
        Exit Sub
    End If
    '
    strNewAssignee = EditedCell.Value
    '
    strClientName = EditedCell.Worksheet.Range(strCM_COL_CLIENT_NAME_OR_ID _
            & CStr(in4CMRow)).Value
    '  --   ...and about a related worksheet which may or may not exist:
    On Error Resume Next
    Set objAssigneeWksht = Sheets(strNewAssignee)
    On Error GoTo 0
    
    '----   Handle some bad entries.
    If strNewAssignee <> "" _
    And strClientName = "" Then
        strMessage = "You are entering a value into a cell in row " _
                & CStr(in4CMRow) & " that is reserved for employee assignments." _
                & vbCrLf & vbCrLf & "Are you sure you want to do that?"
        in4UserResponse = MsgBox(strMessage, vbExclamation Or _
                vbYesNo Or vbDefaultButton2)
        If in4UserResponse = vbNo Then
            '   Remove the entry.
            Application.EnableEvents = False
            EditedCell.Value = ""
            Application.EnableEvents = True
        End If
        GoTo ProcessPossReassign_Exit
    End If
    '  --
    If strNewAssignee <> "" _
    And objAssigneeWksht Is Nothing Then
        strMessage = "There is no worksheet for the employee you entered: " _
                & strNewAssignee
        Call MsgBox(strMessage, vbExclamation Or vbOKOnly)
        '   Remove the entry.
        Application.EnableEvents = False
        EditedCell.Value = ""
        Application.EnableEvents = True
        '
        GoTo ProcessPossReassign_Exit
    End If
    '  --   If an invalid entry is being removed, allow it.
    If strNewAssignee = "" Then
        If strClientName = "" Then
            GoTo ProcessPossReassign_Exit
        End If
    End If
    '  --   Removal of a valid entry will be checked after this...
    
    '----   Find the last Assignment History row for this client.
    Set objAssignmtHistWksht = Sheets(strWORKSHEET_ASSIGNMENT_HISTORY)
    With objAssignmtHistWksht
        in4AHLastRow = .Range(strAH_COL_CLIENT_NAME_OR_ID & in4AH_ROW_FIRST_CLIENT _
                ).End(xlDown).Row
        For in4AHRow = in4AHLastRow To in4AH_ROW_FIRST_CLIENT Step -1
            strAHRow = CStr(in4AHRow)
            If StrComp(.Range(strAH_COL_CLIENT_NAME_OR_ID & strAHRow).Value _
                    , strClientName, vbTextCompare) = 0 Then 'a case-insensitive comparison
                in4AHLatestRowForClient = in4AHRow
                strPriorAssignee = .Range(strAH_COL_ASSIGNED_EMPEE & strAHRow).Value
                Exit For
            End If
        Next in4AHRow
        'If in4AHLatestRowForClient = 0 Then
            '...this is probably a new client.
        'End If
    End With
    
    '----
    If strNewAssignee = "" Then
        strMessage = "You are removing the assignment of " & strClientName _
                & " to " & strPriorAssignee & ". Chaos may ensue. Are you sure?"
        in4UserResponse = MsgBox(strMessage, vbExclamation Or vbYesNo Or _
                vbDefaultButton2)
        If in4UserResponse = vbNo Then
            GoTo ProcessPossReassign_Exit
        End If
    End If
    
    '----   Mark the latest Assignment History row for this client
    '       as Reassigned.
    If in4AHLatestRowForClient <> 0 Then
        strAHRow = CStr(in4AHLatestRowForClient)
        With objAssignmtHistWksht.Range(strAH_COL_FLAG & strAHRow)
            .Value = .Value & "R"
        End With
    End If
    
    '----   Conditionally create a new Assignment History row.
    If strNewAssignee <> "" Then
        With objAssignmtHistWksht
            in4AHLastRow = in4AHLastRow + 1
            strAHRow = CStr(in4AHLastRow)
            .Range(strAH_COL_CLIENT_NAME_OR_ID & strAHRow).Value = strClientName
            .Range(strAH_COL_ASSIGNED_EMPEE & strAHRow).Value = strNewAssignee
            .Range(strAH_COL_ASSIGNMT_DATE & strAHRow).Value = Now
            .Range(strAH_COL_FLAG & strAHRow).Value = ""
        End With
    End If
    
ProcessPossReassign_Exit:
    Set objAssignmtHistWksht = Nothing
    Set objAssigneeWksht = Nothing
    Exit Sub

End Sub

I still think that point #4 is a bad design decision. It leaves your employee-specific data fragile. But once VBA is introduced, it becomes an easy matter to store (possibly discreetly) the client's unique identifier in the employees' worksheets during an assignment. In fact, it might be advisable to store all relevant data in the employees' worksheets and remove the FILTER formulas.