vba date partial text string look up

[tonyotter]

I Have been away from using excel / vba for quite some time, and would really appreciate some assistance from my old friends at the Mr Excel community.

I am looking to run a vba code to output some data from a large spreadsheet based on date, sitename, and a textstring lookup.

The basics are: from the source sheet I need to summarise the sum of the values in column W "post duration" based on all entries by date, per site (based on the last 4 characters of the text string in column R "post name").
And finally to sum any entries (By date) that have the term "SUB" (Including any variation of the term. e.g. "SUBS") in column B "Employee ID"

Source data is as below:

Book1
	A	B	C	D
3	A	B	R	W
4	Date	Employee ID	Post's name	Post's duration
5	05/08/2018	SUB6	SO DBT1	12
6	05/08/2018	SUB1	SO EDI4	12
7	05/08/2018	SUB2	SO EDI4	12
8	05/08/2018	SUB3	SO EDI4	12
9	05/08/2018	SUB4	SO EDI4	12
10	05/08/2018	28223	SO GLA1	12
11	06/08/2018	28458	SO DBT1	12
12	06/08/2018	SUB6	SO DBT1	12
13	06/08/2018	SUB1	SO EDI4	10.5
Sheet2

Output data I am looking achieve:

Book1
	A	B	C	D
1	Output
2	Date	Site	Hours	Sub hours
3	Multiple occasions of date (Column A)	Based on cell values (Text string) last 4 characters end with site code	Sum of column W based on Date and site	Hour where text in column employee ID include the text "SUB"
4
5	Example output
6	01/02/2019	EDI4	225.5	48
Sheet1

Any assistance gratefully received

 

[Peter_SSs]

I can't see any relationship between the sample source data and the output data.
- If there is a relationship can you spell it out some more?
- If there isn't a relationship, can you provide some sample data and expected output that are related?

 

[tonyotter]

The below is some sample data of a single site (Although the data is normally a mix of numerous sites)

Book1
	A	B	C	D
1	Date	Employee ID	Post's name	Post's duration
2	01/08/2018	21662	SO EDI4	12
3	02/08/2018	21662	SO EDI4	12
4	07/08/2018	21662	SO EDI4	12
5	08/08/2018	21662	SO EDI4	12
6	09/08/2018	21662	SO EDI4	12
7	01/08/2018	21666	SO EDI4	10
8	02/08/2018	21666	SO EDI4	10
9	03/08/2018	21666	SO EDI4	10
10	06/08/2018	21666	SO EDI4	4.5
11	08/08/2018	21666	SO EDI4	10
12	09/08/2018	21666	SO EDI4	10
13	05/08/2018	SUB3	SO EDI4	12
14	05/08/2018	SUB4	SO EDI4	12
15	06/08/2018	SUB1	SO EDI4	10.5
16	06/08/2018	SUB2	SO EDI4	12
17	06/08/2018	SUB3	SO EDI4	12
18	06/08/2018	SUB4	SO EDI4	12
19	07/08/2018	SUB1	SO EDI4	12
20	07/08/2018	SUB2	SO EDI4	12
21	07/08/2018	SUB3	SO EDI4	12
22	07/08/2018	SUB4	SO EDI4	12
23	08/08/2018	SUB1	SO EDI4	12
24	08/08/2018	SUB2	SO EDI4	12
25	08/08/2018	SUB3	SO EDI4	12
26	08/08/2018	SUB4	SO EDI4	12
27	09/08/2018	SUB1	SO EDI4	12
28	09/08/2018	SUB2	SO EDI4	12
29	09/08/2018	SUB3	SO EDI4	12
30	09/08/2018	SUB4	SO EDI4	12
31	10/08/2018	SUB1	SO EDI4	12
32	10/08/2018	SUB2	SO EDI4	12
33	10/08/2018	SUB3	SO EDI4	12
34	10/08/2018	SUB4	SO EDI4	12
35	11/08/2018	SUB1	SO EDI4	12
36	11/08/2018	SUB2	SO EDI4	12
37	11/08/2018	SUB3	SO EDI4	12
38	11/08/2018	SUB4	SO EDI4	12
Sheet4

The required out puts based on the sample below would be

Book1
	A	B	C	D
1	Date	Site	Hours	Sub hours
2	01/08/2018	EDI4	22	0
3	02/08/2018	EDI4	22	0
4	03/08/2018	EDI4	10	0
5	05/08/2018	EDI4	24	24
6	06/08/2018	EDI4	51	46.5
7	07/08/2018	EDI4	60	48
8	08/08/2018	EDI4	70	48
9	09/08/2018	EDI4	70	48
10	10/08/2018	EDI4	48	48
11	11/08/2018	EDI4	48	48
Sheet3

Where the hours data refers to the sum of all numbers in column D "posts duration" which have EDI4 as part of the text string in the cell in column C "posts name" for the particular date. The number in column D "Sub hours" are made up of the data in column D "posts duration" which have EDI4 in the text string AND has SUB as part of the text string in column B "employee ID"

Hope this makes a little more sense.

Many thanks for the assistance

 

[Peter_SSs]

Thanks for the additional data.

This code assumes
- original data in Sheet4
- results go in Sheet3

Change the sheet names in the code if required and test in a copy of your workbook (the code removes any existing data from the results sheet).

Sub Calc_Hours()
  Dim a As Variant, b As Variant
  Dim dh As Object, dsh As Object
  Dim i As Long
  Dim s As String
  
  Set dh = CreateObject("Scripting.Dictionary")
  Set dsh = CreateObject("Scripting.Dictionary")
  With Sheets("Sheet4")
    a = .Range("A2", .Range("D" & .Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    s = a(i, 1) & " " & Right(a(i, 3), 4)
    dh(s) = dh(s) + a(i, 4)
    dsh(s) = dsh(s) + IIf(Left(a(i, 2), 3) = "SUB", a(i, 4), 0)
  Next i
  Application.ScreenUpdating = False
  With Sheets("Sheet3")
    .UsedRange.ClearContents
    With .Range("A2").Resize(dh.Count)
      .Value = Application.Transpose(dh.Keys)
      .TextToColumns DataType:=xlDelimited, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1))
      .Offset(, 2).Resize(, 2).Value = Application.Transpose(Array(dh.Items, dsh.Items))
    End With
    .Range("A1:D1").Value = Array("Date", "Site", "Hours", "Sub Hours")
    .UsedRange.Columns.AutoFit
    .Activate
  End With
  Application.ScreenUpdating = True
End Sub

 

[tonyotter]

Hi Peter.

Many thanks for this. The code works great with my test spreadsheet. However except that I have tried to change the source sheet ranges to pull the data for use in my original sheet without any success.

Whilst the columns in the sample data were columns A,B,C and D. The data I need to pull from in the actual source sheet are non consecutive columns - As in the original post - Date (Column A), Employee ID (Column B), post's name (Column R) posts duration (Column W).

Any further help very much appreciated.

 

[Peter_SSs]

Get the source data like this instead. Array(1, 2, 18, 23) represents column A, B, R & W

With Sheets("Sheet4")
  <del>a = .Range("A2", .Range("D" & .Rows.Count).End(xlUp)).Value</del>
  a = Application.Index(.Cells, Evaluate("row(2:" & .Range("A" & .Rows.Count).End(xlUp).Row & ")"), Array(1, 2, 18, 23))
End With

 

[tonyotter]

Hi Peter,

Apologies for the delay in responding. That now works perfectly. Thank you. I have been away from excel and vba for a while, so I will spend a bit of time breaking down the vba for my own understanding.

Thanks again.

 

[Peter_SSs]

Hi Peter,

Apologies for the delay in responding. That now works perfectly. Thank you. I have been away from excel and vba for a while, so I will spend a bit of time breaking down the vba for my own understanding.

Thanks again.

You're welcome. Thanks for letting us know.