Menu Hierarchy - list all security that applies to each row

Question

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.

Menu Hierarchy Ex.xlsx

kristinfreitas · Answer

@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!!

Qiu · Answer

@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 · Answer

@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!!