VBA - Help cleaning data

[omnivl]

Hi
Im stuck on how to achieve this so any input would be appreciated. I have imported the data from tables in Word and I need to clean and sort it. Its a fair bit of data (50,000 rows).
The imported data is set out as per below:


[TABLE="class: grid, width: 560"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[/TR]
[TR]
[TD]PROVISION[/TD]
[TD]POWER, FUNCTION OR ITEM DELEGATED[/TD]
[TD]DELEGATE[/TD]
[TD]CONDITIONS & LIMITATIONS
[/TD]
[/TR]
[TR]
[TD]s.12(1)[/TD]
[TD]function to properly and efficiently manage[/TD]
[TD]Corporate;Main Officer
[/TD]
[TD]where Class B
[/TD]
[/TR]
[TR]
[TD]s.12(2) [/TD]
[TD]duty to have regard to the matters set out in paragraphs[/TD]
[TD]Corporate[/TD]
[TD]where Class B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DOMESTIC ANIMALS ACT 1994[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[/TR]
[TR]
[TD]PROVISION[/TD]
[TD]POWER, FUNCTION OR ITEM DELEGATED[/TD]
[TD]DELEGATE[/TD]
[TD]CONDITIONS AND LIMITATIONS[/TD]
[/TR]
[TR]
[TD]s.41A(1)
[/TD]
[TD]power to declare a dog to be a menacing dog[/TD]
[TD]Community Laws Coordinator;Senior Community Laws Officer;Community Laws Officers[/TD]
[TD]where Class C
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ENVIRONMENT PROTECTION ACT 1970
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3
[/TD]
[TD]Column 4[/TD]
[/TR]
[TR]
[TD]PROVISION[/TD]
[TD]POWER, FUNCTION OR ITEM DELEGATED[/TD]
[TD]DELEGATE
[/TD]
[TD]CONDITIONS & LIMITATIONS[/TD]
[/TR]
[TR]
[TD]r.53M(3)[/TD]
[TD]power to require further information Septic Tanks[/TD]
[TD]Director Development;Manager Statutory Services[/TD]
[TD]where Class B
[/TD]
[/TR]
</tbody>[/TABLE]

I need to have the data cleaned and sorted like so:

[TABLE="class: grid, width: 560"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Act
[/TD]
[TD]Provision[/TD]
[TD]Power, Function or Item Delegated[/TD]
[TD]Delegate[/TD]
[TD]Conditions & Limitations[/TD]
[/TR]
[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions[/TD]
[TD]s.12(1)[/TD]
[TD]function to properly and efficiently manage[/TD]
[TD]Corporate[/TD]
[TD]where Class B[/TD]
[/TR]
[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions[/TD]
[TD]s.12(1) [/TD]
[TD]function to properly and efficiently manage[/TD]
[TD]Main Officer[/TD]
[TD]where Class B[/TD]
[/TR]
[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions[/TD]
[TD]s.12(2) [/TD]
[TD]duty to have regard to the matters set out in paragraphs[/TD]
[TD]Corporate[/TD]
[TD]where Class B
[/TD]
[/TR]
[TR]
[TD]DOMESTIC ANIMALS ACT 1994[/TD]
[TD]s.41A(1)[/TD]
[TD]power to declare a dog to be a menacing dog[/TD]
[TD]Community Laws Coordinator[/TD]
[TD]where Class C[/TD]
[/TR]
[TR]
[TD]DOMESTIC ANIMALS ACT 1994[/TD]
[TD]s.41A(1)[/TD]
[TD]power to declare a dog to be a menacing dog[/TD]
[TD]Senior Community Laws Officer[/TD]
[TD]where Class C[/TD]
[/TR]
[TR]
[TD]DOMESTIC ANIMALS ACT 1994[/TD]
[TD]s.41A(1)[/TD]
[TD]power to declare a dog to be a menacing dog[/TD]
[TD]Community Laws Officers[/TD]
[TD]where Class C[/TD]
[/TR]
[TR]
[TD]ENVIRONMENT PROTECTION ACT 1970[/TD]
[TD]r.53M(3)[/TD]
[TD]power to require further information Septic Tanks[/TD]
[TD]Director Development[/TD]
[TD]where Class B[/TD]
[/TR]
[TR]
[TD]ENVIRONMENT PROTECTION ACT 1970[/TD]
[TD]r.53M(3)[/TD]
[TD]power to require further information Septic Tanks[/TD]
[TD]Manager Statutory Services[/TD]
[TD]where Class B[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[j8h9x]

This is fairly simple looping/reformatting via VBA. Just Do/Loop and move data to new target sheet. Do you need explicit code?

 

[omnivl]

If you have the time code would be good, bit beyond my expertise i feel

 

[omnivl]

So i have managed to get it into the following format..wow..ok so how do i split the delegate and add new row

Act [TABLE="class: cms_table_grid, width: 560"]
<tbody>[TR]
[TD][/TD]
[TD]Provision[/TD]
[TD]Power, Function or Item Delegated[/TD]
[TD]Delegate[/TD]
[TD]Conditions & Limitations[/TD]
[/TR]
[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions[/TD]
[TD]s.12(1)[/TD]
[TD]function to properly and efficiently manage[/TD]
[TD]Corporate; Main Officer
[/TD]
[TD]where Class B[/TD]
[/TR]
[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions
[/TD]
[TD]s.12(2)[/TD]
[TD]duty to have regard to the matters set out in paragraphs[/TD]
[TD]Corporate[/TD]
[TD]where Class B[/TD]
[/TR]
</tbody>[/TABLE]

so it looks like this

ct [TABLE="class: cms_table_grid, width: 560"]
<tbody>[TR]
[TD][/TD]
[TD]Provision[/TD]
[TD]Power, Function or Item Delegated[/TD]
[TD]Delegate[/TD]
[TD]Conditions & Limitations[/TD]
[/TR]
[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions[/TD]
[TD]s.12(1)[/TD]
[TD]function to properly and efficiently manage[/TD]
[TD]Corporate[/TD]
[TD]where Class B[/TD]
[/TR]
[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions[/TD]
[TD]s.12(1)[/TD]
[TD]function to properly and efficiently manage[/TD]
[TD]Main Officer[/TD]
[TD]where Class B[/TD]
[/TR]
[TR]
[TD]CEMETERIES AND CREMATORIA ACT 2003 provisions[/TD]
[TD]s.12(2)[/TD]
[TD]duty to have regard to the matters set out in paragraphs[/TD]
[TD]Corporate[/TD]
[TD]where Class B[/TD]
[/TR]
</tbody>[/TABLE]

 

[omnivl]

With the help from another thread i have solved this