Soldato
Hi,
I really need some help to see if this is doable. We have a system in our school whereby teachers record behavioural incidents. The system allows reports to be downloaded, but the format they come in are not excel friendly and therefore we can't do the analysis we would like. As you can see from the mock data below, it summarises incidents for each student and adds an additional row with their name in the A column at the start of their incidents list. I don't need this as their name is in the B column anyway for each incident.
In addition, when teachers decide to add a comment to the incident, it puts this in as an extra row, and they all start with 'Grounds comment' in the B column. I don't need these comments at all.
Basically what I need to do is get Excel to automatically delete any row that has a blank value in the B column (This would then delete the rows with the students name on, as these only have values in the A column), and also delete any rows where the B column begins with 'Grounds comment'. This would then leave me with all the incidents that can properly be put in to a pivotable.
The company that do the system can't figure it out, but I'm sure it must be possible with some clever macro?
Help please!
I really need some help to see if this is doable. We have a system in our school whereby teachers record behavioural incidents. The system allows reports to be downloaded, but the format they come in are not excel friendly and therefore we can't do the analysis we would like. As you can see from the mock data below, it summarises incidents for each student and adds an additional row with their name in the A column at the start of their incidents list. I don't need this as their name is in the B column anyway for each incident.
In addition, when teachers decide to add a comment to the incident, it puts this in as an extra row, and they all start with 'Grounds comment' in the B column. I don't need these comments at all.
Basically what I need to do is get Excel to automatically delete any row that has a blank value in the B column (This would then delete the rows with the students name on, as these only have values in the A column), and also delete any rows where the B column begins with 'Grounds comment'. This would then leave me with all the incidents that can properly be put in to a pivotable.
The company that do the system can't figure it out, but I'm sure it must be possible with some clever macro?
Help please!
Code:
Name: Bloggs, Joe
Bloggs, Joe MSM Year 9 19/10/2016 Wed:1 Mr Smith Celebration Basic expectations Manners Credit (1 point) 9y/En2 English 2-008 English Room (2008) 1
Bloggs, Joe MSM Year 9 18/10/2016 Tue:1 Mr Smith Celebration Progress Effort Star Student 9cd/Sc1 Science 2-018 Science Lab (2018) 5
Bloggs, Joe MSM Year 9 18/10/2016 Tue:2 Mr Smith Celebration Basic expectations Proud Credit (1 point) 9D/Dr1 Drama 0-064 Theatre / Hall (0064) 1
Bloggs, Joe MSM Year 9 18/10/2016 Tue:1 Mr Smith Celebration Progress Excellent classwork Credit (1 point) 9cd/Sc1 Science 2-018 Science Lab (2018) 1
Bloggs, Joe MSM Year 9 17/10/2016 Mon:1 Mr Smith Incident Progress "Lack of Work " C2 - Lunch detention 9B/Hs1 Health & social 2-027 Science Lab (2027) -1
Bloggs, Joe MSM Year 9 17/10/2016 Mon:5 Mr Smith Celebration Progress Excellent classwork Star Student 9cd/Pe2 Physical E 5
Bloggs, Joe MSM Year 9 17/10/2016 Mon:3 Mr Smith Celebration Progress Excellent classwork Credit (1 point) 9y/Ma2 Mathematic 1-001 Maths Room (1001) 1
7 13
Name: Bloggs, Jim
Bloggs, Jim MSM Year 9 19/10/2016 Wed:Reg Mr Smith Incident Basic expectations No book C2 - Move in Class Reg Registrati 1-003 Maths Room (1003) -1
Grounds comment: No book for tutor time literacy.
Bloggs, Jim MSM Year 9 19/10/2016 Wed:Reg Mr Smith Celebration Basic expectations Improved behaviour "Credit (Improved behaviour ) " Reg Registrati 1-003 Maths Room (1003) 1