Keegan4123
New Member
- Joined
- Mar 30, 2022
- Messages
- 29
- Office Version
- 2016
- Platform
- Windows
We have a sheet that we get from a client daily or every 2nd day. They used to have it formatted in a way that worked well with our system and could easily get the information we needed. However they have recently changed the output and its causing alot of manual manipulation and room for error. I have attached a mini sheet so you can all see how it comes in, and the ultimate output. The issue is the groups of rows can change daily for how many are in each, which makes a recorded macro not useable. The sheet used to have the ID listed first, and then the form type (observation, near miss, hazard ID) next to that. Now they are lumping all together and using a same column label. This obviously doesnt work for us because we have various lookups that reference the form type cells in order to pull other data. The ID is not needed, and we have just been filling down and deleting, but its manual, and takes time. With the chance for mistake we don't want to rely on that.
| Workbook help.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Raw Data Example | ||||||||||||||
| 2 | Observation | ||||||||||||||
| 3 | ID# | Date submitted | Submitter | Subject | Location | Tags | The distribution between these different types varies from day to day, and if the report is run over multiple days. So a standard macro record wont work because of the inconsistencies | ||||||||
| 4 | 47492 | 2022-07-14T17:59:02 | Submitter 1 | Subject Info | Location Info | ||||||||||
| 5 | 47405 | 2022-07-14T16:27:09 | Submitter 2 | Subject Info | Location Info | ||||||||||
| 6 | 47403 | 2022-07-14T16:24:54 | Submitter 3 | Subject Info | Location Info | ||||||||||
| 7 | 47347 | 2022-07-14T15:55:45 | Submitter 4 | Subject Info | Location Info | ||||||||||
| 8 | 47323 | 2022-07-14T15:46:37 | Submitter 5 | Subject Info | Location Info | ||||||||||
| 9 | 47311 | 2022-07-14T15:39:49 | Submitter 6 | Subject Info | Location Info | ||||||||||
| 10 | 47309 | 2022-07-14T15:36:00 | Submitter 7 | Subject Info | Location Info | ||||||||||
| 11 | 47276 | 2022-07-14T15:02:51 | Submitter 8 | Subject Info | Location Info | ||||||||||
| 12 | 47237 | 2022-07-14T14:40:13 | Submitter 9 | Subject Info | Location Info | ||||||||||
| 13 | 47181 | 2022-07-14T13:36:23 | Submitter 10 | Subject Info | Location Info | ||||||||||
| 14 | 47163 | 2022-07-14T13:08:29 | Submitter 11 | Subject Info | Location Info | ||||||||||
| 15 | 47161 | 2022-07-14T13:07:17 | Submitter 12 | Subject Info | Location Info | ||||||||||
| 16 | 47137 | 2022-07-14T12:40:15 | Submitter 13 | Subject Info | Location Info | ||||||||||
| 17 | 47074 | 2022-07-14T10:38:47 | Submitter 14 | Subject Info | Location Info | ||||||||||
| 18 | |||||||||||||||
| 19 | Near Miss | ||||||||||||||
| 20 | ID# | Date submitted | Submitter | Subject | Location | Tags | |||||||||
| 21 | 47562 | 2022-07-14T23:14:48 | Submitter 17 | Subject Info | Location Info | ||||||||||
| 22 | |||||||||||||||
| 23 | Hazard ID | ||||||||||||||
| 24 | ID# | Date submitted | Submitter | Subject | Location | Tags | |||||||||
| 25 | 47532 | 2022-07-14T19:14:42 | Submitter 22 | Subject Info | Location Info | ||||||||||
| 26 | 47094 | 2022-07-14T11:37:38 | Submitter 23 | Subject Info | Location Info | ||||||||||
| 27 | |||||||||||||||
| 28 | Corrected and filtered Data Example | ||||||||||||||
| 29 | Observation | 2022-07-14T17:59:02 | Submitter 1 | Subject Info | Location Info | ||||||||||
| 30 | Observation | 2022-07-14T16:27:09 | Submitter 2 | Subject Info | Location Info | ||||||||||
| 31 | Observation | 2022-07-14T16:24:54 | Submitter 3 | Subject Info | Location Info | ||||||||||
| 32 | Observation | 2022-07-14T15:55:45 | Submitter 4 | Subject Info | Location Info | ||||||||||
| 33 | Observation | 2022-07-14T15:46:37 | Submitter 5 | Subject Info | Location Info | ||||||||||
| 34 | Observation | 2022-07-14T15:39:49 | Submitter 6 | Subject Info | Location Info | ||||||||||
| 35 | Observation | 2022-07-14T15:36:00 | Submitter 7 | Subject Info | Location Info | ||||||||||
| 36 | Observation | 2022-07-14T15:02:51 | Submitter 8 | Subject Info | Location Info | ||||||||||
| 37 | Observation | 2022-07-14T14:40:13 | Submitter 9 | Subject Info | Location Info | ||||||||||
| 38 | Observation | 2022-07-14T13:36:23 | Submitter 10 | Subject Info | Location Info | ||||||||||
| 39 | Observation | 2022-07-14T13:08:29 | Submitter 11 | Subject Info | Location Info | ||||||||||
| 40 | Observation | 2022-07-14T13:07:17 | Submitter 12 | Subject Info | Location Info | ||||||||||
| 41 | Observation | 2022-07-14T12:40:15 | Submitter 13 | Subject Info | Location Info | ||||||||||
| 42 | Observation | 2022-07-14T10:38:47 | Submitter 14 | Subject Info | Location Info | ||||||||||
| 43 | Near Miss | 2022-07-14T23:14:48 | Submitter 17 | Subject Info | Location Info | ||||||||||
| 44 | Hazard ID | 2022-07-14T19:14:42 | Submitter 22 | Subject Info | Location Info | ||||||||||
| 45 | Hazard ID | 2022-07-14T11:37:38 | Submitter 23 | Subject Info | Location Info | ||||||||||
Raw Data | |||||||||||||||
