data layout

[Manexcel]

I have a dataset that is grouped by level which I wish to re format onto another worksheet. An example is below:

Existing dataset

ColA ColB ColC
Level 1
Space
name1 group1 L1111
Space
Level 2
space
Name2 group2 C2222
Name3 group3 L3333
Name4 group4 L4444
space
Level 3
space
Name5 group5 L5555
Name6 group6 L6666
etc
Name20 group20 L2020

Is it possible to have a formula that will format / group the above dataset as follows (each group could have a maximum of say 20):

Required output

ColA ColB ColC ColD

Level1 name 1 group 1 L1111
Level2 name2 group2 C2222
name3 group3 L3333
name4 group4 L4444
Level3 name5 group5 C5555
name6 group6 L6666
name20 group20 L2020
etc

I thank you for time and understanding.

 

[Manexcel]

Thank you for the procedure. The procedure deleted Col D. It moved "group" - Col C to Col A and ended. "Title" is now in Col B. "name" is in Col C.

Test dataset

ColA ColB ColC ColD

title	name	group	date
Senior manager	John Smith	group1	01/01/2017
Senior manager	jock smith	group 2	01/01/2018
Manager	mary smith	group 3	01/01/2019
admin	alan smith	group 4	01/01/2020

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

Output after running procedure

group	title	name
group1	Senior manager	John Smith
group 2	Senior manager	jock smith
group 3	Manager	mary smith
group 4	admin	alan smith

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

Any thoughts? Thank you.

 

[Fluff]

The code in post#10 replaces the SplitData code I originally supplied, in a previous thread. And should be run on your original data.
If it's not working can you please let me know what it's doing wrong.

 

[Manexcel]

I run the procedure against the original dataset. I had one or two attempts on the grouping but eventually changed the column for the grouping to Col C and the output was as I expected. But the procedure did delete Col D which I would like to keep. (I couldn't see how to add this to the procedure?)

Also, is it possible to create the output onto another sheet instead of same sheet?

Thank you so much for all your time and patience it is very much appreciated.

 

[Fluff]

OK, add these 2 lines to the beginning of the code, this will create a sheet called New, with the data.

   ActiveSheet.Copy after:=Sheets(Sheets.Count)
   ActiveSheet.Name = "New"

and remove this line, which deletes the column

Columns(4).Delete