I need an excel formula that will look at Location, Department Code and Job Title then return the Full Name. The problem that I am having is that locations can use the same department code and job title. There may also be multiple result which I will need to be shown in multiple cells. This will be used for an employee breakdown by location and department. I am using Excel 2016.
Example: I want to show all names that match the criteria below.
Location – Virginia
Department Code – 02023, 02023C, 02023A
Job Title – Porter, Porte/Serv, Detail, Service Porter, Detailer-Recon
This is the formula I use for positions that only have one criteria.
I think the easiest way to to use a macro. This one will work. Replace your sheet name with the one in your spreadsheet, place this in a module on in the VBE for the sheet.
Make sure to replace the 5 in this bit with the column where you want to print the name.
You can then filter on that column, and you have your list.
Let me know if this works.
ccccThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 5).Value = strCurrName
Sub find() Dim strLocation As String Dim strHOmeDept As String Dim strJobTitle As String Dim strCurrentLoc As String Dim strCurrHomeDept As String Dim strCurrJobTitle As String Dim strCurrName As String Dim intCurrRow As Integer Dim intEndRow As Integer Dim i As Integer intCurrentRow = 1 intEndRow = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row For i = 1 To intEndRow intCurrentRow = intCurrentRow + 1 strCurrentLoc = ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 1).Value strCurrHomeDept = ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 2).Value strCurrJobTitle = ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 4).Value strCurrName = ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 3).Value If strCurrentLoc = "VIRGINIA" And _ (strCurrHomeDept = "02023" Or strCurrHomeDept = "02023C" Or strCurrHomeDept = "02023A") _ And (strCurrJobTitle = "DETAILER-RECON" Or strCurrJobTitle = "PORTER" Or strCurrJobTitle = "DETAIL" Or strCurrJobTitle = "SERVICE PORTER" _ Or strCurrJobTitle = "PORTE/SERV") Then ThisWorkbook.Sheets("Sheet1").Cells(intCurrentRow, 5).Value = strCurrName End If Next End Sub
Your issue can be solved by using Advance Filter also:
How it works:
- Copy Headers in Row
- Enter Criteria in
F71, G71:G73 & I71:I74.
- Click Advance Filter from Data Tab, Sort
& Filter Menu.
- Fill cell references as shown in the Advance
Filter dialogue below.
- Finish with Ok
- Results may vary as Critetia been used.
- Adjust cell references as your need.