Linking workbook data, but searching to find the data? Google Sheets

[TAPS_MikeDion]

Hey all,

I'm assuming there's a way to do this, but I'm not sure how to go about it. Any help would be greatly appreciated. My guess is that this would be a VBA project and I'm fine with that.

I have two (2) workbooks. One named "Missed Work" and the other "Payroll Totals."

The columns in the workbooks are as follows:
"Missed Work"
A = Last Name of the employee who missed work
B = First Name of the employee who missed work
E = Location the work was missed. If the word COVID is in the cell, then it was a COVID job, otherwise it was a Regular job
F = # of hours covered
G = Last Name of the employee who covered the work
H = First Name of the employee who covered the work

"Payroll Totals"
B = Last Names of the employees
C = First Names of the employees
I = COVID Cover Hours
J = Regular Cover Hours

What I'm trying to do is have the Payroll Totals auto-fill columns I and/or J with the number of hours from the Missed Work sheet that were covered by another employee.


For example:
Data in the Missed Work sheet

A​	B​	E​	F​	G​	H​
LAST NAME​	FIRST NAME​	LOCATION​	HOURS​	COVERED BY (LAST NAME)​	COVERED BY (FIRST NAME)​
Doe	Jane	Location A - COVID	5.00	Smith	Jim
Rivera	Carlos	Location D	3.00	Jones	Mary

Data in the Payroll Totals sheet:

B​	C​	I​	J​
LAST NAME​	FIRST NAME​	COVID COVER HOURS​	REGULAR COVER HOURS​
Barnes	Cindy
Jones	Mary		3.00
Mora	Pablo
Smith	Jim	5.00

Please and thank you to anyone able to assist on this!
-Mike

 

[tonyyy]

You might consider the following...

Sub PayrollTotals()
Dim wb1 As Workbook, wb2 As Workbook
Dim arr1 As Variant, arr2 As Variant
Dim i As Long, j As Long, k As Long

Set wb1 = Workbooks("Payroll Totals.xlsm")
Set wb2 = Workbooks("Missed Work.xlsm")
arr1 = wb1.Sheets("Payroll Totals").UsedRange
arr2 = wb2.Sheets("Missed Work").UsedRange

For i = 2 To UBound(arr2)
    For j = 2 To UBound(arr1)
        If arr2(i, 7) & arr2(i, 8) = arr1(j, 2) & arr1(j, 3) Then
            If InStr(1, arr2(i, 5), "COVID", vbTextCompare) Then
                arr1(j, 9) = arr2(i, 6)
            Else
                arr1(j, 10) = arr2(i, 6)
            End If
        End If
    Next j
Next i
wb1.Sheets("Payroll Totals").UsedRange = arr1
End Sub

Please note that both workbooks need to be open when running the code.

Cheers,

Tony

p.s. Just noted you're on a MacOS... so the above code may or may not work as it's written for a pc.

 

[TAPS_MikeDion]

Hi Tony,

Ugh. I never tried using VBA in Google Sheets and now I see that it isn't an option so I can't use your code. With what I'm doing I have to use Google Sheets, not Excel. Again, I didn't know you couldn't use VBA in Google Sheets or I wouldn't even have posted the question. I'm sorry you did all that work for nothing.

Thank you very much for trying to help though.

 

[Marc L]

(removed)

 

[tonyyy]

Hi Tony,

Ugh. I never tried using VBA in Google Sheets and now I see that it isn't an option so I can't use your code. With what I'm doing I have to use Google Sheets, not Excel. Again, I didn't know you couldn't use VBA in Google Sheets or I wouldn't even have posted the question. I'm sorry you did all that work for nothing.

Thank you very much for trying to help though.

" I'm sorry you did all that work for nothing." No worries, Mike. Sorry it didn't work out. Good luck finding a solution.

 

[TAPS_MikeDion]

Thanks man, I appreciate it.

 

[TAPS_MikeDion]

I'm probably making this sound far easier than it may be since I am not that well versed with formulas, but is a formula possible here?

If I were to use the IMPORTRANGE formula in the Payroll Totals sheet to copy the data from columns E - H of the Missed Work sheet, would a formula be able to make the name and location comparisons, and if a match is found, place the data into the matching cells of the Payroll Totals sheet?

Thank you.