Picking the right Job Title between two or more dates using time sheet date and effective date employee held title

[algere]

I have a 400 MB excel workbook filled with Labor Ledger data but it is missing job titles for each line. It contains 49 columns of data and there are two ledger tabs: the first is named "1LD" and the second "2LD". I would like to focus on 2 columns within worksheets named "1LD" and "2LD" to populate the Job Title from another worksheet within the same workbook labeled "Emp". The worksheets named "1LD" and "2LD" contain a 6 digit "Normalized Labor ID" and "GLPSTime Stamp Date" (Time Sheet Date). The worksheet named "Emp" contains more than one effective date per employee if they have held more than one job. From my analysis I can see that the max Job Title change per employee is 8. So, an employees name might be on the list a maximum of 8 times with 8 job titles and 8 effective dates. Ledger:1) Column L: The "Normalized Labor ID" (these could be "000006" or "D03456" and are in no particular sequence but are always 6 digits)2) Column Z: The "GLPSTime Stamp Date" (these dates are in the format of M/DD/YYYY)Employee List with Employee ID and Job Title:1) Column A: "Normalized Employee ID"2) Column E: "Effdt" (Effective Date)3) Column G: "PTS Job Title"My only thought is to have a formula or macro populate the Job Title by matching the 6 digit "Normalized Labor ID" and looking to see if the "GLPSTime Stamp Date" (time sheet date) is before or after each "Effdt" (Effective Date). If it is after the first Effective Date but before the next one pick the first Job Title.

 

[jamtay317]

this can be done with a macro, no problem, we will need some sample data, can you please provide a SMALL ​sample of data, so that we can see where the inforamtion is

 

[algere]

Here you go sir, I really appreciate it.

sample.zip - Speedy Share - upload your files here

 

[algere]

jamtay317 - I just realized I forgot to add a column for the Job Title in the sample. You can choose to put it anywhere (add a column) on the 1LD worksheet. Thinking on it now, not sure if you already had an idea of where to put it; it doesn't matter at this point as long as each line has the correct job title. Thanks!

 

[jamtay317]

1. I've looked at this file, i'm not sure that I understand the file, could you please tell me where the ID number is on the 2LD sheet
2. where is your start date and your end date?
3. the name are different on 1 and 2, would it be easier to combine the sheets?

 

[algere]

You said a small sample for format. I don't have my computer in front of me right now but the first/second sheet names are combined on the third tab. The reason is there is two worksheets and over 22,000 employees / seven years labor ledger data on the original file; the row limits are reached for one sheet ... hence two sheets. The time sheet start date is the date after the name on the first and second sheet. If that persons job title effective date on sheet three is after the time sheet date (first sheet) but before their next job title held (also third sheet bc that is where it would be bc it's basically there bc it's the only thing not included in the ledger (Job Title). So I have to use the Labor ID and Timesheet Date and see what positions these people held and what date they held it bc they might have held more than one position. Start date is on sheet three and there is only an "end" date if they held more than one job. In that case the time sheet date from first two sheets will be used to see which job title to use from dates on third sheet.

 

[algere]

Further explanation... now that I have my computer in front of me...

If you look in worksheet 1LD, column K there is a Normalized Labor ID. In column X there is a GLPSTime Stamp Date. There is no Job Title available so I had to get the history of employees with the date they signed their contracts. This date is called "Effdt" (effective date) and can be found on the Emp worksheet. (The real list for the Emp worksheet (Employee worksheet) is 22,000 rows multiplied by up to 8 job titles with their effective dates).

The start and end dates... on the 1LD tab you will see for the Normalized Labor ID (column K) and GLPSTime Stamp Dates (column X) ranging from 7/10/2010 to 8/1/2012. Since there is no Job Title available in that sheet I have to look on the "Emp" worksheet to see what Job Title he is for each time sheet date. For time sheet dates 7/27/2012-8/1/2012 (Normalized Labor ID) 969891 (Superman) would be a Fuel Operator because he signed his contract on 7/27/2012 to be a Fuel Operator (I know that because on the Emp worksheet column B has his employee ID and Column E has his effective date for that period as 7/27/2012) . Any time prior to 7/27/2012 he was a General Laborer because he signed a contract on 7/10/2010 to fill that position. This is important because there are time sheet dates for 969891 (Superman) from 7/10/2010-7/14/2010 in which should be recorded as a General Laborer.

Does this make sense or should I explain it differently?

 

[jamtay317]

im working on this, I will have it done by tomorrow and upload it for you

 

[algere]

That is awesome! I appreciate your help!

 

[jamtay317]

Algere,
there are several steps that your going to have to do to complete this,
1. on 1LD and 2ld you must select the entire row 1 and right click insert row (probably will just say insert)
2. in column Y you must insert this
Excel 2012

	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	D64968													edate	5/28/2010
2	Normalized Labor ID	Fiscal Year	Period	Sub Period	Account Id	Account Name	Organization Id	Organization Name	Project Id	Project Name	Journal Code	Labor Suppress Id	Labor Suppress Name	GLPSTime Stamp Date	Job Tital

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

1LD​

Array Formulas Cell Formula Y1 {=IF(INDEX(Emp!$E$2:$E$50000,SMALL(IF(K1=Emp!A2:A50000,ROW(Emp!F2:F50000)-ROW(Emp!F2)+1),1))>TODAY(),INDEX(Emp!$E$2:$E$50000,SMALL(IF(K1=Emp!A2:A50000,ROW(Emp!F2:F50000)-ROW(Emp!F2)+1),2)),INDEX(Emp!$E$2:$E$50000,SMALL(IF(K1=Emp!A2:A50000,ROW(Emp!F2:F50000)-ROW(Emp!F2)+1),1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

on 2LD
Excel 2012

	K	V	W	X	Y
1	000002			enddate	1/5/2012
2	Normalized Labor ID	Labor Suppress Id	Labor Suppress Name	GLPSTime Stamp Date	Job Tital

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

2LD​

Array Formulas Cell Formula Y1 {=IF(INDEX(Emp!$E$2:$E$50000,SMALL(IF(K1=Emp!A2:A50000,ROW(Emp!F2:F50000)-ROW(Emp!F2)+1),1))>TODAY(),INDEX(Emp!$E$2:$E$50000,SMALL(IF(K1=Emp!A2:A50000,ROW(Emp!F2:F50000)-ROW(Emp!F2)+1),2)),INDEX(Emp!$E$2:$E$50000,SMALL(IF(K1=Emp!A2:A50000,ROW(Emp!F2:F50000)-ROW(Emp!F2)+1),1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

after doing this you can hide the row (selectthe entire row again and right click hide)

3.next you need to hit Alt+F11 insert 2 modules and call them what ever you wantto
(renameby highlighting that module and hit F4, you then can type where it says"module 1" or 2)

this is module 1 copy and paste

[/FONT]Sub ADEployee()
    Dim ld1 As Worksheet, ld2 As Worksheet, emp As Worksheet, id1 As String, Job As String, id2 As String, sid As String, sdate As Date, edate As Date, empdate As Date
    Set ld1 = ThisWorkbook.Sheets("1ld")
    Set ld2 = ThisWorkbook.Sheets("2ld")
    Set emp = ThisWorkbook.Sheets("emp")
    lr1 = ld1.Cells(Rows.Count, 11).End(xlUp).Row
    lr2 = ld2.Cells(Rows.Count, 11).End(xlUp).Row
    elr = emp.Cells(Rows.Count, 1).End(xlUp).Row
    elc = emp.Cells(1, Columns.Count).End(xlToLeft).Column
    lc1 = ld1.Cells(1, Columns.Count).End(xlToLeft).Column
    lc2 = ld2.Cells(1, Columns.Count).End(xlToLeft).Column
    e = 2
    'column varilables
    k = 11
    v = 22
    w = 23
    xvar = 24
    yvar = 25
    x1 = 3
    x2 = 3
    
' text to columns
    'employee
    
    emp.Activate
    emp.Range(Cells(1, 1), Cells(1, elc)).Select
    Selection.Font.Bold = True
    emp.Cells(1, 2).Select
    emp.Range(Cells(2, 1), Cells(elr, 1)).Select
    Selection.Texttocolumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "000000"
    
    
    '1ld
    ld1.Activate
    ld1.Range(Cells(2, 1), Cells(2, lc1)).Select
    Selection.Font.Bold = True
    ld1.Cells(3, k).Select
        ld1.Range("K3:K" & lr1).Select
    Selection.Texttocolumns Destination:=Range("K3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "000000"
    
    '2ld
        ld2.Activate
        ld2.Range(Cells(2, 1), Cells(2, lc2)).Select
        Selection.Font.Bold = True
        ld2.Cells(3, k).Select
        ld2.Range("k2:K" & lr2).Select
    Selection.Texttocolumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "000000"
' sort
    Call sortids
    Call SortEmp


    'loop 1
        Do
        DoEvents
        sid = emp.Cells(e, 1)
        empdate = emp.Cells(e, 5)
        Job = emp.Cells(e, 7)
       


        'loop 2
            On Error Resume Next
                id1 = ld1.Cells(x1, k)
                id2 = ld2.Cells(x2, k)
                lookingnumber = Empty
            'choosing case number
                If Not Application.IsNumber(Application.WorksheetFunction.Match(emp.Cells(e, 1), ld1.Range("k3:k1000000"), 0)) Then
                    lookingnumber = 2
                End If
                If Not Application.IsNumber(Application.WorksheetFunction.Match(emp.Cells(e, 1), ld2.Range("k3:k1000000"), 0)) Then
                   lookingnumber = 1
                End If
                Select Case lookingnumber
                Case 1
                    
                   ld1.Cells(1, k) = sid
                   sdate = ld1.Cells(1, w)
                   edate = ld1.Cells(1, yvar)
                   sr = Application.WorksheetFunction.Match(ld1.Cells(1, k), ld1.Range("k3:k100000"), 0) + 2
                   er = Application.WorksheetFunction.CountIf(ld1.Range("k3:k100000"), ld1.Cells(1, k)) + sr - 1
                    For x1 = sr To er
                        If edate = empdate Then
                            ld1.Cells(x1, yvar) = Job
                        End If
                    Next x1
                Case 2
                   ld2.Cells(1, k) = sid
                   sdate = ld2.Cells(1, w)
                   edate = ld2.Cells(1, yvar)
                   sr = Application.WorksheetFunction.Match(ld2.Cells(1, k), ld2.Range("k3:k100000"), 0) + 2
                   er = Application.WorksheetFunction.CountIf(ld2.Range("k3:k100000"), ld2.Cells(1, k)) + sr - 1
                    For x2 = sr To er
                        If edate = empdate Then
                            ld2.Cells(x2, yvar) = Job
                        End If
                    Next x2
                End Select
            
        e = e + 1
       x1 = x1 + 1
       x2 = x2 + 1
        Loop Until e = elr + 1
End Sub


[FONT=Verdana]

module 2

[/FONT]Sub sortids()
'
' sortids Macro
    Dim ld1 As Worksheet, ld2 As Worksheet, emp As Worksheet, id1 As String, Job As String, id2 As String, sid As String, sdate As Date, edate As Date, empdate As Date
    Set ld1 = ThisWorkbook.Sheets("1ld")
    Set ld2 = ThisWorkbook.Sheets("2ld")
    Set emp = ThisWorkbook.Sheets("emp")
    lr1 = ld1.Cells(Rows.Count, 11).End(xlUp).Row
    lr2 = ld2.Cells(Rows.Count, 11).End(xlUp).Row


'
    Range("V6").Select
    ActiveWorkbook.Worksheets("1LD").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("1LD").AutoFilter.Sort.SortFields.Add Key:=Range( _
        Cells(2, 11), Cells(lr1, 11)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("1LD").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("2LD").Select
    Range("K7").Select
    ActiveWorkbook.Worksheets("2LD").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("2LD").AutoFilter.Sort.SortFields.Add Key:=Range( _
        Cells(3, 11), Cells(lr2, 11)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("2LD").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


Sub SortEmp()
    Dim ld1 As Worksheet, ld2 As Worksheet, emp As Worksheet, id1 As String, Job As String, id2 As String, sid As String, sdate As Date, edate As Date, empdate As Date
    Set ld1 = ThisWorkbook.Sheets("1ld")
    Set ld2 = ThisWorkbook.Sheets("2ld")
    Set emp = ThisWorkbook.Sheets("emp")
    lr1 = ld1.Cells(Rows.Count, 11).End(xlUp).Row
    lr2 = ld2.Cells(Rows.Count, 11).End(xlUp).Row
    emplr = emp.Cells(Rows.Count, 1).End(xlUp).Row
        Range("A2").Select
        emp.Sort.SortFields.Clear
        emp.Sort.SortFields.Add Key:=Range("A2:A9"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        emp.Sort.SortFields.Add Key:=Range("E2:E9"), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Emp").Sort
            .SetRange Range(Cells(1, 1), Cells(emplr, 7))
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
End Sub


[FONT=Verdana]

then you can exit out of VBA and hit alt F8 to run ADEployee