Find and transpose ranges as blocks.... I think?

[picklefactory]

Hi folks
Trying to help our finance lady out with simplifying pay calcs. We have our company ERP systems collecting employee clockings but the format it outputs is not very amenable for manipulating the data, at least not for my meagre talents. The sample below shows the format of the report that's exported from the ERP, you can see that the number of entry rows can vary by employee, and some will include absences/holiday where applicable. I can't change the format of the export.
What I'm trying to do is grab each 'block' of data for each employee from the columnar format and transpose them so I have horizontal 'blocks' of data. In my head I then have short columns of data for each employee that I can add formulae to underneath and jiggle about with the data as I wish, I'm struggling to do that now as each employee block of data can vary and I'm not smart enough to find/index and manipulate where the position of entries is constantly varying. What I'd like to achieve is shown in the 2nd sample but I can't see a way to define each block, and then move it to a dynamically adjacent position to previous blocks.
Am I barking up the wrong tree, or is that doable?
Thanks

CLOCK ON OFF2.xls
	A	B	C	D	E	F
153	0100	A NOTHER
154	02/03/2020 09:15:06	02/03/2020 16:05:47	6.83
155	03/03/2020 06:28:08	03/03/2020 16:02:58	9.57
156	04/03/2020 06:29:53	04/03/2020 16:05:42	9.60
157	05/03/2020 06:27:53	05/03/2020 16:04:08	9.62
158	06/03/2020 06:30:15	06/03/2020 15:33:58	9.05
159	44.67
160	Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S
161
162
163
164
165	0101	B NOTHER
166	02/03/2020 10:16:48	02/03/2020 16:03:48	5.78
167	03/03/2020 06:55:10	03/03/2020 16:03:20	9.13
168	04/03/2020 07:00:17	04/03/2020 16:03:03	9.05
169	05/03/2020 07:00:28	05/03/2020 16:02:41	9.03
170	06/03/2020 06:58:46	06/03/2020 15:32:11	8.57
171	41.57
172	Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S
173
174
175
176
177	1002	C NOTHER
178	04/03/2020 06:56:29	04/03/2020 16:02:09	9.10
179	05/03/2020 06:56:24	05/03/2020 16:03:20	9.12
180	06/03/2020 06:56:14	06/03/2020 15:32:45	8.60
181	26.82
182	Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S
183	1002
184	1002	03/03/2020	F	070000	163000	H
185
186
Sheet1

CLOCK ON OFF2.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
153	0100	A NOTHER						0101	B NOTHER						1002	C NOTHER
154	02/03/2020 09:15:06	02/03/2020 16:05:47	6.83					02/03/2020 10:16:48	02/03/2020 16:03:48	5.78					04/03/2020 06:56:29	04/03/2020 16:02:09	9.10
155	03/03/2020 06:28:08	03/03/2020 16:02:58	9.57					03/03/2020 06:55:10	03/03/2020 16:03:20	9.13					05/03/2020 06:56:24	05/03/2020 16:03:20	9.12
156	04/03/2020 06:29:53	04/03/2020 16:05:42	9.60					04/03/2020 07:00:17	04/03/2020 16:03:03	9.05					06/03/2020 06:56:14	06/03/2020 15:32:45	8.60
157	05/03/2020 06:27:53	05/03/2020 16:04:08	9.62					05/03/2020 07:00:28	05/03/2020 16:02:41	9.03					26.82
158	06/03/2020 06:30:15	06/03/2020 15:33:58	9.05					06/03/2020 06:58:46	06/03/2020 15:32:11	8.57					Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S
159	44.67							41.57							1002
160	Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S		Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S		1002	03/03/2020	F	070000	163000	H
Sheet1

 

[picklefactory]

Me again.
Please belay all that guff above.... I have since discovered that I can indeed modify the output format from the ERP to something much easier to work with, so I am sorted via another route. Please don't waste any time on this.... if anyone was!!!
Thanks folks

 

[mumps]

I just got done with macro so you could give it a try. The macro assumes your data starts in row 1.

Sub TransposeRanges()
    Application.ScreenUpdating = False
    Dim fRow As Long, lastRow As Long, i As Long, x As Long: x = 8
    With Range("A:A").SpecialCells(xlCellTypeConstants).Areas
        fRow = .Item(1).Cells.Count
        With .Item(.Count).Cells
            lastRow = .Item(.Cells.Count).Row
        End With
        For i = 2 To .Count
            .Item(i).Resize(, 6).Copy Cells(1, x)
            x = x + 7
        Next i
    End With
    Rows(fRow + 1 & ":" & lastRow).Delete
    Application.ScreenUpdating = True
End Sub

 

[picklefactory]

Thanks mumps.... in hindsight I should leave this open as I might still find an easier method via Excel.
I'll get back to you in a minute when I've tried that

 

[picklefactory]

Genius..... that was exactly what I was utterly stumped with. Always a mystery to me how you guys seem to do this so easily. That's perfect thank you mumps.

 

[mumps]

You are very welcome.

 

[picklefactory]

Bugger!
I'm back again. Only just found time to get back onto this and I'm afraid that code doesn't work quite as well as I first thought, although it gets close, but I'm missing some rows. If I'm not mistaken, that is transposing a fixed size range at a time? What I get when it runs is, it finds the first line for each employee, perfect, but then it is selecting a fixed 7 rows and transposing. The problem is... the ranges it needs to transpose are NOT a consistent size and they vary from employee to employee. I'm thinking I maybe didn't post the best example, I hope this one is clearer for what I need to see. The top section is what I'd like to see, the lower section is what mump's code is actually giving me, as you can see, some of the ranges are cut off short of a few rows. Is that an easy fix?
Thanks again for any time spent on this.

PAY CALCS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	0008	A NOTHER						0011	B NOTHER						0100	C NOTHER						1010	D NOTHER						6666	E NOTHER
2	09/03/2020 05:23:44	09/03/2020 14:02:07	8.65					09/03/2020 06:54:13	09/03/2020 16:04:15	9.17					12/03/2020 06:29:06	12/03/2020 16:05:38	9.60					09/03/2020 05:49:02	09/03/2020 14:10:25	8.35					10/03/2020 08:21:47	10/03/2020 16:30:51	8.15
3	10/03/2020 05:22:27	10/03/2020 14:02:03	8.67					10/03/2020 06:52:35	10/03/2020 16:03:44	9.18					13/03/2020 06:27:22	13/03/2020 15:36:58	9.15					10/03/2020 05:50:30	10/03/2020 14:09:03	8.32					11/03/2020 08:24:57	11/03/2020 16:43:52	8.32
4	11/03/2020 05:28:18	11/03/2020 14:02:06	8.57					18.35							18.75							11/03/2020 05:55:37	11/03/2020 14:13:02	8.30					13/03/2020 08:24:23	13/03/2020 16:02:43	7.63
5	12/03/2020 05:27:10	12/03/2020 14:02:02	8.58					Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S		Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S		12/03/2020 05:53:50	12/03/2020 14:05:53	8.20					24.10
6	13/03/2020 05:25:56	13/03/2020 14:02:08	8.62					0011							0100							33.17							Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S
7	43.08							0011	11/03/2020	F	070000	160000	S		0100	09/03/2020	F	073000	170000	H		Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S
8	Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S		0011	12/03/2020	F	070000	160000	S		0100	10/03/2020	F	073000	170000	H		1010
9								0011	13/03/2020	F	070000	160000	S		0100	11/03/2020	F	073000	170000	H		1010	13/03/2020	F	070000	140000	H
10
11
12	0008	A NOTHER						0011	B NOTHER						0100	C NOTHER						1010	D NOTHER						6666	E NOTHER
13	09/03/2020 05:23:44	09/03/2020 14:02:07	8.65					09/03/2020 06:54:13	09/03/2020 16:04:15	9.17					12/03/2020 06:29:06	12/03/2020 16:05:38	9.60					09/03/2020 05:49:02	09/03/2020 14:10:25	8.35					10/03/2020 08:21:47	10/03/2020 16:30:51	8.15
14	10/03/2020 05:22:27	10/03/2020 14:02:03	8.67					10/03/2020 06:52:35	10/03/2020 16:03:44	9.18					13/03/2020 06:27:22	13/03/2020 15:36:58	9.15					10/03/2020 05:50:30	10/03/2020 14:09:03	8.32					11/03/2020 08:24:57	11/03/2020 16:43:52	8.32
15	11/03/2020 05:28:18	11/03/2020 14:02:06	8.57					18.35							18.75							11/03/2020 05:55:37	11/03/2020 14:13:02	8.30					13/03/2020 08:24:23	13/03/2020 16:02:43	7.63
16	12/03/2020 05:27:10	12/03/2020 14:02:02	8.58					Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S		Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S		12/03/2020 05:53:50	12/03/2020 14:05:53	8.20					24.10
17	13/03/2020 05:25:56	13/03/2020 14:02:08	8.62					0011							0100							33.17							Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S
18	43.08							0011	11/03/2020	F	070000	160000	S		0100	09/03/2020	F	073000	170000	H		Employee_Clock_No	Date	Full_or_Half_Day	From_Time	To_Time	Code_H_or_S
19
20
Sheet3

 

[picklefactory]

Is it possible to find each 4 digit clock number and set as first row of range to transpose, and then find next 4 digit clock number and use that (row-1) for last row of range to transpose?
I wish I knew more

 

[Fluff]

How about

Sub picklefactory()
   Dim i As Long
   
   With Range("A:A").SpecialCells(xlConstants).Areas
      For i = 2 To .Count
         With .Item(i).CurrentRegion
            .Copy Cells(1, (i - 1) * 8)
            .ClearContents
         End With
      Next i
   End With
End Sub

 

[picklefactory]

That's got the bugger!!!
Double checked this time, and that works exactly as I hoped. Thanks again Fluff, that's bob on. Thanks again also to mumps for the initial help with my crappy explanation.