Macro or VBA to format jumbled sheet

K

Keegan4123

New Member
Joined
Mar 30, 2022
Messages
29
Office Version
  1. 2016
Platform
  1. 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
ABCDEFGHIJKLM
1Raw Data Example
2Observation
3ID#Date submittedSubmitterSubjectLocationTagsThe 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
4474922022-07-14T17:59:02Submitter 1Subject InfoLocation Info
5474052022-07-14T16:27:09Submitter 2Subject InfoLocation Info
6474032022-07-14T16:24:54Submitter 3Subject InfoLocation Info
7473472022-07-14T15:55:45Submitter 4Subject InfoLocation Info
8473232022-07-14T15:46:37Submitter 5Subject InfoLocation Info
9473112022-07-14T15:39:49Submitter 6Subject InfoLocation Info
10473092022-07-14T15:36:00Submitter 7Subject InfoLocation Info
11472762022-07-14T15:02:51Submitter 8Subject InfoLocation Info
12472372022-07-14T14:40:13Submitter 9Subject InfoLocation Info
13471812022-07-14T13:36:23Submitter 10Subject InfoLocation Info
14471632022-07-14T13:08:29Submitter 11Subject InfoLocation Info
15471612022-07-14T13:07:17Submitter 12Subject InfoLocation Info
16471372022-07-14T12:40:15Submitter 13Subject InfoLocation Info
17470742022-07-14T10:38:47Submitter 14Subject InfoLocation Info
18
19Near Miss
20ID#Date submittedSubmitterSubjectLocationTags
21475622022-07-14T23:14:48Submitter 17Subject InfoLocation Info
22
23Hazard ID
24ID#Date submittedSubmitterSubjectLocationTags
25475322022-07-14T19:14:42Submitter 22Subject InfoLocation Info
26470942022-07-14T11:37:38Submitter 23Subject InfoLocation Info
27
28Corrected and filtered Data Example
29Observation2022-07-14T17:59:02Submitter 1Subject InfoLocation Info
30Observation2022-07-14T16:27:09Submitter 2Subject InfoLocation Info
31Observation2022-07-14T16:24:54Submitter 3Subject InfoLocation Info
32Observation2022-07-14T15:55:45Submitter 4Subject InfoLocation Info
33Observation2022-07-14T15:46:37Submitter 5Subject InfoLocation Info
34Observation2022-07-14T15:39:49Submitter 6Subject InfoLocation Info
35Observation2022-07-14T15:36:00Submitter 7Subject InfoLocation Info
36Observation2022-07-14T15:02:51Submitter 8Subject InfoLocation Info
37Observation2022-07-14T14:40:13Submitter 9Subject InfoLocation Info
38Observation2022-07-14T13:36:23Submitter 10Subject InfoLocation Info
39Observation2022-07-14T13:08:29Submitter 11Subject InfoLocation Info
40Observation2022-07-14T13:07:17Submitter 12Subject InfoLocation Info
41Observation2022-07-14T12:40:15Submitter 13Subject InfoLocation Info
42Observation2022-07-14T10:38:47Submitter 14Subject InfoLocation Info
43Near Miss2022-07-14T23:14:48Submitter 17Subject InfoLocation Info
44Hazard ID2022-07-14T19:14:42Submitter 22Subject InfoLocation Info
45Hazard ID2022-07-14T11:37:38Submitter 23Subject InfoLocation Info
Raw Data
 
Sort by date Sort by votes
Hi Fluff,
I tried it - works really well - some pretty cunning code in there as opposed to my "make it work for me so I understand it" approach :)

nice one... always learning..

Rob
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA: delete all rows where value (time) is less than 1 minute
2
Replies
13
Views
219
RobP
R
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
ExcelNewbie2020
  • Solved
sum function that will reset the running total every after 5 counts
Replies
3
Views
289
Cubist
Cubist
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top