Help Restructure Data: Delete Unnecessary Rows, Rename Fields, Pivot Dynamic Fields

[paulwini]

Hi Everyone,

I have data that is structured in an incredibly unfortunate format. Here is a screenshot of this data as is (Excel file attached with input & desired output tabs):

As you can see it's really not pretty. My desired output is below:

However, I'm really struggling to achieve my desired output in Alteryx. I need to automate the generation of the desired output on a monthly basis and unfortunately the values in the "Name" field will change each time (e.g. Claire Gute may not be in next months data pull and instead a new person). Also, some month data pulls may contain more than 3 people and some may have less than 3 people, so the data transformations need to be very dynamic.

Some of my initial thoughts to achieve the desired output would be to dynamically duplicate any of the field names that are a person, then use a Select Records field to remove the top 2 rows, then change the field names to use the value in the top row as the field name. Then ultimately pivot the data to create the Name column.

Unfortunately, you can see in the input that each person has a Summary and Pull Date value in their column before their Order Date information starts.

Is there a way any of this is even possible in Alteryx? Any help to put me in the right direction would be greatly appreciated as I am so stumped right now!

Attached is the excel file and Alteryx workflow.

Thank you,

Paul

[chukleswk]

@paulwini  Please double check that you put in the sample file. The sample file you have doesn't look like the input that you have shown above.

[paulwini]

My apologies @chukleswk. The correct file is now attached to the post. Thank you

[chukleswk]

@paulwini is the Name always on the first row?

[paulwini]

@chukleswk Yes, the Name will always be in the first row

[chukleswk · Accepted solution]

@paulwini after some work.....I was able to get it to wok with the attached flow. I have gone through and updated it so that it was annotated as to what I was doing and I did add in a check to make sure that all of the column names were in the correct order. Please let me know if this works for you. Sorry for re-posting and updating it several times!

[paulwini]

@chukleswk This is absolutely incredible! I was able to get your solution to work for my actual data. Hats off to you as this is seriously impressive. I've been able to learn so much about Alteryx functionality just through your workflow alone. Many thanks!!

[chukleswk]

@paulwini glad to hear that it worked for you and you were able to learn something from it (that’s the most important thing)!