Ragged Hierarchy

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

banker123 - 20 Dec 2007 15:45 GMT

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

Reply to this Message

CLR - 20 Dec 2007 16:47 GMT

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

> 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)
[quoted text clipped - 11 lines]
> Employee1 Company Business Unit1
> Employee2 Company Business Unit1 Business Unit2

Reply to this Message

banker123 - 20 Dec 2007 17:13 GMT

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.

Reply to this Message

CLR - 20 Dec 2007 17:59 GMT

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

> 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.

Reply to this Message

banker123 - 20 Dec 2007 18:14 GMT

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.

Reply to this Message

CLR - 20 Dec 2007 19:53 GMT

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

> 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.

Reply to this Message