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:

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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

	Provision	Power, Function or Item Delegated	Delegate	Conditions & Limitations
CEMETERIES AND CREMATORIA ACT 2003 provisions	s.12(1)	function to properly and efficiently manage	Corporate; Main Officer	where Class B
CEMETERIES AND CREMATORIA ACT 2003 provisions	s.12(2)	duty to have regard to the matters set out in paragraphs	Corporate	where Class B

<tbody>
</tbody>

so it looks like this

ct

	Provision	Power, Function or Item Delegated	Delegate	Conditions & Limitations
CEMETERIES AND CREMATORIA ACT 2003 provisions	s.12(1)	function to properly and efficiently manage	Corporate	where Class B
CEMETERIES AND CREMATORIA ACT 2003 provisions	s.12(1)	function to properly and efficiently manage	Main Officer	where Class B
CEMETERIES AND CREMATORIA ACT 2003 provisions	s.12(2)	duty to have regard to the matters set out in paragraphs	Corporate	where Class B

<tbody>
</tbody>

 

[omnivl]

With the help from another thread i have solved this