picklefactory
Well-known Member
- Joined
- Jan 28, 2005
- Messages
- 506
- Office Version
- 365
- Platform
- Windows
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
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 |