community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

Column Data Indenting and Organization Structure

Meteor

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

Alteryx Certified Partner
Alteryx Certified Partner

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

 

 

Alteryx Certified Partner
Alteryx Certified Partner

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

Meteor

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.

Labels