This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!
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
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.