Column Data Indenting and Organization Structure

Hi all,

I have an issue with the attached file format. Essentially, I am trying to construct an organization structure using multi-row formulas.

I currently have data that looks like the following:

Department Name 1 Employee Name 1

Employee Name 2

Department Name 2 Employee Name 3

Employee Name 4

Essentially, I know that Employee 2 reports to Employee 1 and they are within Department 1. Additionally, I know that Employee 4 reports to Employee 3 and they are within Department 2.

However, using the input tool with an Excel document, these indents are not showing up. If these indents were retained using the input tool, I would be able to use a multi-row formula to fill in the department names, and then create another multi-row formula that evaluated the number of space characters (or indents) between people in the same department. Is there any solution to this problem of formatting, or is this more of a manual analysis?

Right now, I'm think of printing these files to PDF then using Acrobat to revert back to excel files, since the columns may change.

Please let me know if you have any ideas. Thank you!

Jesse

Hierarchical Example.xlsx

Input

Best Practices

Accepted answers

BenMoss

Perhaps you can run the VBA script outlined in this post prior to bringing it into Alteryx: http://professor-excel.com/how-to-return-the-indentation-of-a-cell-in-excel/

Ben

All comments

BenMoss

No type of cell formatting shows when pulling the data into Alteryx so i'm not sure what you can do here.

Can you replace the indent formatting with spaces? Thus allowing you to perform the task.

Ben

BenMoss

Perhaps you can run the VBA script outlined in this post prior to bringing it into Alteryx: http://professor-excel.com/how-to-return-the-indentation-of-a-cell-in-excel/

Ben

Jesse89

Hi Ben,

This was definitely an Excel-based fix. I created a VBA user defined function in Excel using the following code:

Function indenture(r As Range) As Integer

indenture = r.IndentLevel

End Function

This allowed me to use =indenture(A2) to retrieve a cell's indent value. 0 being no indents, 1 being one indent, etc.

I used these integers to create a multi-row formula in Alteryx that was grouped by the department employees worked at. The person who created this report had created it manually leading to a variation of both leading spaces and indents. I created a formula in excel based on the trim and len functions to count the leading spaces too. It was a real nightmare, but thank you for the idea of looking at VBA.

Jesse.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703