#  Other Applications & Softwares  > Outlook Formatting & Functions >  >  Need  a Fill Down Function For a Multiple-Variable Hierarchy

## stevensimon10482

Hi - 

I've asked a version of this question previously, but my new example is more complicated and has to account for a scenario where I am filling in hierarchical categories in a column, but different parts of the hierarchy may be missing in some cases. 

In the attached sheet, Columns A:C are what I already have, Columns D:F are what I am trying to create. You'll see that there is a basic FUNCTION -> SERIES -> GROUP -> Title hierarchy, but in some cases there is "No Group," and in one case there is also "No Series." 

How do I construct a formula that achieves this result. Previously I used a version of this:  =IF(A2="**",C2,E1) but I've expanded to a four-level hierarchy and can't figure out how to handle missing steps in the hierarachy, all while creating the asterisks in the D:F columns.

----------


## leelnich

Sorry, Steven, I'm not seeing any clear hierarchy, and I have no idea how you populated columns D:F. Are the stars some sort of code? I don't get it...

----------


## stevensimon10482

Sorry leelnich. 

What I mean by hierarchy is that every "Title" is part of a "Group," and every Group is part of a "Series," and every Series is part of a "Function." The asterisks are a sort of code, yes: three for functions, two for series, one for groups, and zero for titles. However, there are a couple series with no group, and one instance of a function without a series. 

I constructed columns D:F manually - I'm trying to figure out what formula will help me create the results in those columns.

----------


## leelnich

Ok, a pattern is emerging...

----------


## leelnich

Paste these in row 2 and copy down:

D2 =CHOOSE(LEN($A2)+1,D1,$C2,"*","*")
E2 =CHOOSE(LEN($A2)+1,E1,E1,$C2,"**")
F2 =CHOOSE(LEN($A2)+1,F1,F1,F1,$C2)

----------


## stevensimon10482

leelnich! thanks so much, this is great. 

One updated question - 

On the first instance of the hierarchy, what if I wanted it to display the appropriate asterisk from Column A, rather than the text of the category? 

For example, in this sheet, on D4, it says "Agricultural Production Group" - what if I wanted it to say "*"? And instead of E3 saying "Agriculture Series," what if I wanted it to say "**"? And in F2, what if I wanted it to say "*" instead of "Land Function"? 

I've played around with the LEN part of your function and I can't quite make it work - it seems like to get the asterisk result, it has to be static, but obviously to get the subsequent result it needs the "+1" you've added. Any thoughts? 

Thanks again - this is sufficient for my purposes even if you don't have a solution to my additional question.

----------


## leelnich

Try this:

D2
=IF($A2>="*","*",IF($A1="*",$C1,D1))

E2
=IF($A2>="**","**",IF($A1="**",$C1,E1))

F2
=IF($A2="***","***",IF($A1="***",$C1,F1))

----------

