Stacking Data in specific order

Hello,

I was hoping someone might be able to help me with a formula. I have attached a file with an example starting in cell G6 through H23. Columns A through E have the data. I am trying to stack that data into one column in a specific order. I have tried repeat formulas, if(and) formulas and a number of other options but I keep getting stuck. What I need is as follows:

1. the very first row should be G6=#Root and H6 = to the value in column A.
2. the second row should be G7 = Top from column A and H7 = Toyota, however, later on down the list it should be Lexus whenever that field in column B changes to Lexus
3. the third row should be G8 = Toyota from Column B and H8 = 4Runner in Column C, however that should change whenever there is a change in column C.
4. The fourth row should be G9 = 4Runner and H9 = to 4Runner_Gas and that should change when there is a change in column D
5. The fifth row should be G10 = 4Runner_Gas and H10 = the code in column E
6. The sixth row should repeat item 5 above but with the new code in column E.
7. The rows should continue repeating item 6 for every change in column E however when there is a change in column C it should go to item 4 above.

Hopefully I am making sense. It needs to be in a specific order as shown below.

#Root Top
Top Toyota
Toyota 4Runner
4Runner 4Runner_Gas
4Runner_Gas XXXX_GRN280LGKAGKA
4Runner_Gas 8642_GRN280LGKAGKA
4Runner_Gas 8645_GRN280LGKAGKA
4Runner_Gas 8646_GRN280LGKAGKA
4Runner_Gas 8648_GRN280LGKAGKA
4Runner_Gas 8649_GRN280LGKAGKA
Toyota 86
86 86_Gas
86_Gas 6252_ZN6HYC7
86_Gas XXXX_ZN6HYC7
86_Gas 6253_ZN6HYC8
86_Gas XXXX_ZN6HYC8
86_Gas 6254_ZN6HYH7
86_Gas XXXX_ZN6HYH7
Top Lexus
Lexus ES
ES ES_Gas
ES_Gas XXXX_AXZA15LBEZGBA
ES_Gas B11D1T_AXZA15LBEZGBA
ES_Gas XXXX_GSZ10LBEZGBA
ES_Gas BZ1B1T_GSZ10LBEZGBA
ES_Gas XXXX_GSZ10LAEZGBA
ES_Gas 9000_GSZ10LAEZGBA
ES_Gas 9005_GSZ10LAEZGBA
ES_Gas 9090_GSZ10LAEZGBA




Capture.JPG

You are awesome. That is exactly what I want with one exception. I need two columns. See your file attached with the second column added to it. Also if I need to replicate this over and over what would I need to do? Can I just kick off the query or a macro to build it?

Hello,

Any thoughts on the additional column I need?