Change parent/child to hierarchical address

**kaDargo** · 12-08-2014, 11:40 PM

My GL system defines the Chart of Accounts in a parent/child form. It allows for nine levels of depth. An excerpt is in the table below.

COA	DEPTH	ID	PARENT ID
NETINCOME	1	1
NETINCB4TAX	2	2	1
OPERINC	3	3	2
TOTOPERREV	4	4	3
TOTNETREV	5	5	4
NETPATREV	6	6	5
GRSPATREV	7	7	6
INPAT	8	8	7
IPREVBASE	9	9	8
IPCAPITATED	9	10	8
IPNONCON	9	11	8
IPFFS	9	12	8
IPOTHER	9	13	8
IPUNINSURED	9	14	8
OUTPAT	8	15	7
OPREVBASE	9	16	15
OPCAPITATED	9	17	15
OPNONCON	9	18	15

The system has several miscellaneous fields which I can populate with string (up to 30 characters) values.

My idea is to flatten the parent/child relationship into a hierarchical address.

For instance, NETINCOME is the top of my chart and I've assigned it an address value of "001000000000000000000000000". The address is comprised of nine 3-digit values (LEVELS) representing NETINCOME's place in the hierarchy:

Level 1 = 001
Level 2 = 000
Level 3 = 000
Level 4 = 000
Level 5 = 000
Level 6 = 000
Level 7 = 000
Level 8 = 000
Level 9 = 000

GRSPATREV is a descendant of NETINCOME and is several levels of depth down. I've assigned it an address of "001001001001001001001000000".

Level 1 = 001
Level 2 = 001
Level 3 = 001
Level 4 = 001
Level 5 = 001
Level 6 = 001
Level 7 = 001
Level 8 = 000
Level 9 = 000

INPAT is a direct descendant of GRSPATREV. I've assigned it an address of "001001001001001001001001000"

Level 1 = 001
Level 2 = 001
Level 3 = 001
Level 4 = 001
Level 5 = 001
Level 6 = 001
Level 7 = 001
Level 8 = 001
Level 9 = 000

I've done this manually and am looking for a formula that would do this for me and hoping someone can help.

I'm attaching a workbook with the results of my manual input. The red cells indicate differences between the cells above and are not necessary to the formula.

There are occasional changes to our COA and I don't want my future successors to throw their hands up in the air and curse my parentage when they inevitably need to make a change.

If I can provide any other information, please let me know.

Thanks.

**Danerida** · 12-09-2014, 12:59 AM

Your code assignments mean that you are only covering the depth, and not assigning unique codes to each GL item. If you would prefer unique values for you codes, you can use something like:

Formula:

=TEXT(B2,"000")&TEXT(C2,"000")&TEXT(D2,"000")

Otherwise the below does what you describe for the depth only:

Formula:

=LOOKUP(B2,{1,2,3,4,5,6,7,8,9},{"001000000000000000000000000","001001000000000000000000000","001001001000000000000000000","001001001001000000000000000","001001001001001000000000000","001001001001001001000000000","001001001001001001001000000","001001001001001001001001000","001001001001001001001001001"})

Another way:

Formula:

=REPT("001",B2)&REPT("000",9-B2)

Dan

**kaDargo** · 12-09-2014, 01:16 AM

Dan, thanks for the reply, but it's not what I'm looking for.

I need to find the values in Columns C thru K, based on the value in Column B and the Level (Columns C thru K) the formula is in. The string in Column L is a concatenation of those values.

**kaDargo** · 12-09-2014, 01:23 AM

If I insert a row between rows 1 and 2, this formula works partially.

=IF($B3=C$1,MAX(C$2:C2)+1,IF($B3>C$1,MAX(C$2:C2),0))

Unfortunately, the child values do not start numbering at 1 again when there is a change.

**Danerida** · 12-09-2014, 08:19 PM

Dan, thanks for the reply, but it's not what I'm looking for.
I need to find the values in Columns C thru K, based on the value in Column B and the Level (Columns C thru K) the formula is in.

Sorry, I'm not following your logic here.

**kaDargo** · 12-09-2014, 09:41 PM

My goal is to create an address based on the depth of the SUMMARY_ACCT and its relative position (which I've labeled Level in the workbook) to its parent within the COA. The address is comprised of nine 3-digit numbers.

NETINCOME is the top of my chart, has a depth of 1. It has no parent, so based on its depth and its relative position, I'm assigning it an address of:

"001000000000000000000000000" which is comprised of the Level values below.

Level 1 = 001
Level 2 = 000
Level 3 = 000
Level 4 = 000
Level 5 = 000
Level 6 = 000
Level 7 = 000
Level 8 = 000
Level 9 = 000

NETINCB4TAX has a depth of 2 and is a child of NETINCOME and is in the first relative position of its depth.
PROVINCTAX also has a depth of 2 and is a child of NETINCOME and is in the second relative position of its depth.

Their addresses are "001001000000000000000000000" and "001002000000000000000000000" respectively, the only difference being the value of Level 2.

INPAT is a descendant of NETINCOME, has a depth of 9, and has an address of "001001001001001001001001000".

Level 1 = 001 -> NETINCOME
Level 2 = 001 --> NETINCB4TAX
Level 3 = 001 ---> OPERINC
Level 4 = 001 ----> TOTOPERREV
Level 5 = 001 -----> TOTNETREV
Level 6 = 001 ------> NETPATREV
Level 7 = 001 -------> GRSPATREV
Level 8 = 001 --------> INPAT
Level 9 = 000

It has children of IPREVBASE, IPCAPITATED, IPNONCON, IPFFS, IPOTHER, and IPUNINSURED which have addresses of "001001001001001001001001001" through "001001001001001001001001006".

Level	IPREVBASE	IPCAPITATED	IPNONCON	IPFFS	IPOTHER	IPUNINSURED
1	1	1	1	1	1	1
2	1	1	1	1	1	1
3	1	1	1	1	1	1
4	1	1	1	1	1	1
5	1	1	1	1	1	1
6	1	1	1	1	1	1
7	1	1	1	1	1	1
8	1	1	1	1	1	1
9	1	2	3	4	5	6

OUTPAT is a descendant of NETINCOME, and also has a depth of 9. Its address is "001001001001001001001002000".

Level 1 = 001 -> NETINCOME
Level 2 = 001 --> NETINCB4TAX
Level 3 = 001 ---> OPERINC
Level 4 = 001 ----> TOTOPERREV
Level 5 = 001 -----> TOTNETREV
Level 6 = 001 ------> NETPATREV
Level 7 = 001 -------> GRSPATREV
Level 8 = 002 --------> OUTPAT
Level 9 = 000

It has children of OPREVBASE, OPCAPITATED, OPNONCON, OPFFS, OPOTHER, and OPUNINSURED which have addresses of "001001001001001001001002001" through "001001001001001001001002006".

Level	OPREVBASE	OPCAPITATED	OPNONCON	OPFFS	OPOTHER	OPUNINSURED
1	1	1	1	1	1	1
2	1	1	1	1	1	1
3	1	1	1	1	1	1
4	1	1	1	1	1	1
5	1	1	1	1	1	1
6	1	1	1	1	1	1
7	1	1	1	1	1	1
8	2	2	2	2	2	2
9	1	2	3	4	5	6

As I stated in a previous post, this formula works partially, but it does not renumber the children in relation to their parent.

=IF($B3=C$1,MAX(C$2:C2)+1,IF($B3>C$1,MAX(C$2:C2),0))

I hope this clears away some of the fog surrounding my previous explanation of the problem.

**shg** · 12-09-2014, 08:24 PM

COA	DEPTH	ID	PARENT ID
NETINCOME	1	1
NETINCB4TAX	2	2	1	D3 and down: =LOOKUP(2, 1/(B$1:B2 = B3-1), C$1:C2)
OPERINC	3	3	2
TOTOPERREV	4	4	3
TOTNETREV	5	5	4
NETPATREV	6	6	5
GRSPATREV	7	7	6
INPAT	8	8	7
IPREVBASE	9	9	8
IPCAPITATED	9	10	8
IPNONCON	9	11	8
IPFFS	9	12	8
IPOTHER	9	13	8
IPUNINSURED	9	14	8
OUTPAT	8	15	7
OPREVBASE	9	16	15
OPCAPITATED	9	17	15
OPNONCON	9	18	15

**Danerida** · 12-09-2014, 10:08 PM

Doesn't your concatenating formula calculates the address as you describe it? What does it not do that you need it to?
Also, what cell are you putting the above formula in? And what are you trying to calculate with it? Is it to calculate the levels in Col C:K ?

**kaDargo** · 12-09-2014, 10:45 PM

Yes, the concatenation formula calculates the address.

What I'm looking for is a method of generating the address components (the levels), based upon the SUMMARY_ACCTs depth and its relative position to its parent.

**Danerida** · 12-10-2014, 12:29 AM

Try this in Cell C2:

Formula:

=IF(AND(MATCH($B2,$C$1:$K$1,0)>=C$1,$B2=C$1),COUNTIF($B$2:$B2,$B2),IF(MATCH($B2,$C$1:$K$1,0)>=C$1,1,0))

Copied across and down for the rest of your table

Dan

**kaDargo** · 12-10-2014, 02:33 AM

We're getting close. The levels for SUMMARY_ACCT OUTPAT are correct, however, the levels for the children of OUTPAT are not.

**shg** · 12-10-2014, 07:18 PM

Maybe ...

Row\Col	A	B	C	D	E	F	G	H
1	SUMMARY_ACCT	DEPTH	Parent	Child
2	NETINCOME	1			001000000000000000000000000			E2: =LEFT(TEXT(B2, "000") & REPT(0, 27), 27)
3	NETINCB4TAX	2	2	1	001001000000000000000000000	C3: =LOOKUP(9E+307, ROW(B$1:B2)/(B$1:B2 = B3-1))	D3: =COUNTIF(INDEX($B:$B, C3):$B3, B3)	E3: =LEFT(LEFT(INDEX(E:E, C3), 3*B3-3) & TEXT(D3, "000") & REPT(0, 27), 27)
4	OPERINC	3	3	1	001001001000000000000000000
5	TOTOPERREV	4	4	1	001001001001000000000000000
6	TOTNETREV	5	5	1	001001001001001000000000000
7	NETPATREV	6	6	1	001001001001001001000000000
8	GRSPATREV	7	7	1	001001001001001001001000000
9	INPAT	8	8	1	001001001001001001001001000
10	IPREVBASE	9	9	1	001001001001001001001001001
11	IPCAPITATED	9	9	2	001001001001001001001001002
12	IPNONCON	9	9	3	001001001001001001001001003
13	IPFFS	9	9	4	001001001001001001001001004
14	IPOTHER	9	9	5	001001001001001001001001005
15	IPUNINSURED	9	9	6	001001001001001001001001006
16	OUTPAT	8	8	2	001001001001001001001002000
17	OPREVBASE	9	16	1	001001001001001001001002001
18	OPCAPITATED	9	16	2	001001001001001001001002002
19	OPNONCON	9	16	3	001001001001001001001002003
20	OPFFS	9	16	4	001001001001001001001002004
21	OPOTHER	9	16	5	001001001001001001001002005
22	OPUNINSURED	9	16	6	001001001001001001001002006
23	CDA	7	7	2	001001001001001001002000000
24	SYSADJCLEAR	8	23	1	001001001001001001002001000
25	CHARITYCARE	8	23	2	001001001001001001002002000
26	3RDPRTYCONT	8	23	3	001001001001001001002003000

**kaDargo** · 12-10-2014, 10:48 PM

Thanks for the attempt, shg, but that's not it. The workbook has the values I want Columns C thru K, which I entered manually. I want a formula to derive those values based on the depth of the SUMMARY_ACCT and its relative position to its parent.

**shg** · 12-11-2014, 01:20 AM

Which of the values in my example is wrong, and why?

**kaDargo** · 12-11-2014, 01:41 AM

I don't want a parent and child column.

Also, I'm not sure what this formula means =LOOKUP(9E+307, ROW(B$1:B2)/(B$1:B2 = B3-1)).

Why 9E+307?

**shg** · 12-11-2014, 06:24 PM

1. You do realize that columns C and D have formulas, they are not manual entries? You used 9 helper columns, I used two.

2. You can combine the formulas in columns C and D into a single ugly formula in col E if you like:

SUMMARY_ACCT	DEPTH
NETINCOME	1	001000000000000000000000000	C2: =LEFT(TEXT(B2, "000") & REPT(0, 27), 27)
NETINCB4TAX	2	001001000000000000000000000	C3: =LEFT(LEFT(INDEX(C:C, LOOKUP(9E+307, ROW(B$1:B2)/(B$1:B2 = B3-1))), 3*B3-3) & TEXT(COUNTIF(INDEX($B:$B, LOOKUP(9E+307, ROW(B$1:B2)/(B$1:B2 = B3-1))):$B3, B3), "000") & REPT(0, 27), 27)
OPERINC	3	001001001000000000000000000
TOTOPERREV	4	001001001001000000000000000
TOTNETREV	5	001001001001001000000000000
NETPATREV	6	001001001001001001000000000
GRSPATREV	7	001001001001001001001000000
INPAT	8	001001001001001001001001000
IPREVBASE	9	001001001001001001001001001
IPCAPITATED	9	001001001001001001001001002
IPNONCON	9	001001001001001001001001003
IPFFS	9	001001001001001001001001004
IPOTHER	9	001001001001001001001001005
IPUNINSURED	9	001001001001001001001001006
OUTPAT	8	001001001001001001001002000
OPREVBASE	9	001001001001001001001002001
OPCAPITATED	9	001001001001001001001002002
OPNONCON	9	001001001001001001001002003
OPFFS	9	001001001001001001001002004
OPOTHER	9	001001001001001001001002005
OPUNINSURED	9	001001001001001001001002006
CDA	7	001001001001001001002000000
SYSADJCLEAR	8	001001001001001001002001000
CHARITYCARE	8	001001001001001001002002000
3RDPRTYCONT	8	001001001001001001002003000
IPCDACAPOLD	8	001001001001001001002004000
OPCDACAPOLD	8	001001001001001001002005000

**shg** · 12-11-2014, 07:37 PM

A conventional WBS is simpler:

Row\Col	A	B	C	D
1	SUMMARY_ACCT	DEPTH
2	NETINCOME	1	1	C2: Input
3	NETINCB4TAX	2	1.1	C3: =INDEX(C:C, LOOKUP(9E+307, ROW(B$1:B2)/(B$1:B2 = B3-1))) & "." & COUNTIF(INDEX($B:$B, LOOKUP(9E+307, ROW(B$1:B2)/(B$1:B2 = B3-1))):$B3, B3)
4	OPERINC	3	1.1.1
5	TOTOPERREV	4	1.1.1.1
6	TOTNETREV	5	1.1.1.1.1
7	NETPATREV	6	1.1.1.1.1.1
8	GRSPATREV	7	1.1.1.1.1.1.1
9	INPAT	8	1.1.1.1.1.1.1.1
10	IPREVBASE	9	1.1.1.1.1.1.1.1.1
11	IPCAPITATED	9	1.1.1.1.1.1.1.1.2
12	IPNONCON	9	1.1.1.1.1.1.1.1.3
13	IPFFS	9	1.1.1.1.1.1.1.1.4
14	IPOTHER	9	1.1.1.1.1.1.1.1.5
15	IPUNINSURED	9	1.1.1.1.1.1.1.1.6
16	OUTPAT	8	1.1.1.1.1.1.1.2

**kaDargo** · 12-11-2014, 11:23 PM

That's what I'm looking for! Please explain the "9E+307"...what are you doing there?

**shg** · 12-12-2014, 12:28 AM

It returns the last row where the level is one less than the current level - i.e., the row of the parent.

Change parent/child to hierarchical address

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Re: Change parent/child to hierarchical address

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Parent child relationships(working out parent item) for each item

Parent Child Relationship

Vba code for Parent child

Parent Child Macro

Sorting Parent Child

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions