How can I create a parent control based on the text position

[felipeprevente]

Hi Experts,

I have exported a file containing a large amount of data where its origin has a parent-child structure. But once you open the file, this structure is shown as a single column and the parent-child relationship can be only determined by the visual position of the text inside the cells. Things get worse when going into the cells and you see that there are no tabulations or any other differences to make the position different between them.

Looking at the pictures below, you see there's a clear parent-child relationship: Setup > General > Users, but this is only visual, you can't extract any space or tabulation from these cells to try to organize it in a helper column.

I was wondering if there is any trick to set a value in a helper column like the picture below because I have been doing this manually and it's taking forever to get it done.

[#2]

@felipeprevente  yes you could have that 'helper' column with tree level and create another column something like:

=REPT(" ",B1)&A1

or =REPT(" ",3*B1) & A1 to create 3 spaces for each indent level or just put 3 spaces in the "   "

BTW you can also GROUP rows in excel:

so it will NOT create the spaces but on the left are the 'groupings' that you can easily collapse and expand.  You can find the control in Data and under 'outline' as shown in the above ribbon all the way to the right.

[#3]

Hi mtarler, thanks for your response.

Actually, that "space" already exists. I know that I can use the group feature to collapse/expand but this is not my goal. Given that I have the spaces to determine the parent control visually, at least, I'm still having to do this manually, because there is no practical difference between A2, A3 and A4 in my example.

I don't know if I'm being clear on explaining this

[#4 · Accepted solution]

@felipeprevente so if you have the column WITH the spaces the attached file does both give the tier level and has a macro to auto create those groups if you want

[#5]

Thanks, mtarler! it helped me a lot