Listed Employee data

[Nygie]

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

 

[Joe4]

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.

 

[Nygie]

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.

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

 

[Joe4]

Based on the sample data you posted, can you post what you would like your expected result to look like?

 

[Nygie]

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

 

[Joe4]

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?

 

[Nygie]

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

 

[Joe4]

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?

 

[Nygie]

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

 

[Joe4]

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:

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