How to handle hierarchy processing like this

  • Thread starter Thread starter Belinda
  • Start date Start date
B

Belinda

Hello All

I have a cost center hierarchy which is like the following:

ROOT
|
|-NODEA
| |
| |-NODEB
| |-NODEC
| |-NODED
| |-NODEF
| |-NODEG
|
|-NODEH
|-NODEI


I get the above cost center hierarchy into a spreadsheet in the
following format.



A B C D E <== Spreadsheet columns
=========================================================
ROOT
NODEA

NODEB
NODEC
NODED
NODEF
NODEG

NODEH
NODEI
=======================================================


I want the above spreadsheet output flattened into the following
format:

======================
CostCenter Parent
======================
NODEA ROOT
NODEB NODEA
NODEC NODEA
NODED NODEC
NODEF NODED
NODEG NODED
NODEH ROOT
NODEI NODEH
======================

can you kindly let me know how I can get the above spreadsheet into
the above format.

Can you kindly let me know how I can process the hierarchy into the
above format.


Thanks
Belinda
 
Sub Tester2()
Dim rng As Range, cell As Range
Dim sh2 As Worksheet
Dim sh1 As Worksheet
Set sh1 = ActiveSheet
Set sh2 = Worksheets.Add(after:=ActiveSheet)
Set rng = sh1.Range("B1:D200")
For Each cell In rng
If cell <> "" Then
rw = rw + 1
sh2.Cells(rw, 1) = cell.Value
sh2.Cells(rw, 2) = cell.Offset(0, -1).End(xlUp).Value
End If
Next

End Sub

Should get you started.
 
Belinda said:
I have a cost center hierarchy which is like the following:

ROOT
|
|-NODEA
| |
| |-NODEB
| |-NODEC
| |-NODED
| |-NODEF
| |-NODEG
|
|-NODEH
|-NODEI


I get the above cost center hierarchy into a spreadsheet in the
following format.

A B C D E <== Spreadsheet columns
=========================================================
ROOT
NODEA

NODEB
NODEC
NODED
NODEF
NODEG

NODEH
NODEI
=======================================================
Click to expand...

Do you really have blank lines between NODEA-NODEB and NODEG-NODEH?
I want the above spreadsheet output flattened into the following
format:

======================
CostCenter Parent
======================
NODEA ROOT
NODEB NODEA
NODEC NODEA
NODED NODEC
NODEF NODED
NODEG NODED
NODEH ROOT
NODEI NODEH
======================
Click to expand...
....

Just to be different, you could do this with only worksheet functions and
formulas. If your hierarchical listing in the worksheet were named HLst and
the top-left result cell (NODEA) were named T_L, then enter the following
formulas for the first cost center-parent record.

Cost Center:
=HLOOKUP("?*",INDEX(HLst,ROW()-ROW(T_L)+2,0),1,0)

Parent: [array formula]
=VLOOKUP("?*",T(OFFSET(HLst,ROW()-ROW(T_L)+1-ROW(INDIRECT("1:"&
(ROW()-ROW(T_L)+1))),MATCH("?*",INDEX(HLst,ROW()-ROW(T_L)+2,0),0)
-2,1,1)),1,0)

Select these two cells (which should be a 1 row by 2 column range) and fill
down as needed. Blank rows in HLst will generate #N/A in both columns. The
cost center formula will generate #REF! when the entries in HLst have been
exhausted.
 
Back
Top