Search code examples
excelvbatreeviewparent

Return the value in the first non-empty cell in the column directly to the left and going upward

I'm all new to VBA and have mostly been trying to modify code after recording macros, so it's all pretty basic and the approach might not be as elegant as some of the stuff I've seen on here. So here we go.

I have coded (by brute force) my data to be arranged like a CAD design tree view with parent products/assemblies and constituent sub-assemblies/parts.

Column E contains Level 0 top assembly Part Number Column F contains Level 1 items Part Number ... etc all the way to ... Column M containing Level 8 items Part Number

As an example, cell G112 contains ASSY1; cells H113 to H134 contain its constituent items.

I would like to display in a new column (i.e. Column O) the value of cell G112 (ASSY1) for each of its constituents. So O113 to O134 would show the value of G112. That would need to be applied to every single level of the assembly.

I'm not sure I'm making much sense do please have a look at the picture linked below, it speaks a thousand words. I've highlighted and colour-coded the result I would like in column O.

enter image description here

ADDENDUM - To clarify things: I don't know how else to explain my request but to post a simplified version of my original picture. SIMPLIFIED EXCEL TABLE

.CSV available here WeTransfer

Solution

  • Here's the answer I got from somewhere else if anyone is interested:

    Formula in Cell O3:

    =IF(C3=0,"N/A , ALREADY TOP LEVEL",INDEX(D$2:D2,AGGREGATE(14,6,(ROW(D$2:D2)-ROW(D$2)+1)/(C$2:C2=C3-1),1)))

    Copy/Paste down in every cell in column O