Madam_Xavier
New Member
- Joined
- Aug 26, 2024
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
Hi everyone,
I appreciate everyone who takes a moment to read over the problem I'm trying to solve. The image below is a list of 10 records that equal a total of 4 sets of "Washes".
Ex.02: Red Box
On the 13th, Indiana submitted two records (5hrs and 3hrs) totaling 8hrs. So their dispatcher created a wash by creating only one record with -8.
Ex.01: Blue Box -
On the 15th, Indi submitted 4 hours worked for jobsite 68. However, it was corrected by his dispatcher and they created a Wash by duplicating the record and creating the hours worked to be a negative value.
Problem:
Humans have to manually identify washes and remove from "Master" Sheet. We currently have to highlight all the negatives, then compare the nearby rows to see if Columns A, B, C, and D are identical. Then we have to compare the negative row with the surrounding rows to see which records are being washed out.
Current Solution:
Every week, we receive a text file of all the labor records that need to be imported into our system. Use Text to Column to delimit the data with the Vertical Line (|) and then we use either conditional formatting where we input ( =0>$G1) OR Filter on G to find the negatives and then highlight the column (depending on the Analyst). Which then gives us a starting point to simply eyeball it and verify the Washes' by periodically checking the Sum in the bottom right-hand corner to see if it equals zero.
Desired Solution:
Preferably, I'd love to create a Macro or use Power Automate to consistently and accurately find all the potential washes, leaving the rest of the records alone. Then remove these washes from the "Master" sheet and move them to the "Washes" sheet.
Unfortunately, I'm the most experienced on the team when it comes to Excel, but the last time I needed to go this in depth was back in 2016, when I desperately learned about VBA and how to code a solution within a week.
This amazing community helped me back then in identifying why my code kept failing.
So once again, I turn on the MrExcel bat-signal
in hopes of receiving help.
Thank you for coming to my TED Talk.
Madam Xavier
I appreciate everyone who takes a moment to read over the problem I'm trying to solve. The image below is a list of 10 records that equal a total of 4 sets of "Washes".
Ex.02: Red Box
On the 13th, Indiana submitted two records (5hrs and 3hrs) totaling 8hrs. So their dispatcher created a wash by creating only one record with -8.
Ex.01: Blue Box -
On the 15th, Indi submitted 4 hours worked for jobsite 68. However, it was corrected by his dispatcher and they created a Wash by duplicating the record and creating the hours worked to be a negative value.
Problem:
Humans have to manually identify washes and remove from "Master" Sheet. We currently have to highlight all the negatives, then compare the nearby rows to see if Columns A, B, C, and D are identical. Then we have to compare the negative row with the surrounding rows to see which records are being washed out.
Current Solution:
Every week, we receive a text file of all the labor records that need to be imported into our system. Use Text to Column to delimit the data with the Vertical Line (|) and then we use either conditional formatting where we input ( =0>$G1) OR Filter on G to find the negatives and then highlight the column (depending on the Analyst). Which then gives us a starting point to simply eyeball it and verify the Washes' by periodically checking the Sum in the bottom right-hand corner to see if it equals zero.
Desired Solution:
Preferably, I'd love to create a Macro or use Power Automate to consistently and accurately find all the potential washes, leaving the rest of the records alone. Then remove these washes from the "Master" sheet and move them to the "Washes" sheet.
Unfortunately, I'm the most experienced on the team when it comes to Excel, but the last time I needed to go this in depth was back in 2016, when I desperately learned about VBA and how to code a solution within a week.
This amazing community helped me back then in identifying why my code kept failing. So once again, I turn on the MrExcel bat-signal
in hopes of receiving help. Thank you for coming to my TED Talk.
Madam Xavier
