Vertical data import

[Elisevandergeest]

Hi Alteryx designers!

I have a problem with a dataset I am working with. At this point I have an Excel document with various sheets in the same format. The only difference is the amount of sub entities, some entities have 1 sub or some have for example 5. Every sheet has a format that looks like the example as shown below:

Entity Name A
	Sub entity A	sub entity B
Balance	1	2
P&L	3	4
Shares	2	1
Dividends	1	2

After joining the sheets, importing and cleaning the data, the dataset looked like this: 

RecordID	Entity Name	Accountnumber	F3	F4
1	a		Sub A	Sub B
2	a	Balance	1	2
3	a	P&L	3	4
4	a	Shares	2	1
5	a	Dividend	1	2
1	a		Sub C
2	b	Balance	2
3	b	P&L	3
4	b	Shares	1
5	b	Dividends	4
1	c		Sub D	Sub E
2	c	Balance	2	4
3	c	etc....	2	3

Alteryx added the RecordID. Every new sheet starts with 1. 

The output I would like to have is as follows:

Entity Name		A	A	B	C	C	C
		Sub A	Sub B	Sub C	Sub D	Sub E	Sub F
Balance		1	2	3	Etc..
P&L		3	4	2
Shares		2	1	1
Dividends		1	2	4

Unfortunately, when using the wildcard input macro, the data is imported vertically. So, the sheets are imported under each other instead of next to each other. What I would like to do is whenever a new entity starts on a new row, I want to move the data from the specific rows to new columns. 

The solution is probably transposing the data. However, I haven't found the correct way to do this. I hope you are able to help me!

If you need more information let me know.

Thanks you in advance. 

Elise

[Claje]

Hi,

I've attached a workflow that can accomplish this, although there may be simpler ways to do so.  To accomplish this I had to do some transposing of data, and then use a few cross tabs to create the values you were looking for, and then union'ed the data back together.

Hope this helps!

[ShawnM · Accepted solution]

@Elisevandergeest

If you want to be able to just add the files to a folder, and have the workflow read in all the files within that folder, then Dynamically build out the report with a single button push; I would suggest this method.  The attached Workflow uses the Directory tool to import the paths of all of your files you add to the folder.  Then the "Directory Consolidation" macro I threw together assembles them into one file, without you having to do this manually.  This will work, regardless of the number of Sub Columns.  Then the rest of the workflow will Dynamically generate your report, so when you add new data files to the folder, all you have to do is click the Run Button.  The report that the attached workflow generates isn't exactly a match to the one you showed, so you may or may not want to make an edit, but setting your workflow like this will take a lot of manual work out of your process.

[ShawnM]

The attached file contains the macro and the data files.

[Elisevandergeest]

Hi @ShawnM,

Thank you for your solution! Especially the crosstabs were a challenge, but it worked. I only had to manually make sure the rows were in the right order, but that's it. 

With kind regards,

Elise