Preparing Excel Data

[ACES2019]

I'm trying to prepare an excel file for data analysis that requires the first three lines to be manipulated. First Line contains a year that I need merged with the third line. The second line needs to be removed all together. I can remove the second line and transpose lines 1 and 3 then write a formula to concatenate the two columns but can't get past that. This new row should become the header for the rest of the data.

[DataNath]

Hey @ACES2019, how does this look? The bottom stream is the main focus here where we isolate lines 1 & 3, transpose them and then concatenate them to create the new headers, before looking for the old headers in the original data set and replacing them with the newly-created ones. The top stream literally just skips the first 3 lines to discard the old 'header' rows from the data:

I wasn't sure you wanted to do with the first 2 fields i.e. the Alabama Limited/Kentucky General etc but these should be easy enough for you to clean up as you wish.

[IraWatt]

Hey @ACES2019,

Is this the kind of output your looking for?

If it is I can give you a walkthrough of how the steps work.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[ACES2019]

Exactly what I'm looking for. I can rename the first two columns. I'm going to download the workflow and test.  Thank you.

[IraWatt]

Great to hear @ACES2019, one thing to note on the data input is I selected that the first row contains data:

[DataNath]

Let us know how you get on @ACES2019. One thing to note is that, the reason I define a range in my Input Data is, without it, Alteryx doesn't start importing from your first 2 fields:

You can either put a name in them within Excel before running the flow, or define a range like above. Doing the former will also remove the name to do the final rename within Alteryx.

[ACES2019]

I have a new one for you using the same data. I no longer need to merge row 1 and 3 into a header line. I need Line 3 to become the header for the data set. However, the headers would be duplicated for as many years. In the end the headers I need are:

F1 (rename to State), F2 (rename to CourtID), Year, Begin Pending, Income Cases, Rate per 100k Population, Outgoing Cases, Ending Pending, Clearance Rate, Percent of Outgoing Cases with SRLs, Dispositions, Bench Trial Rate, Jury Trial Rate, Total Population.

After that the headers start repeating themselves.

Thank you,

[DataNath]

Hey @ACES2019 that's fine, just a very slight amendment to the previously offered solutions. However, there is an issue (or it might not be, unsure on your case!) that - in Alteryx - you can't have fields with the same name and so when the fields begin to repeat, they'll be called X2, X3, X4 & Y2, Y3, Y4 etc.. New amended flow attached where you can see what I mean:

[ACES2019]

I have amended the workflow but think there might be an easier way.

[DataNath]

@ACES2019 did you test the workflow above? Was just a slight tweak of the original: