Ragged Hierarchy

[banker123]

I have an excel file with a ragged hierarchy as shown below, I would
like to structure the data in a different format (also shown below)
please help. Also the data below is just an example the actual data
file is much larger and the hierarchy has 10 levels the lowest level
"Employee" could be stored anywhere wtihin the 10 levels depeding upon
the business units structure. All yeah I am not a VBA programmer,
business power user.


Original
Company Business Unit1 Employee1
Company Business Unit1 Business Unit2 Employee2

New
Employee1 Company Business Unit1
Employee2 Company Business Unit1 Business Unit2

 

[CLR]

If you are not wanting to actually move the columns around, but rather to
just filter the data as desired, you might take a look at Data > Filter >
Autofilter.......or Advanced Filter.

Some of the functions might be accomplished by using creative IF statements
to bring say "Employee1" and "Employee2" both over to the same column,
etc......
=IF(LEFT(A1,3)="Emp",A1,IF(LEFT(B1,3)="Emp",B1...etc etc......,"")

Any way about it, it's no small task.

Vaya con Dios,
Chuck, CABGx3

 

[banker123]

Employee is actual employees names, that vary. While I like the
thought, I do not think I can apply the if statement because of the
employee name always changing. I agree this is no small task, if I
can find a solution I plan top document and share.

 

[CLR]

There needs to be some way of identifying the "Employee" cells, so they can
be automatically extracted from the row to a given column......OR,
identifying all the other cells NOT an "Employee" for the same purpose.
Otherwise, this small macro, run by Ctrl-Z after the manual selection of
each Employee cell will copy them over to column D (which can be changed to
your need)

Sub MoveMe()
'Copies selection value to column D
'Macro assumes Header text in row 1
' Keyboard Shortcut: Ctrl+z
Dim mycell
ActiveCell.Select
mycell = Selection.Value
Range("d65000").Select
Application.GoTo Reference:="R65000C4"
Selection.End(xlUp).Offset(1, 0).Select
Selection.Value = mycell
End Sub

hth
Vaya con Dios,
Chuck, CABGx3

 

[banker123]

Yes, the only way to identify the employee cell is the last cell in
the hierarchy with data. Manually selecting over 20,000 employees is
not feasable.

 

[CLR]

Well then, perhaps some variation of this will help........

=INDIRECT(LOOKUP(COUNTA(A2:J2),{1,2,3,4,5,6,7,8,9,10},{"A","B","C","D","E","F","G","H","I","J"})&ROW())

Vaya con Dios,
Chuck, CABGx3