Hello,
I need to transfer data from one worksheet to another. I would be able to do it in a non-VBA way, but it results in some manual work still remaining. And VBA is sadly above my competence at the moment. Thanks for any help in advance!
First sheet is an automatically generated daily workers' sign in/sign out sheet (this will be our data source sheet):
The other one is a weekly sheet that is used for accounting later on (this will be data destination sheet):
Each operative needs to be extracted along with his trade and working times for each day of the week (matching colors show source/destination). The amount of workers vary week to week. Naturally, extra rows for each worker need to be created in the destination worksheet. 'NAME' column in the destinaton sheet = conjoined 'First name' and 'Last name' columns from the source sheet.
I know merged cells shouldn't be used, but these are pre-set forms. Let me know if it's not doable with merged cells.
Bonus: workers in the destination worksheet need to be arranged alphabetically by TRADE first and by NAMES second, e.g. all the 'fixers' need to be in the same cluster, but sorted alphabetically among themselves, then followed by alphabetically sorted 'labours' and 'supervisors'.
I need to transfer data from one worksheet to another. I would be able to do it in a non-VBA way, but it results in some manual work still remaining. And VBA is sadly above my competence at the moment. Thanks for any help in advance!
First sheet is an automatically generated daily workers' sign in/sign out sheet (this will be our data source sheet):
| defaultest.xlsx | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | U | V | W | X | Y | Z | ||||
| 10 | Mar, 20 | Mar, 22 | Mar, 23 | Mar, 24 | Mar, 25 | Mar, 26 | |||||||||||||||||||||
| 11 | Saturday | Monday | Tuesday | Wednesday | Thursday | Friday | |||||||||||||||||||||
| 12 | Contractor | First Name | Last Name | Employee ID | Trade | Individual Type | Project Name | Project Ref | Week Beginning | Normal | OT1 | Normal | OT1 | Normal | OT1 | Normal | OT1 | Normal | OT1 | Normal | OT1 | ||||||
| 13 | John | Wade | Labourer | 20/03/2021 | 6.62 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
| 14 | Rick | Holmes | Fixer | 20/03/2021 | 6.58 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
| 15 | Robert | Hicks | Fixer | 20/03/2021 | 9.00 | 0.00 | 9.00 | 0.00 | 8.98 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||||
| 16 | Jeff | Pendo | Fixer | 20/03/2021 | 6.63 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
| 17 | Nick | Walsh | Fixer | 20/03/2021 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||||
| 18 | Charlie | Jefferson | Fixer | 20/03/2021 | 6.63 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
| 19 | Chris | Waller | Fixer | 20/03/2021 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||||
| 20 | Tom | Henderson | Fixer | 20/03/2021 | 6.57 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
| 21 | Colin | Jones | Labourer | 20/03/2021 | 6.53 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
| 22 | Bob | Willson | Labourer | 20/03/2021 | 6.53 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
| 23 | Ian | Lee | Labourer | 20/03/2021 | 6.58 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
| 24 | Paul | Kirk | Supervisor | 20/03/2021 | 6.67 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||||||||
| 25 | Zac | Downey | Supervisor | 20/03/2021 | 6.15 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | 9.00 | 0.00 | |||||||||||
Daily Sheet | |||||||||||||||||||||||||||
The other one is a weekly sheet that is used for accounting later on (this will be data destination sheet):
| defaultest.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 19 | ID CODE | TRADE | NAME | QUANT (hours) | TOTAL | RATE | TOTAL | ||||||||||
| 20 | S | S | M | T | W | T | F | ||||||||||
| 22 | |||||||||||||||||
| 23 | SHEET TOTAL | Total hrs | |||||||||||||||
Weekly Sheet | |||||||||||||||||
Each operative needs to be extracted along with his trade and working times for each day of the week (matching colors show source/destination). The amount of workers vary week to week. Naturally, extra rows for each worker need to be created in the destination worksheet. 'NAME' column in the destinaton sheet = conjoined 'First name' and 'Last name' columns from the source sheet.
I know merged cells shouldn't be used, but these are pre-set forms. Let me know if it's not doable with merged cells.
Bonus: workers in the destination worksheet need to be arranged alphabetically by TRADE first and by NAMES second, e.g. all the 'fixers' need to be in the same cluster, but sorted alphabetically among themselves, then followed by alphabetically sorted 'labours' and 'supervisors'.
