Count text in cell and find next cell with one more

[Gimics]

Hey team!

I'm working with some hierarchical data output from our ERP system's vanilla reporting software and my finance team wants a low maintenance excel spreadsheet that identifies all of the levels of a hierarchy as attributes / dimensions. Our hierarchies are built as parent/child combos, not as attribute/level rolls ups, so we don't really have a table that's stored with each individual level of the hierarchy for each GL account.

I would love to land on a formula that I can use to identify the levels of the hierarchy for each GL. The report outputs the hierarchy cascading with the hierarchy nodes below each GL account. The nodes are identified with leading asterisk characters (I know, that makes things a little more challenging), that increase in quantity as we get closer to the top of the hierarchy.

Here's an example of what the report spits out:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Hierarchy[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]GL1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GL2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GL3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]***Gross Slaes[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]GL4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]GL5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]***Discounts[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]****Net Sales[/TD]
[/TR]
</tbody>[/TABLE]


What I am working towards is this:
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Hierarchy[/TD]
[TD="align: center"]Level 1[/TD]
[TD="align: center"]Level 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]GL1[/TD]
[TD]***Gross Sales[/TD]
[TD]****Net Sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GL2[/TD]
[TD]***Gross Sales[/TD]
[TD]****Net Sales[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GL3[/TD]
[TD]***Gross Sales[/TD]
[TD]****Net Sales[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]***Gross Sales[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]GL4[/TD]
[TD]***Discounts[/TD]
[TD]****Net Sales[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]GL5[/TD]
[TD]***Discounts[/TD]
[TD]****Net Sales[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]***Discounts[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]****Net Sales[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Level 1 is relatively simple, I just needed to find the next value with an "*", or the parent to the GL accounts. I used a formula like this (note, I'm trimming everything because my actual data has a ton of unneeded spaces):

=IF(LEFT(TRIM($A2),2)="GL",INDEX($A2:$A$8,MATCH("~*",LEFT(TRIM($A2:$A$8),1),0),),"")

Level 2, however, I think I need to find the length of "*"s in a cell, then find the next cell that's 1 "*" longer.

I know I can use a "len() - len(substitute)" formula to find how many "*"s there are, like this:

=LEN(TRIM($B2))-LEN(SUBSTITUTE(TRIM($B2),"*",""))

... and could add "+1" to this to know the number of "*" I need to lookup, but I'm at a loss for what formula to use now to find the next record with +1 "*"s from the value I've found.


I am hoping to figure this out with formulas as the maintenance for the eventual users will be much easier than a macro. The current hierarchy is 1,000 records long and there are 10 different levels in some places, so this would be a very tedious process to replicate manually whenever there are hierarchy updates.

Thanks in advance!!

 

[Marcelo Branco]

I would create a helper row. 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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Hierarchy​

[/td][td]

Level 1​

[/td][td]

Level 2​

[/td][/tr]

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

2​

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

Chars​

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

3​

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

4​

[/td][/tr]

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

3​

[/td][td]

GL1​

[/td][td]

***Gross Sales​

[/td][td]

****Net Sales​

[/td][/tr]

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

4​

[/td][td]

GL2​

[/td][td]

***Gross Sales​

[/td][td]

****Net Sales​

[/td][/tr]

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

5​

[/td][td]

GL3​

[/td][td]

***Gross Sales​

[/td][td]

****Net Sales​

[/td][/tr]

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

6​

[/td][td]

***Gross Sales​

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

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

7​

[/td][td]

GL4​

[/td][td]

***Discounts​

[/td][td]

****Net Sales​

[/td][/tr]

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

8​

[/td][td]

GL5​

[/td][td]

***Discounts​

[/td][td]

****Net Sales​

[/td][/tr]

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

9​

[/td][td]

***Discounts​

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

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

10​

[/td][td]

****Net Sales​

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

Helper row in gray

Array formula in B3 copied across and down
=IF(LEFT(TRIM($A3),2)="GL",INDEX($A3:$A$100,MATCH(REPT("~*",B$2)&"*",TRIM($A3:$A$100),0),),"")
Ctrl+Shift+Enter

Hope this helps

M.

 

[Gimics]

Thanks Marcelo,

Unfortunately, there are varying levels within the hierarchy for all GL accounts. That is, not all GL accounts belong to the lowest level (ex. Level 1); their first level might actually have 5 "*"s or more.

This is why I was working towards using the number of "*"s in column B as the count.

I honestly didn't know about the =REPT() formula though - that solved my problems, I used it to repeat the number of "*"s based on the number I had found using my formula in the first post. With the full data set, this is what my actual formulas look like:

Parent formula (would be in column "B", where column C in the formula is actually column "A" in my example):

{=IFERROR(IF(ISNUMBER(--LEFT(TRIM($C3),1)),INDEX($C3:$C$757,MATCH("~*",LEFT(TRIM($C3:$C$757),1),0),),INDEX($C3:$C$757,MATCH(REPT("~*",(LEN(TRIM($C3))-LEN(SUBSTITUTE(TRIM($C3),"*","")))+1),LEFT(TRIM($C3:$C$757),(LEN(TRIM($C3))-LEN(SUBSTITUTE(TRIM($C3),"*","")))+1),0),)),"")}


Levels formula (would be in each column following "C", where column C in my formula is actually column "A" in my example, and column "C" in my formula is actually column "B" in my formula):

{=IF(F3="","",IFERROR(INDEX($C3:$C$757,MATCH(REPT("~*",(LEN(TRIM(F3))-LEN(SUBSTITUTE(TRIM(F3),"*","")))+1),LEFT(TRIM($C3:$C$757),(LEN(TRIM(F3))-LEN(SUBSTITUTE(TRIM(F3),"*","")))+1),0),),""))}


Each is an array formula (Ctrl+Shift+Enter) to appropriately evaluate the left(,N) characters, and trim the hierarchy values before evaluating.

This worked!

 

[Marcelo Branco]

I'm glad you found a solution. And also for having helped a little with the REPT function
Good job

M.