Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Listed Employee data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Listed Employee data

    Good afternoon, by racking my tiny brains on this one as to whether it is actually possible with such little knowledge I have, although a fast(ish) learner. I tried pivot tables but could only get on row of data to pull in.
    The data I have is employee payments and deductions by date listed down the spreadsheet for each employee with repeating headings in bold. Basically individual little ranges of data.
    I would like to reorder it keeping it the data in date period order but with the employee names across the top ideally I will create a new tab for each kind of payment/deduction.
    I had a sample to post but I do not have that privilege.
    Any pointers in the right direction would be much appreciated.
    Many thanks
    Nigel

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Listed Employee data

    I had a sample to post but I do not have that privilege.
    You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
    Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

    If you are unable to use any of those tools, note that many people upload files to file sharing sites and provide links to them. Just note that many people are unable or unwilling to download files from the internet for security reasons.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Listed Employee data

    Many thanks.

    As can be seen by the snippit below that not all employee data has the same number of entries or set of dates. But the dates are consistent as in every Friday.

    HTML Code:
    Ref.        Employee Name              Date      Total Gross         E'er NIC                E'ee NIC               Tax Paid               Net Pay
    245         Employee 245    07/12/2018         1247.02 149.73   94.70     249.40   902.92
                                    14/12/2018         284.85   16.90     14.70     57.00     213.15
                                    21/12/2018         462.09   41.47     36.06     92.40     333.63
                                    28/12/2018         462.09   41.47     36.06     92.40     333.63
                                    04/01/2019         0.00        0.00        0.00        0.00        0.00
                                    11/01/2019         189.90   3.79        3.30        38.00     148.60
                                    18/01/2019         253.20   12.63     10.98     50.60     191.62
    Ref.        Employee Name              Date      Total Gross         E'er NIC                E'ee NIC               Tax Paid               Net Pay
    246         Employee246     02/11/2018         476.06   43.40     37.74     95.20     334.48
                                    09/11/2018         613.86   62.31     54.18     122.80   424.93
                                    16/11/2018         448.92   39.54     34.38     89.80     316.75
                                    23/11/2018         448.92   39.54     34.38     89.80     316.75
                                    30/11/2018         448.92   39.54     34.38     89.80     316.75
                                    07/12/2018         714.08   76.24     66.30     142.80   490.63
                                    14/12/2018         448.92   39.54     34.38     89.80     316.75
    Ref.        Employee Name              Date      Total Gross         E'er NIC                E'ee NIC               Tax Paid               Net Pay
    247         Employee 247    02/11/2018         433.44   37.47     32.58     41.00     352.24
                                    09/11/2018         434.64   37.60     32.70     41.40     352.89
                                    16/11/2018         612.76   62.17     54.06     77.00     458.78
                                    23/11/2018         347.71   25.60     22.26     23.80     296.09
                                    30/11/2018         545.60   52.92     46.02     63.60     420.67
                                    07/12/2018         447.23   39.40     34.26     43.80     361.22
                                    14/12/2018         0.00        0.00        0.00        -45.60    45.60
    Ref.        Employee Name              Date      Total Gross         E'er NIC                E'ee NIC               Tax Paid               Net Pay
    248         Employee 248    02/11/2018         410.01   34.29     29.82     36.40     336.73
                                    09/11/2018         379.25   30.01     26.10     30.20     316.63
                                    16/11/2018         379.25   30.01     26.10     30.20     316.63
                                    23/11/2018         379.25   30.01     26.10     30.20     316.63

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Listed Employee data

    Based on the sample data you posted, can you post what you would like your expected result to look like?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Listed Employee data

    Pay Date Empl 1 Empl 2 Empl 3 Empl 4
    06/04/2018 414.24 305.55 429.74
    13/04/2018 324.24 590.73 418.86
    20/04/2018 216.16 417.59 421.20
    27/04/2018 676.27 437.96 427.05
    04/05/2018 437.96 422.96
    11/05/2018 478.97 277.92 437.96 251.55
    18/05/2018 108.08 437.96 422.37
    25/05/2018 262.48 437.96 391.95
    01/06/2018 517.80 138.96 437.96 424.13
    08/06/2018 755.73 431.73
    15/06/2018 216.16 535.73 424.24
    22/06/2018 437.96 394.88
    29/06/2018 529.62 427.05
    06/07/2018 414.24 216.16 464.44 476.78
    13/07/2018 701.56 433.44
    20/07/2018 332.33 563.76 437.64
    27/07/2018 238.82 584.64 434.64
    03/08/2018 94.95 448.92 431.64
    10/08/2018 316.50 605.52 307.54

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Listed Employee data

    Those numbers really do not seem to "jive" with your original data. The dates are all different.
    Which column are the salary numbers coming from? Is it the "gross" column?

    A few other questions:
    1. Is your original data an Excel file and is it already split into different columns?
    2. Is a VBA solution acceptable (that is the only way I can think to do it)?
    3. Is it all right if we modify the original data to add employee number to every row?
    4. About how many employees are we talking about?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Listed Employee data

    Quote Originally Posted by Joe4 View Post
    Those numbers really do not seem to "jive" with your original data. The dates are all different.
    Which column are the salary numbers coming from? Is it the "gross" column? Ultimately once I am on the right track I will split the data into different tabs for each payment/deduction type. So only need to concentrate on one at a time e.g. gross pay.
    The original data for each employee has a header which is bold if that could be used as an identifier in some way. I guess the name could be used for that also.

    A few other questions:
    1. Is your original data an Excel file and is it already split into different columns? Yes it is in excel already exported from Sage.
    2. Is a VBA solution acceptable (that is the only way I can think to do it)? Yes I have had a little (read very little) experience with VBA.
    3. Is it all right if we modify the original data to add employee number to every row? Employee number can be removed as it not really necessary.
    4. About how many employees are we talking about?
    There is quite a number the original spreadsheet is just shy of a thousand lines, next year will be maybe 50% bigger hence trying to work a solution out rather than manually copy and pasting each set of payments/deductions.
    Thanks

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Listed Employee data

    Can you confirm the columns each field in your original data is located in?
    That will be critical to setting up the macro correctly, and it isn't quite clear from the original post.

    Also, for the different columns for the different employees, do you want the header row to list the employee name or employee number?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Listed Employee data

    As exported from the application they are;
    B Ref
    C Employee Name
    D Date
    E Total Gross
    F E'er NIC
    G E'ee NIC
    H Tax Paid
    I Net Pay
    Thanks

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Listed Employee data

    I am sure that there are probably more efficient ways (maybe with Power Query or something), but this should work.
    I assume that you start with one sheet, the data sheet, and it will insert and populate the "Net Pay" sheet:
    Code:
    Sub NetPayMacro()
    
        Dim srcWS As Worksheet
        Dim npWS As Worksheet
        Dim cl As Long
        Dim rw As Long
        Dim i As Long
        Dim minDate As Date
        Dim maxDate As Date
        Dim d As Date
        Dim nm As String
        Dim dte As Date
        Dim amt As Double
        Dim fRow As Long
    
        Application.ScreenUpdating = False
        
    '   Capture current data worksheet as data source sheet
        Set srcWS = ActiveSheet
        
    '   Capture minimum and maximum date values from Date column
        minDate = Application.WorksheetFunction.Min(srcWS.Range("D:D"))
        maxDate = Application.WorksheetFunction.Max(srcWS.Range("D:D"))
        
    '   Insert new sheet and call it "Net Pay"
        Sheets.Add After:=srcWS
        ActiveSheet.Name = "Net Pay"
        Set npWS = ActiveSheet
        
    '   Add title row and format column A for dates
        Range("A1").FormulaR1C1 = "Pay Date"
        Columns("A:A").NumberFormat = "dd/mm/yyyy"
        Columns("A:A").ColumnWidth = 10
        
    '   Populate column with dates
        rw = 2
        For d = minDate To maxDate Step 7
            npWS.Cells(rw, "A") = d
            rw = rw + 1
        Next d
        
    '   Populate columns with data
        cl = 1
        rw = 2
        Do Until srcWS.Cells(rw, "D") = ""
    '       Skip title row and increment row counter
            If Trim(srcWS.Cells(rw, "C")) = "Employee Name" Then
                rw = rw + 1
            Else
    '           Capture name and increment column counter, and populate name in header
                If (Trim(srcWS.Cells(rw, "C")) <> "") Then
                    cl = cl + 1
                    nm = srcWS.Cells(rw, "C")
                    npWS.Cells(1, cl) = nm
                    npWS.Cells(1, cl).EntireColumn.AutoFit
                    npWS.Columns(cl).NumberFormat = "0.00"
                End If
    '           Capture other values
                dte = srcWS.Cells(rw, "D")
                amt = srcWS.Cells(rw, "E")
    '           Populate on net pay sheet
                On Error GoTo err_chk
                fRow = npWS.Columns("A:A").Find(What:=dte, After:=npWS.Range("A1"), LookIn:=xlFormulas _
                    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Row
                On Error GoTo 0
                npWS.Cells(fRow, cl) = amt
    '           Increment row counter
                rw = rw + 1
            End If
    '   Move to next row
        Loop
        
        Application.ScreenUpdating = True
        
        MsgBox "Process complete"
        
        Exit Sub
        
    
    '   Error handling if cannot find date
    err_chk:
        MsgBox "Date of " & dte & " does not seem to be a valid pay date", vbOKOnly, "ERROR!"
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •