Hi Mark,

Am Thu, 23 Jan 2020 22:04:00 -0800 (PST) schrieb noodnutt@gmail.com:

I gives me this:

Date State Agency Code Agency Name Employee Name Employee Code Cost Centre Dept Grade Shift Total Hours $_Cost
Terri-anne 36.00 1519.95
Kerrieanne 16.00 662.64
Mary 7.50 303.45
Natalija 15.00 611.25
Marilyn 13.50 564.90

I would prefer to capture the data for all columns.

I would like this please:

Date State Agency Code Agency Name Employee Name Employee Code Cost Centre Dept Grade Shift Total Hours $_Cost
5/01/2020 NSW TA00015 Agency 1 Terri-anne 100000 T-28 Admin ADLV1 D 36.00 1519.95
5/01/2020 VIC TA00027 Agency Kerrieanne 100002 T-32 Admin ADLV1 D 16.00 662.64
5/01/2020 VIC TA00012 Agency 3 Mary 100003 T-06 Admin ADLV2 D 7.50 303.45
5/01/2020 VIC TA00027 Agency 4 Natalija 100004 T-32 Admin ADLV1 D 15.00 611.25
5/01/2020 SA TA00017 Agency 5 Marilyn 100005 T-58 Admin ADLV1 D 13.50 564.90

try:

Sub Test()
Dim sWS As Worksheet, tWS As Worksheet
Dim vData, vaData, varOut As Variant
Dim sHeader As String
Dim i As Integer, myCol As Integer
Dim LRow As Long, LCol As Long
Dim rngNames, rngHours, rngCost As Range

Set sWS = Sheets("Import 1")
Set tWS = Sheets("Import 2")

With sWS
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set rngNames = .Range("E2:E" & LRow)
Set rngHours = .Range("K2:K" & LRow)
Set rngCost = .Range("L2:L" & LRow)
vData = rngNames
varOut = .Range(.Cells(2, 1), .Cells(LRow, LCol))
End With

sHeader = "Date,State,Agency Code,Agency Name,Employee Name,Employee
Code,Cost Centre,Dept,Grade,Shift,Total Hours,$_Cost"
vaData = Split(sHeader, ",")

With tWS
.Range("A1").Resize(, UBound(vaData) + 1) = vaData
With .Cells(2, 11).Resize(LRow - 1)
.Formula = "=Sumif('" & sWS.Name & "'!" & rngNames.Address & ",'" & sWS.Name & "'!" & rngNames(1, 1).Address(0, 0) & ",'" & sWS.Name & "'!" & rngHours.Address & ")"
.Value = .Value
End With
With .Cells(2, 12).Resize(LRow - 1)
.Formula = "=Sumif('" & sWS.Name & "'!" & rngNames.Address & ",'" & sWS.Name & "'!" & rngNames(1, 1).Address(0, 0) & ",'" & sWS.Name & "'!" & rngCost.Address & ")"
.Value = .Value
End With
For i = 1 To 10
myCol = Application.Match(.Cells(1, i), sWS.Range("1:1"), 0)
.Cells(2, i).Resize(LRow - 1) = Application.Index(varOut, 0, myCol)
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Claus

Many thanks for your tireless efforts in making my life that much easier, I appreciate it immensely.

In the For statement, you had 1 - 10, when I think it was supposed to be 1 to 1 to 12 given there is 12 cols.

With that said, the code did halt on the myCol= line citing Type Mismatch.

2 other issues Claus:

First: the minor being no data copied across from Cols: 7 to 10.
Lastly: It is actually copying each row and summing each line as the total sum. eg.

Date State Agency Code Agency Name Employee Name Employee Code Cost Centre Dept Grade Shift Total Hours $_Cost
1/05/2020 NSW TA00015 Agency 1 Terri-anne 100000 36.00 1519.95
1/05/2020 NSW TA00015 Agency 1 Terri-anne 100000 36.00 1519.95
1/05/2020 NSW TA00015 Agency 1 Terri-anne 100000 36.00 1519.95
1/05/2020 NSW TA00015 Agency 1 Terri-anne 100000 36.00 1519.95
1/05/2020 VIC TA00027 Agency 2 Kerrieanne 100001 16.00 662.64
1/05/2020 VIC TA00027 Agency 2 Kerrieanne 100001 16.00 662.64
1/05/2020 VIC TA00012 Agency 3 Mary 100002 7.50 303.45
1/05/2020 VIC TA00027 Agency 4 Natalija 100003 15.00 611.25
1/05/2020 VIC TA00027 Agency 4 Natalija 100003 15.00 611.25
1/05/2020 SA TA00017 Agency 5 Marilyn 100004 13.50 564.904
1/05/2020 SA TA00017 Agency 5 Marilyn 100004 13.50 564.904

I am hoping to get the following as the result:

Date State Agency Code Agency Name Employee Name Employee Code Cost Centre Dept Grade Shift Total Hours $_Cost
1/05/2020 NSW TA00015 Agency 1 Terri-anne 100000 36.00 1519.95
1/05/2020 VIC TA00027 Agency 2 Kerrieanne 100001 16.00 662.64
1/05/2020 VIC TA00012 Agency 3 Mary 100002 7.50 303.45
1/05/2020 VIC TA00027 Agency 4 Natalija 100003 15.00 611.25
1/05/2020 SA TA00017 Agency 5 Marilyn 100004 13.50 564.904

As always

Many thanks Claus.

I am working from home for the next 3 days so I am more than happy to give you a call and chat.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Mark,

Am Mon, 27 Jan 2020 14:44:00 -0800 (PST) schrieb noodnutt@gmail.com:

2 other issues Claus:

First: the minor being no data copied across from Cols: 7 to 10.
Lastly: It is actually copying each row and summing each line as the total sum. eg.

why don't you use a Pivot table to calculate the totals for each
employee?
Download the workbook from here: https://1drv.ms/x/s!AqMiGBK2qniTgel2td3hxiwPpTHfhg?e=e3cctA

There's a Pivot table and a macro to do what you want.


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Claus

Even though I am working from home, I am logged into works servers via a Citric Connection.

Their ( Understandably ) anul approach to redirects is non-negotiable, so I can't access this link, sadly. That being said, I can open attachments such as excel files ( if you're able to email it to me via the usual address).

I can only guess they feel this is less of a threat given nobody in our organisation now! opens unsolicited attachments.

Especially given after one of the interns a while back opened an attachment on a legitimate looking email from AustPost and we got hit with a ransom worm, which we recovered from easily.

Thanks again
Sincerely
Mark.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Claus

I thought maybe I can use CountA to find the first instance of a name in the range( Column E ) and if ("E" & i) => 1 then .offset(,-4).resize(,9).copy only the first instance of each value to "Import 2".

From "Import 2" I can then use the sumif() for each value I need for columns (K & L ).

I have been trawling thru so many google searches to find something that comes close to what I think I need to make it work, but as yet, nada! hence the reason I am back here... :( bothering you.

Cheers
Mark.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)