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.

 

[Fluff]

Is this what you're after

Sub RealignData()

   Dim Rng As Range
   
   Columns(1).Insert
   With Columns(2)
      .Replace "Level", "=", xlPart, , False, , False, False
      For Each Rng In .SpecialCells(xlFormulas).Areas
         Rng.Cut Rng.Offset(2, -1)
      Next Rng
      .SpecialCells(xlBlanks).EntireRow.Delete
   End With
   Columns(1).Replace "=", "Level", xlPart, , False, , False, False
      
End Sub

 

[Manexcel]

Many thanks for your quick response.

At present when i run the procedure i get a runtime error 1004.

The procedure creates col A and then gives the above message.

I have my dataset in sheet2 and am placing the procedure in sheet2

Any thoughts? Thank you again.

 

[Fluff]

First off the code needs to go in a standard module, rather than a sheet module.
Secondly are your levels called Level 1, Level 2 etc?

 

[Manexcel]

ahhhh...

I defined the term "level" purely as an example name which could, in real life, be anyname (manager 1 or manager 2 or admin 1 or admin 3 and so on) that names are grouped under. I must be more specific with my requirement! Apologies.

Does the above assist you?

thank you for your continued patience.

 

[Fluff]

Does the above assist you?

Not really, it makes things a lot more complicated
Will the "levels" always have a blank row above & below?
Also will "level1" be in row 2, with a header in row 1?

 

[Manexcel]

Last week you very kindly provided me with a procedure called "splitdata" which grouped data under a role. This is four columns and many rows.

name/role/group/number.

This grouped the source dataset under "role" in col B. and placed onto sheet2. The procedure produced spaces around each group.

An example of the source dataset that "splitdata" used is:

Name

Role

group

No.

<tbody>
</tbody>

name 1 Manager 1 group1 L1111
name 2 manager 1 group2 L2222
name 3 admin 1 group3 L33
etc.

The "splitdata" procedure grouped on Col B. The output produced was:

Col A Col B Col C
space
Manager 1
space
Name 1 group1 L1111
Name 2 group2 L2222
space
space
space
Admin 1
space
Name 3 group3 L33
space
space
space
etc

This is great and in use so thank you.

This request has an additional requirement for data layout taken either from the output of "splitdata" or the dataset source.

Required output

ColA ColB ColC ColD

R1 Manager1 name1 group1 L1111
R2 name2 group2 L2222
R3 Admin1 name3 group3 L33

etc

I trust the above makes a little more sense and clarification

Many thanks for your continued patience.

 

[Fluff]

If I've got this right, you started with

Excel 2013 32 bit
	A	B	C	D	E
1	Last Name	First Name	Title 1	Title 2	Date
2	Smith	John	Senior Manager	SM1	01/01/2017
3	Jones	Alan	Senior Manager	SM1	02/02/2017
4	Bloggs	Fred	Manager	M1	01/01/2017
5	Bloggs	George	Manager	M1	02/03/2017
6	Smith	Mary	Senior Admin	SA1	02/02/2017
7	Smith	Alan	Administrator	A1	01/06/2017
ID1

And you now want this

Excel 2013 32 bit
	A	B	C	D
10	Title 1	Last Name	First Name	Date
11	Senior Manager	Smith	John	01/01/2017
12	Senior Manager	Jones	Alan	02/02/2017
13	Manager	Bloggs	Fred	01/01/2017
14	Manager	Bloggs	George	02/03/2017
15	Senior Admin	Smith	Mary	02/02/2017
16	Administrator	Smith	Alan	01/06/2017
ID1

Is that right?

 

[Manexcel]

So for each Title 1 e.g. Senior manager there is only one line for this title (A11) there is no Senior manager words in A12.


Senior manager Smith John 01/01/2017
Jones Alan 02/02/2017
Manager Bloggs Fred 01/01/2017
Bloggs george 02/03/2017
Senior admin Smith Mary 02/02/2017
Administrator Smith Alan 01/06/2017


Many thanks as always


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">ABCD10Title 1Last NameFirst NameDate11Senior ManagerSmithJohn01/01/201712Senior ManagerJonesAlan02/02/201713ManagerBloggsFred01/01/201714ManagerBloggsGeorge02/03/201715Senior AdminSmithMary02/02/201716AdministratorSmithAlan01/06/2017</body>

 

[Fluff]

Ok, how about

Sub ReorganiseData()

   Dim Cl As Range
   
   Columns(3).Cut
   Columns(1).Insert
   Columns(4).Delete
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Nothing
         Else
            Cl.ClearContents
         End If
      Next Cl
   End With
   
End Sub