Create Parent Child from legacy data

[arosyid]

Hi MrExcel.

I would like to ask abaout data processing using macro instead manual process.
because it's repetition task.

please someone help me to solve this case using macro.

Thanks in advanced.

 

[arosyid]

for additional information.

by running the macro, i will get the expected result.
1. create new two coulomb.
2. read data hierarchy from existing data (coulomb item ), and paste the name in the line manager.
3. create new information, manager and staff depend on the name has sub-ordinate employee or not.

Thanks.

 

[Marcelo Branco]

Welcome to Mr Excel

If a solution using formulas is acceptable, maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Item​

[/td][td]

Name​

[/td][td][/td][td][/td][td]

Item​

[/td][td]

Line manager​

[/td][td]

Name​

[/td][td]

Position​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

adry​

[/td][td][/td][td][/td][td]

1​

[/td][td]

top manager​

[/td][td]

adry​

[/td][td]

manager​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1.1​

[/td][td]

diana​

[/td][td][/td][td][/td][td]

1.1​

[/td][td]

adry​

[/td][td]

diana​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1.2​

[/td][td]

john​

[/td][td][/td][td][/td][td]

1.2​

[/td][td]

adry​

[/td][td]

john​

[/td][td]

manager​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

1.2.1​

[/td][td]

joshua​

[/td][td][/td][td][/td][td]

1.2.1​

[/td][td]

john​

[/td][td]

joshua​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

1.2.2​

[/td][td]

gabriel​

[/td][td][/td][td][/td][td]

1.2.2​

[/td][td]

john​

[/td][td]

gabriel​

[/td][td]

manager​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

1.2.2.1​

[/td][td]

kevin​

[/td][td][/td][td][/td][td]

1.2.2.1​

[/td][td]

gabriel​

[/td][td]

kevin​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

1.2.2.2​

[/td][td]

jihan​

[/td][td][/td][td][/td][td]

1.2.2.2​

[/td][td]

gabriel​

[/td][td]

jihan​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

1.2.2.3​

[/td][td]

sary​

[/td][td][/td][td][/td][td]

1.2.2.3​

[/td][td]

gabriel​

[/td][td]

sary​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

1.2.3​

[/td][td]

bob​

[/td][td][/td][td][/td][td]

1.2.3​

[/td][td]

john​

[/td][td]

bob​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

1.2.4​

[/td][td]

tesla​

[/td][td][/td][td][/td][td]

1.2.4​

[/td][td]

john​

[/td][td]

tesla​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

2​

[/td][td]

bush​

[/td][td][/td][td][/td][td]

2​

[/td][td]

top manager​

[/td][td]

bush​

[/td][td]

manager​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

2.1​

[/td][td]

george​

[/td][td][/td][td][/td][td]

2.1​

[/td][td]

bush​

[/td][td]

george​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

2.2​

[/td][td]

olof​

[/td][td][/td][td][/td][td]

2.2​

[/td][td]

bush​

[/td][td]

olof​

[/td][td]

manager​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

2.2.1​

[/td][td]

steven​

[/td][td][/td][td][/td][td]

2.2.1​

[/td][td]

olof​

[/td][td]

steven​

[/td][td]

staff​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

2.2.2​

[/td][td]

kent​

[/td][td][/td][td][/td][td]

2.2.2​

[/td][td]

olof​

[/td][td]

kent​

[/td][td]

staff​

[/td][/tr]
[/table]


Columns E and G are identical to A and B respectively. So you can either copy/paste or use formulas

in E2 copied down
=A2

in G2 copied down
=B2

Results
Formula in F2 copied down
=IFERROR(INDEX(B:B,MATCH(LEFT(A2,SEARCH("#",SUBSTITUTE(A2,".","#",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1),A:A,0)),"top manager")

Formula in H2 copied down
=IF(COUNTIF(A$2:A$1000,A2&"*")>1,"manager","staff")

Hope this helps

M.

 

[arosyid]

Dear marcelo,

Thanks for your help,
i've try and it's work for H2 formula.

but, i've problem with F2 coloumb.
is there any mistakes from copied formula?

Thanks in advanced.
AR.

 

[Marcelo Branco]

The values in column A must be texts, not numbers, for the formula to work.

Either format column A as text and re-enter the Items or put an apostrophe before each item like
Type in A2
'1
'1.1
'1.2
...

M.

 

[Peter_SSs]

Another option if you are happy to have the item number of the "top managers" with a "." at the end like below.
Since you appear to have formal tables I have used structured references in my formulas. If the left hand table is not called "Table1" then change that in the column E & column G formulas.
Table1 can be left with the mixture of numerical & text values in column A.

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")

Edit: BTW, please investigate the link in my signature block below for providing sample data that we can copy, so we don't have to type it out manually

 

[Marcelo Branco]

To convert the values in column A to text you can use
Select A2:A16
Data > Text to columns
Delimited
Next
Next
In step 3 pick Text
Finish

Done!

M.

 

[arosyid]

Thanks,
It's works.

 

[Peter_SSs]

Thanks,
It's works.

Not sure which of the two methods you used, but glad you got something that works for you.