Hierarchy from Tree

[MattHas]

Hi all,

Someone may have done this before or had some ideas on how to do it so thought I'd check before I dedicated time to it...

I have data in a tree and needed to convert it to a '.' separated hierarchy as below.
[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 70"]a[/TD]
[TD="class: xl65, width: 70"] [/TD]
[TD="class: xl65, width: 70"] [/TD]
[TD="class: xl65, width: 70"]a[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]a.b[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]a.b.c[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]a.b.d[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]a.e[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]a.e.f[/TD]
[/TR]
[TR]
[TD="class: xl65"]g[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]g[/TD]
[/TR]
</tbody>[/TABLE]

2500 rows, ~10 columns deep. Any ideas?

Ideally it would also be possible to select to exclude one or more columns. For example, excluding the second column would generate:
[TABLE="width: 280"]
<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 70"]a[/TD]
[TD="class: xl65, width: 70"] [/TD]
[TD="class: xl65, width: 70"] [/TD]
[TD="class: xl65, width: 70"]a[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]b[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]a[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]c[/TD]
[TD="class: xl65"]a.c[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]d[/TD]
[TD="class: xl65"]a.d[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]e[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]a[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]f[/TD]
[TD="class: xl65"]a.f[/TD]
[/TR]
[TR]
[TD="class: xl65"]g[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]g[/TD]
[/TR]
</tbody>[/TABLE]

Ideally duplicate entries are left blank e.g. on the above there are three 'a' entries. Could always do a remove duplicates operation after, but it may be more efficient to do it at the time?

 

[azumi]

Why is not this the results?

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]a.
[/TD]
[/TR]
[TR]
[TD]a.b.
[/TD]
[/TR]
[TR]
[TD]a.b.c.
[/TD]
[/TR]
[TR]
[TD]a.b.d.[/TD]
[/TR]
[TR]
[TD]a.e.d.
[/TD]
[/TR]
[TR]
[TD]a.e.f.
[/TD]
[/TR]
[TR]
[TD]g.e.f.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jkpieterse]

Is this a one-off? Or does it have to be dynamic?

 

[Peter_SSs]

Welcome to the MrExcel board!

Without knowing too much about just what can occur within your data, perhaps you could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formulas as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Hierarchy(rng As Range, ParamArray ExcludedColumns()) As String
  Dim a As Variant, Bits As Variant
  Dim i As Long, j As Long, rws As Long, cols As Long
  
  a = rng.Value
  rws = UBound(a, 1)
  cols = UBound(a, 2)
  For j = 1 To cols
    i = rws
    Do Until a(i, j) <> ""
      i = i - 1
    Loop
    Hierarchy = Hierarchy & "." & a(i, j)
    If i = rws Then Exit For
  Next j
  Hierarchy = Mid(Hierarchy, 2)
  If UBound(ExcludedColumns) > -1 Then
    Bits = Split(Hierarchy, ".")
    For i = 0 To UBound(ExcludedColumns)
      If ExcludedColumns(i) < UBound(Bits) + 2 Then Bits(ExcludedColumns(i) - 1) = "."
    Next i
    Hierarchy = Replace(Join(Bits, "."), "..", "")
  End If
End Function

- The column K formula is for no exclusions.
- The column L formula has used additional arguments to exclude columns 2, 4 & 5 of the given range.
- The column M formula excludes columns 2,4 & 6 of the given range and avoids duplicates.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	a										a	a	a
3		b									a.b	a
4			c								a.b.c	a.c	a.c
5				d							a.b.c.d	a.c
6				e							a.b.c.e	a.c
7					f						a.b.c.e.f	a.c	a.c.f
8						g					a.b.c.e.f.g	a.c.g
9						h					a.b.c.e.f.h	a.c.h
10							i				a.b.c.e.f.h.i	a.c.h.i	a.c.f.i
11								j			a.b.c.e.f.h.i.j	a.c.h.i.j	a.c.f.i.j
12	k										k	k	k
13		l									k.l	k
14			m								k.l.m	k.m	k.m
Hierarchy (2)

Cell Formulas
Range	Formula
K2	=Hierarchy(A$2:J2)
L2	=Hierarchy(A$2:J2,2,4,5)
M2	=IF(ISNA(MATCH(Hierarchy(A$2:J2,2,4,6),M$1:M1,0)),Hierarchy(A$2:J2,2,4,6),"")

 

[Peter_SSs]

If you wanted a standard worksheet formula approach and you have Excel 2016 through Office 365 then you could try this for the "no exclusions" option.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	a										a
3		b									a.b
4			c								a.b.c
5				d							a.b.c.d
6				e							a.b.c.e
7					f						a.b.c.e.f
8						g					a.b.c.e.f.g
9						h					a.b.c.e.f.h
10							i				a.b.c.e.f.h.i
11								j			a.b.c.e.f.h.i.j
12	k										k
13		l									k.l
14			m								k.l.m
Hierarchy (3)

Cell Formulas
Range	Formula
K2	=IF(A2="",TEXTJOIN(".",TRUE,LEFT(K1,FIND("#",SUBSTITUTE(K1&".",".","#",MATCH(TRUE,INDEX(A2:J2<>"",0),0)-1))-1),CONCAT(A2:J2)),A2)