To give my care team a breakdown of the hours they have worked during a pay period, I have created this bit of VBA code which I use to process the .CSV file that Care4IT generates. I accept that this might be a bit beyond beginner Excel users, but for those with a bit of confidence/experience, this code should work for you if pasted into the VBA console for the spreadsheet created by C4IT.

The labels we use may not align with those you use, so may need a bit of a tweak. Mail me if you are interested and need some help.....

The resulting output is as follows: (There have been some anonymity changes...)

Jane Doe Hours Rate Total
W/End Care Visits 30.5 £x.xx £xxx.xx
W/Day Care Visits 124.42 £x.xx £xxx.xx
B/H Care Visits 8 £xx.xx £xx.xx
Training 1 £x.xx £x.xx
W/D Shadow visit 3.75 £x.xx £xx.xx



John Doe Hours Rate Total
W/Day Care Visits 33.5 £x.xx £xxx.xx
W/End Care Visits 9.5 £x.xx £xx.xx
Training 1 £x.xx £x.xx



Simon Curtis Hours Rate Total
Training 9 £x.xx £xx.xx
W/D Shadow visit 25.25 £x.xx £xxx.xx
B/H Shadow visit 2 £x.xx £xx.xx
W/Day Care Visits 6.75 £x.xx £x.xx
W/End Care Visits 7 £x.xx £xx.xx

Code as follows:

'-----------------------------------------------
' Function GetVisitType() translates the
' type of visit generated by
' care for it, into the description we use.
'---------------------------------------------
Private Function GetVisitType$(ByVal exportLabel As String)

If InStr(exportLabel, "34") > 0 Then
GetVisitType$ = "Training"

ElseIf InStr(exportLabel, "36") > 0 Then

If InStr(exportLabel, "Weekday") > 0 Then
GetVisitType$ = "W/D Shadow visit"

ElseIf InStr(exportLabel, "Bank") > 0 Then
GetVisitType$ = "B/H Shadow visit"

Else
GetVisitType$ = "W/E Shadow visit"

End If

ElseIf InStr(exportLabel, "Bank Holidays") > 0 Then
GetVisitType$ = "B/H Care Visits"

ElseIf InStr(exportLabel, "Weekend") > 0 Then
GetVisitType$ = "W/End Care Visits"

ElseIf InStr(exportLabel, "Weekday") > 0 Then
GetVisitType$ = "W/Day Care Visits"

Else
GetVisitType$ = "Func: GetVisitType() Label not found" & "- " & exportLabel
End If

End Function

Sub Sample()
Dim ws As Worksheet
Dim lRow As Long
Dim lCol As Long
'Dim rng As Range
Dim rCntr As Integer, cCntr As Integer
Dim myStr As String
Dim sRowCntr As Long, sColCntr As Long

Application.ScreenUpdating = False
sColCntr = 0

'~~> Set this to the relevant worksheet
Set ws = [Sheet1]
ws.Rows(1).EntireRow.Delete

'~~> Get the last row and last column
lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

sRowCntr = lRow + 2

For rCntr = 2 To lRow ' Step through each row of the wages export....

ws.Cells(rCntr + sRowCntr, 1) = ws.Cells(rCntr, 1) ' write given name
ws.Cells(rCntr + sRowCntr, 2) = ws.Cells(rCntr, 2) ' write family name
ws.Cells(rCntr + sRowCntr, 3) = "Hours"
ws.Cells(rCntr + sRowCntr, 4) = "Rate"
ws.Cells(rCntr + sRowCntr, 5) = "Total"


For cCntr = 6 To lCol - 2 ' Now step through each column....

If ws.Cells(rCntr, cCntr).Value = "0" Then
Else
'MsgBox ("got value " & ws.Cells(rCntr, cCntr))

' get & write visit label
ws.Cells(rCntr + sRowCntr + 1, 2) = GetVisitType(ws.Cells(1, cCntr))

' hours worked
ws.Cells(rCntr + sRowCntr + 1, 3) = ws.Cells(rCntr, cCntr)

' pay rate
ws.Cells(rCntr + sRowCntr + 1, 4) = ws.Cells(rCntr, cCntr + 1)
ws.Cells(rCntr + sRowCntr + 1, 4).NumberFormat = "£#,##0.00"

' clunky formula
ws.Cells(rCntr + sRowCntr + 1, 5).Formula = "=" & ws.Cells(rCntr + sRowCntr + 1, 3) _
& "*" & ws.Cells(rCntr + sRowCntr + 1, 4)
ws.Cells(rCntr + sRowCntr + 1, 5).NumberFormat = "£#,##0.00"

sRowCntr = sRowCntr + 1
cCntr = cCntr + 1

End If

Next cCntr
'MsgBox ("done carer -" & ws.Cells(rCntr, 1) & " " & ws.Cells(rCntr, 2))
sRowCntr = sRowCntr + 3

Next rCntr

ws.Columns("B").ColumnWidth = 14

Application.ScreenUpdating = True
MsgBox ("done processing")


End Sub

I would be interested in your coding.