Dynamic Formulas: Sort Two Columns with Exclusion Criteria

[AntNMC1]

Hello,
I have three columns, Department, Employee Name, and Contract Status. The data table is about 670 rows and will continue to grow. Thus, on a separate tab, I would like to extract the Department and Employee Name columns, but the rules are as follows:
1) Sort alphabetically, Department first, then Employee Name second
2) If the Contract Status has a reason, then the record must be excluded from the list
3) The formulas must be dynamic, as this list will be continuously updated.
Below are two example tables (before and after) of what I am trying to achieve.

(Cell A1)Department	Employee Name	Contract Status
Sales	Bob
HR	John
Sales	Randy	9 Month
HR	Jane
Finance	Bill	Special Project
Finance	Robert
HR	Julie	3 Month

<tbody>
</tbody>

Desired Results

Department	Employee Name
Finance	Robert
HR	Jane
HR	John
Sales	Bob

<tbody>
</tbody>

Thank you

 

[alansidman]

Here is some VBA code for you

Option Explicit
Sub sortncopy()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False


    s1.Sort.SortFields.Clear
    s1.Sort.SortFields.Add Key:=Range("A2:A" & lr), _
                           SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    s1.Sort.SortFields.Add Key:=Range("B2:B" & lr), _
                           SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With s1.Sort
        .SetRange Range("A1:C" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1:C1").AutoFilter
    s1.Range("$A$1:$C$" & lr).AutoFilter Field:=3, Criteria1:=""
    Range("A1:C" & lr).Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[AntNMC1]

Anything that doesn’t involve VBA? Thank you

 

[Peter_SSs]

See if this is any use for you. I haven't tested it greatly so check results carefully and post back with details if it is failing in some circumstances.

I have done this on a single sheet but if it works for your requirements then you should be able to adapt it to two sheets.
I have also used some helper columns but these could be hidden once populated.
In all formulas, adjust the ranges (I have used to row 100) so that they will be bigger than your data will ever be.
Formulas in D2:E2 copied down.
Formula in G2 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Department	Employee Name	Contract Status				Department	Employee Name
2	Sales	Bob		6	6000002		Finance	Robert
3	HR	John		3	3000004		HR	Jane
4	Sales	Randy	9 Month				HR	John
5	HR	Jane		4	3000003		Sales	Bob
6	Finance	Bill	Special Project
7	Finance	Robert		2	2000007
8	HR	Julie	3 Month
9
Sort

 

[alansidman]

Anything that doesn’t involve VBA? Thank you

Why?