Hi Team

I have been given a monumental task to change a file from its existing format to more of a Transposed version which will literally take hours, if not days to do manually so here I am again asking for help.

Existing format: ( Sheet 1 )

There are 2 sets of data in each Weekly Group: ( Monday to Saturday ).

Group .1
Set .1 = Column A = Unit No, then 6 groups of ( 5 columns x 14 rows ) - starting @ B9

e.g

Monday = B9:F22... Tuesday = G9:K22 etc to Saturday = AA9:AE22

A B C D E F
7__Monday, 26 June 2017
8__Trucks___HrsTot HrsIdle HrsActive Loads Rev/Cost
9__C001_____15.25_______________15.25________6____ _____________________
10_C002___________________________________________ _____________________
11_C003______9.50________________9.50________3____ _____________________
12_C004______8.25________________8.25________3____ _____________________
13_C005_____18.00______3.50_____14.50________5____ _____________________
14_C006_____10.75_______________10.75________3____ _____________________
15_C007_____10.75_______________10.75________3____ _____________________
16_C008______9.25________________9.25________3____ _____________________
17_C009______8.00______1.00______7.00________2____ _____________________
18_C010_____10.00_______________10.00________3____ _____________________
19_C011_____10.00_______________10.00________3____ _____________________
20_C012______9.75________________9.75________2____ _____________________
21_C013______9.00________________9.00________3____ _____________________
22_C014___________________________________________ _____________________

Set .2 = Column A = Unit No, then 6 groups of ( 5 columns x 20 rows ) €“ starting @ B27

27_S001_____10.00_______________10.00________3____ _________
28_S002______9.75________________9.75________2____ _________
29_S003______9.00________________9.00________3____ _________
30_S004___________________________________________ _________
Etc......

Each set is recursive in that for each Weekly Group there is the same format for data entry.

The next weeks data:

Group .2
Set .1 = 5 columns x 14 rows - starting @ B55
Set .2 = 5 columns x 20 rows - starting @ B73

And the spacing for each consecutive sets of data are exactly the same 46 rows.

Required Format: ( Sheet 2 )

_____A________B________C_______D_________E________ _F__________G_______________
1___Date_____Unit____HrsTot__HrsIdle__HrsActive___ Loads____Rev/Cost___________
2__26/6/17___C001____15.25_____0_______15.25_______6______ ___0.00_____________
3__26/6/17___C002_____0.00_____0________0.00_______0______ ___0.00_____________
4__Etc.....

As always, much appreciation in advance
Kind regards
Mark.