Treeview Parents/Child - How to return the first non blank value located one column to the left of the source cell and n rows upward.

UlyssesFR

New Member
Joined
Jun 30, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi all, I'll try to be as clear as possible so please do not hesitate to ask for clarification.

I'm working with a text file data extracted from CATIA V5 that replicates the tree structure of an assembly with its components and subcomponents.

From that text file I arranged the source data in column A and B in such a way:
  • Column A contains the assembly level (0 being the top assembly all the way down to level 3)
  • Column B contains the component's part number or name

I have formatted that data in a more visual way in columns C to F to replicate a treeview structure:
  • So column C has the top assembly at level 0
  • Column D has all the sub-components at level 1
  • Column E, level 2 sub-components
  • Column F, level 3 sub-components
This formatting was achieved by using the following formula:
  • Cell C3 for example is =IF($C$2=VALUE(A3),B3,"DELETE")
  • This formula was then pasted across columns C to F cells.
  • I then run a VBA macro that clears the content of all cells containing the DELETE value to make sure all unused cells in columns C to F are truly empty.

Now... What I'm trying to achieve is to return the parent component of each component in their corresponding row (eee the attached picture to see the wanted results)

For example for SUB-ASSY 1 row 5; I would like cell G5 to return the value ASSY 1 (contained in cells B4 or D4)
Another example in PART 10 row 17; I would like cell G17 to return the value TOP ASSY (contained in cells B3 or C3)

I'm not sure if that could be achieved by just using the source data column A and B:

I'm thinking for example, cell G12 looks at the value of cell A12 (in that case 1) then looks upward for the first value that is equal to the value in cell A12 minus 1 (in that case 0) which would be in cell A3 and then returns the value in the cell adjacent to it (in that case cell B3 = TOP ASSY)

Or if this could be achieved using the formatted data in column C to F:

I'm thinking for example, cell G12 looks in range C12:F12 for a non-empty cell (in that case cell D12), then goes one column to the left (Column C) and n rows upward until it meets the first non-empty cell (in that case cell C3) and returns its value (TOP ASSY)

Unfortunately, I've been looking at this for a while now and I'm no closer to finding a solution :(

I hope you can help me, thank you for your time :)

Kind regards,
 

Attachments

  • TREEVIEW.jpg
    TREEVIEW.jpg
    60.4 KB · Views: 5

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

UlyssesFR

New Member
Joined
Jun 30, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Something like this...
Book4
DEFGHI
1DATA TYPE0123
20Data1Data1   
31Data2 Data2  
42Data3  Data3 
53Data4   Data4
60Data5Data5   
70Data6Data6   
80Data7Data7   
93Data8   Data8
103Data9   Data9
112Data10  Data10 
Sheet2
Cell Formulas
RangeFormula
F2:I11F2=IF($D2=F$1,$E2,"")

Ok it seems I have overcomplicated my question by detailing how I got there.

I already have the data you are showing in your table.

Maybe it'll make more sense if I use the Directory/Sub-Directory analogy.

What formula in column G would allow me to display the parent directory as shown in the table below?

TREEVIEW.xlsx
ABCDEFG
1LEVELDIRECTORYLEVELPARENT DIRECTORY
20123
30ROOT DIRECTORYROOT DIRECTORY   
41SUB-DIRECTORY A SUB-DIRECTORY A  ROOT DIRECTORY
52SUB-DIRECTORY AA  SUB-DIRECTORY AA SUB-DIRECTORY A
63SUB-DIRECTORY AA1   SUB-DIRECTORY AA1SUB-DIRECTORY AA
73SUB-DIRECTORY AA2   SUB-DIRECTORY AA2SUB-DIRECTORY AA
83SUB-DIRECTORY AA3   SUB-DIRECTORY AA3SUB-DIRECTORY AA
93SUB-DIRECTORY AA4   SUB-DIRECTORY AA4SUB-DIRECTORY AA
102SUB-DIRECTORY AB  SUB-DIRECTORY AB SUB-DIRECTORY A
112SUB-DIRECTORY AC  SUB-DIRECTORY AC SUB-DIRECTORY A
121SUB-DIRECTORY B SUB-DIRECTORY B  ROOT DIRECTORY
132SUB-DIRECTORY BA  SUB-DIRECTORY BA SUB-DIRECTORY B
143SUB-DIRECTORY BA1   SUB-DIRECTORY BA1SUB-DIRECTORY BA
153SUB-DIRECTORY BA2   SUB-DIRECTORY BA2SUB-DIRECTORY BA
163SUB-DIRECTORY BA3   SUB-DIRECTORY BA3SUB-DIRECTORY BA
171SUB-DIRECTORY C SUB-DIRECTORY C  ROOT DIRECTORY
181SUB-DIRECTORY D SUB-DIRECTORY D  ROOT DIRECTORY
Book1 (2)
Cell Formulas
RangeFormula
C3:F18C3=IF(C$2=$A3,$B3,"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Try this, copied down.

20 07 01.xlsm
ABCDEFG
1LEVELDIRECTORYLEVELPARENT DIRECTORY
20123
30ROOT DIRECTORYROOT DIRECTORY 
41SUB-DIRECTORY ASUB-DIRECTORY AROOT DIRECTORY
52SUB-DIRECTORY AASUB-DIRECTORY AASUB-DIRECTORY A
63SUB-DIRECTORY AA1SUB-DIRECTORY AA1SUB-DIRECTORY AA
73SUB-DIRECTORY AA2SUB-DIRECTORY AA2SUB-DIRECTORY AA
83SUB-DIRECTORY AA3SUB-DIRECTORY AA3SUB-DIRECTORY AA
93SUB-DIRECTORY AA4SUB-DIRECTORY AA4SUB-DIRECTORY AA
102SUB-DIRECTORY ABSUB-DIRECTORY ABSUB-DIRECTORY A
112SUB-DIRECTORY ACSUB-DIRECTORY ACSUB-DIRECTORY A
121SUB-DIRECTORY BSUB-DIRECTORY BROOT DIRECTORY
132SUB-DIRECTORY BASUB-DIRECTORY BASUB-DIRECTORY B
143SUB-DIRECTORY BA1SUB-DIRECTORY BA1SUB-DIRECTORY BA
153SUB-DIRECTORY BA2SUB-DIRECTORY BA2SUB-DIRECTORY BA
163SUB-DIRECTORY BA3SUB-DIRECTORY BA3SUB-DIRECTORY BA
171SUB-DIRECTORY CSUB-DIRECTORY CROOT DIRECTORY
181SUB-DIRECTORY DSUB-DIRECTORY DROOT DIRECTORY
Parent
Cell Formulas
RangeFormula
G3:G18G3=IF(A3=0,"",INDEX(B$2:B2,AGGREGATE(14,6,(ROW(B$2:B2)-ROW(B$2)+1)/(A$2:A2=A3-1),1)))
 

UlyssesFR

New Member
Joined
Jun 30, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Welcome to the MrExcel board!

Try this, copied down.

20 07 01.xlsm
ABCDEFG
1LEVELDIRECTORYLEVELPARENT DIRECTORY
20123
30ROOT DIRECTORYROOT DIRECTORY 
41SUB-DIRECTORY ASUB-DIRECTORY AROOT DIRECTORY
52SUB-DIRECTORY AASUB-DIRECTORY AASUB-DIRECTORY A
63SUB-DIRECTORY AA1SUB-DIRECTORY AA1SUB-DIRECTORY AA
73SUB-DIRECTORY AA2SUB-DIRECTORY AA2SUB-DIRECTORY AA
83SUB-DIRECTORY AA3SUB-DIRECTORY AA3SUB-DIRECTORY AA
93SUB-DIRECTORY AA4SUB-DIRECTORY AA4SUB-DIRECTORY AA
102SUB-DIRECTORY ABSUB-DIRECTORY ABSUB-DIRECTORY A
112SUB-DIRECTORY ACSUB-DIRECTORY ACSUB-DIRECTORY A
121SUB-DIRECTORY BSUB-DIRECTORY BROOT DIRECTORY
132SUB-DIRECTORY BASUB-DIRECTORY BASUB-DIRECTORY B
143SUB-DIRECTORY BA1SUB-DIRECTORY BA1SUB-DIRECTORY BA
153SUB-DIRECTORY BA2SUB-DIRECTORY BA2SUB-DIRECTORY BA
163SUB-DIRECTORY BA3SUB-DIRECTORY BA3SUB-DIRECTORY BA
171SUB-DIRECTORY CSUB-DIRECTORY CROOT DIRECTORY
181SUB-DIRECTORY DSUB-DIRECTORY DROOT DIRECTORY
Parent
Cell Formulas
RangeFormula
G3:G18G3=IF(A3=0,"",INDEX(B$2:B2,AGGREGATE(14,6,(ROW(B$2:B2)-ROW(B$2)+1)/(A$2:A2=A3-1),1)))

This is amazing and is exactly what I am looking for.

Unfortunately, in my attempt to simplify my request I seem to halve altered/omitted the nature/source of some of the cells/ranges you are using in the formula.

As a result, I am getting a #NUM error upon pasting the formula.

Please find the actual worksheet I'm working from below:

N254L4001-000_CATIA V5_TREE FORMATING FROM LISTING REPORT_20200701_0940.xlsm
ABCDEFGHIJKLMN
2RAWTRIMMEDLVLPART NUMBER012345678USED IN
30 N254L4001-000 0 N254L4001-0000N254L4001-000N254L4001-000#NUM!
4 1 N254L4002-000 1 N254L4002-0001N254L4002-000N254L4002-000#NUM!
5 2 N254L4003-000 2 N254L4003-0002N254L4003-000N254L4003-000#NUM!
6 3 N254L4055-000 3 N254L4055-0003N254L4055-000N254L4055-000#NUM!
7 4 N254L5002-200 4 N254L5002-2004N254L5002-200N254L5002-200#NUM!
8 4 N254L5029-200 4 N254L5029-2004N254L5029-200N254L5029-200#NUM!
9 3 N254L4057-000 3 N254L4057-0003N254L4057-000N254L4057-000#NUM!
10 4 N254L5003-200 4 N254L5003-2004N254L5003-200N254L5003-200#NUM!
11 4 N254L5030-200 4 N254L5030-2004N254L5030-200N254L5030-200#NUM!
12 4 N254L5036-200 4 N254L5036-2004N254L5036-200N254L5036-200#NUM!
13 4 N254L5036-200 4 N254L5036-2004N254L5036-200N254L5036-200#NUM!
14 4 N254L5036-200 4 N254L5036-2004N254L5036-200N254L5036-200#NUM!
15 3 N254L4058-000 3 N254L4058-0003N254L4058-000N254L4058-000#NUM!
16 4 N254L5005-200 4 N254L5005-2004N254L5005-200N254L5005-200#NUM!
17 4 N254L5036-200 4 N254L5036-2004N254L5036-200N254L5036-200#NUM!
18 4 N254L5036-200 4 N254L5036-2004N254L5036-200N254L5036-200#NUM!
19 4 N254L5036-200 4 N254L5036-2004N254L5036-200N254L5036-200#NUM!
20 4 N254L5036-200 4 N254L5036-2004N254L5036-200N254L5036-200#NUM!
21 4 N254L5103-200 4 N254L5103-2004N254L5103-200N254L5103-200#NUM!
22 4 N254L5296-200 4 N254L5296-2004N254L5296-200N254L5296-200#NUM!
23 4 N254L5299-200 4 N254L5299-2004N254L5299-200N254L5299-200#NUM!
24 3 N254L4059-000 3 N254L4059-0003N254L4059-000N254L4059-000#NUM!
FORMATED
Cell Formulas
RangeFormula
B3:B24B3=TRIM(A3)
C3:C24C3=LEFT(B3,1)
D3:D24D3=RIGHT(B3,LEN(B3)-2)
E3E3=IF($E$2=VALUE(C3),D3,"DELETE")
F4F4=IF($F$2=VALUE(C4),D4,"DELETE")
G5G5=IF($G$2=VALUE(C5),D5,"DELETE")
H6,H24,H15,H9H6=IF($H$2=VALUE(C6),D6,"DELETE")
I16:I23,I10:I14,I7:I8I7=IF($I$2=VALUE(C7),D7,"DELETE")
N3:N24N3=IF(C3=0,"",INDEX(D$2:D2,AGGREGATE(14,6,(ROW(D$2:D2)-ROW(D$2)+1)/(C$2:C2=C3-1),1)))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows
Your values in column C are text, not numerical, as formula was expecting.
Can you alter your C3 formula to this & copy down?
=LEFT(B3,1)+0

Then your existing formulas in column N should return the correct results I think.

Further, if you make that change, then you shouldn't need the VALUE() function in your columns E:I formulas.
In E3, it would only need ..
=IF($E$2=C3,D3,"DELETE")
.. which is more like what you had in post #3.

BTW, why are you using "DELETE" in that formula and not just
=IF($E$2=C3,D3,"")
 

UlyssesFR

New Member
Joined
Jun 30, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Your values in column C are text, not numerical, as formula was expecting.
Can you alter your C3 formula to this & copy down?
=LEFT(B3,1)+0

Then your existing formulas in column N should return the correct results I think.
Indeed you are right, flawless, perfect! Thank you so much!!!

Now I'm going to stare at this function for weeks and try to dissect it in order to understand how it works, but INDEX and AGGREGATE are so obscure to me.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,780
Office Version
  1. 365
Platform
  1. Windows
You're welcome. :)

Did you see the edits I added that perhaps happened after you initially viewed my previous post?
 

UlyssesFR

New Member
Joined
Jun 30, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
You're welcome. :)

Did you see the edits I added that perhaps happened after you initially viewed my previous post?

I had not seen the edits.

I'm using DELETE in the formula =IF($E$2=C3,D3,"DELETE") because I needed the cell to be truly empty instead of containing a ""

So I have a bit of VBE that clears the content of all cells within my range afterward.

VBA Code:
' Clear Content of Cells containing DELETE in the Tree View Range'
    For Each cell In ActiveSheet.Range("A1", ActiveCell.SpecialCells(xlLastCell))
        If cell.Value = "DELETE" Then
           cell.ClearContents
        End If
    Next cell

This allows my tree to look like this (the text carries into the next column because the adjacent cell is now truly empty):
tree text full.png

Instead of like this (the text stops as far as the width of the column goes, because the next cell contains "" and thus is not truly empty):
tree text truncated.png


As for removing the VALUE(), thanks, I have removed it and nothing collapsed :)

Thanks again!
 

UlyssesFR

New Member
Joined
Jun 30, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
VBA, not VBE...

Not sure how to edit a post once it's been posted...
 

Watch MrExcel Video

Forum statistics

Threads
1,111,854
Messages
5,541,460
Members
410,546
Latest member
htran4
Top