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.

--
Regards,
Tom Ogilvy


"Belinda" wrote in message
om...
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

"Belinda" wrote...
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
================================================= ======

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

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.