A | B | C | D | E | F | G | H | |
1 | Item | Name | Item | Line manager | Name | Position | ||
2 | 1 | adry | 1 | top manager | adry | manager | ||
3 | 1.1 | diana | 1.1 | adry | diana | staff | ||
4 | 1.2 | john | 1.2 | adry | john | manager | ||
5 | 1.2.1 | joshua | 1.2.1 | john | joshua | staff | ||
6 | 1.2.2 | gabriel | 1.2.2 | john | gabriel | manager | ||
7 | 1.2.2.1 | kevin | 1.2.2.1 | gabriel | kevin | staff | ||
8 | 1.2.2.2 | jihan | 1.2.2.2 | gabriel | jihan | staff | ||
9 | 1.2.2.3 | sary | 1.2.2.3 | gabriel | sary | staff | ||
10 | 1.2.3 | bob | 1.2.3 | john | bob | staff | ||
11 | 1.2.4 | tesla | 1.2.4 | john | tesla | staff | ||
12 | 2 | bush | 2 | top manager | bush | manager | ||
13 | 2.1 | george | 2.1 | bush | george | staff | ||
14 | 2.2 | olof | 2.2 | bush | olof | manager | ||
15 | 2.2.1 | steven | 2.2.1 | olof | steven | staff | ||
16 | 2.2.2 | kent | 2.2.2 | olof | kent | staff |
Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | item | Name | item | line manager | Name | position | ||||
2 | 1 | adry | 1. | top manager | adry | manager | ||||
3 | 1.1 | diana | 1.1 | adry | diana | staff | ||||
4 | 1.2 | john | 1.2 | adry | john | manager | ||||
5 | 1.2.1 | jhosua | 1.2.1 | john | jhosua | staff | ||||
6 | 1.2.2 | gabriel | 1.2.2 | john | gabriel | manager | ||||
7 | 1.2.2.1 | kevin | 1.2.2.1 | gabriel | kevin | staff | ||||
8 | 1.2.2.2 | jihan | 1.2.2.2 | gabriel | jihan | staff | ||||
9 | 1.2.2.3 | sary | 1.2.2.3 | gabriel | sary | staff | ||||
10 | 1.2.3 | bob | 1.2.3 | john | bob | staff | ||||
11 | 1.2.4 | tesla | 1.2.4 | john | tesla | staff | ||||
12 | 2 | bush | 2. | top manager | bush | manager | ||||
13 | 2.1 | george | 2.1 | bush | george | staff | ||||
14 | 2.2 | olof | 2.2 | bush | olof | manager | ||||
15 | 2.2.1 | steven | 2.2.1 | olof | steven | staff | ||||
16 | 2.2.2 | kent | 2.2.2 | olof | kent | staff | ||||
Parent Child (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2 | =Table1[@item]&IF(FIND(".",Table1[@item]&".")Table1[@item]),"",".") | |
F2 | =IF(RIGHT([@item],1)=".","top manager",LOOKUP(2,1/(LEN(E$2:E2)[@item])),G$2:G2)) | |
G2 | =Table1[@Name] | |
H2 | =IF(COUNTIF([item],[@item]&"*")>1,"manager","staff") |