Menu Hierarchy - list all security that applies to each row

[kristinfreitas]

Hi.  I posted something similar yesterday, but I have learned a little more about my data set and am trying again.  I have attached an example of the data.  I need to create a new field (column D) that will include the Security (column C) for that row plus anything above in the hierarchy.  So for Level 3 Review Industry Example Application, I need the new column to list Review Industry Example Application, Review Trail Reporting Guide (Level 2), and Review Trail Guide (Level 1).  I started building this with a multi-row formula tool but there are so many combinations (source file is over 1 million rows) that I think I may need help building an iterative macro.

[Qiu]

@kristinfreitas 
It seems that I get the similar results as you inputted in the Excel.
I interprete that by "anything above in the hierarchy" means the "Security" value just above the current hierarchy,

For Row#7 in my snapshot below, it will the value from #5 and #1.

[kristinfreitas]

Thank you so much! This looks good so I will try it with my large dataset!

[kristinfreitas]

@Qiu 

The logic isn't working quite right but I really do appreciate the workflow!  Record 12 should be Check Application instead of Check Middle Tier along with Check Reports and Check Tracking (those 2 are there). I will see if I can make some changes though to get this working.  Thanks again!!

[Qiu]

@kristinfreitas 
Thanks for the feedback.

I beleve you are referring Row#13, of which value for "Security" is "Check Middle Tier", so I dont get why it should be "Check Application"?

[kristinfreitas]

@Qiu I am looking at row #12.  The Concat_Right_Security field you created should match the Created Field - All Security in the same row.  But it matches row #13 instead.  Thanks for looking into this!!