Rearrange Employees data in sensible format to create Org structure

[Raghav125]

Hi All,

I need to Rearrange Employees Name in sensible format to create Org structure.

I have the list of employees in the below format

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Level 4[/TD]
[TD]Level 3[/TD]
[TD]level 2[/TD]
[TD]level 1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]James[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Michael[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Jackson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl65, width: 50"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]James[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Tyler[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Jacob[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Logan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

However when we look at level-3 employee Column "John" who should be at level-4 is classified under level-3 reason being while updating the data-base an employees immediate reporting manager is considered as next level...

now I'm trying to create a org structure in excel pivot table so i need to organize this data, based on Level of the Employee

so that end results Should look like below

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Level 4[/TD]
[TD]level 3[/TD]
[TD]level 2[/TD]
[TD]level 1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl65, width: 50"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl65, width: 50"]James[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Michael[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Jackson[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl65, width: 50"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl65, width: 50"]James[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Michael[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Tyler[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl65, width: 50"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56"]Michael[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Jacob[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl65, width: 50"]John[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl65, width: 50"]James[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl65, width: 58"]Logan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


as the count of employee are 3000+ can someone please help me out to write VBA Codes for this situation at the earliest....

or

Please suggest me any other way to deal with this situation

Many thanks in advance...

Raghav

 

[Rijnsent]

Hi Raghav,

what I would do:
-create a simple macro that transforms your data into a 2-column table: "manager" and "managed", e.g. John - James, James - Michael, Michael - Jackson, etc.
-then use something like this for your hierarchy (key words for searching more solutions: parent, child ) http://www.mrexcel.com/forum/excel-...-out-parent-child-hierarchy-construction.html

Cheers,

Koen