Find text in one cell, select all cells for same person on same date and copy to another sheet

Moonriser

New Member
Joined
Feb 8, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi All,
Just had to re-register after 8 years away. I receive a weekly timesheet spreadsheet for a construction project in an area with high rainfall. Each person can charge to 3 or 4 different time codes per day. One of the codes is "inclement weather/rain". When this code appears I need to "find" the other codes that person charged to on the same day and manually determine which "work code" was impacted by the rain. What I need in code or advanced filters is an ability to select the other "work code" that has the highest "value". So in the example below an employee charges 2 hours to rain, 0.5 hours to another work code and 9 hours to another work code, I need to select the code with 9 hours and say that this was the work impacted by rain. I've written vba that finds the rain code and copies the cell below and above but I'd like it to find the cell with the same employee name, same date and largest hours and just copy that row to another sheet - any suggestions would be great - here's the rough vba I have
For i = 1 To LastRow
With Worksheets("EMPLOYEE HOURS at 2022_02_06")
'This is working then altered to pick up CELL BELOW AND ABOVE and Copy
'because 1 of those is the cell with the work that is impacted

If .Cells(i, 10).Value = "Inclement Weather" Then
.Rows(i).Copy Destination:=Worksheets("TEST").Range("A" & j)

'.Rows(i + 1).Copy Destination:=Worksheets("TEST").Range("A" & j) 'copy row below
'.Rows(i - 1).Copy Destination:=Worksheets("TEST").Range("A" & j) 'copy row above
j = j + 1
End If
End With
Next i
HoursDateDay Rule CodeFirstNameLast NameFull NameClaim Class NameWorkOrderCodeWorkOrderName
231/01/2022ORDCharlieCanhamCharlie CanhamCrane Driver / OperatorZJ20002.0002Mech Inclement Weather
0.531/01/2022ORDCharlieCanhamCharlie CanhamCrane Driver / OperatorZJ20002.0014Primary Cable ladder Install RL42.5
931/01/2022ORDCharlieCanhamCharlie CanhamCrane Driver / OperatorP5070.S.L1.1.P1.MP5070.S.L1.1 - Pancake 1
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top