Splitting & transforming data

[suds144]

Hi Everyone,

I am new to using Alteryx and would be grateful for your kind guidance.

I need to change the format of data in an excel file which contains information of student societies (e.g. basketball)  and student IDs. I attach Dummy data in the format I currently have the information in. 

The data should look like this (ideally).

Grateful for any support and guidance. 

[atcodedog05]

Hi @suds144 

You can do this with help of transpose like below

Hope this helps : )

[Christina_H]

You need a transpose tool for this:

Then it's just a case of renaming the columns and adding the society type.  Assuming you have more than just sports societies, I would link to a mapping table with Society Type and Society Name columns.

[suds144]

Hi, thanks so much. This is super useful. I just tried it and have some questions.

How can I get the data categories to stay together? For example I want all the students who were/are part of the basketball society to be lumped together.

Some of the same students took part in different societies and there was an unequal number of students in some of the societies. For example:

I'm assuming Alteryx won't remove the duplicates? In running the proposed solution blanks appeared in the output file. Does this have something to do with the unequal number of students in each society?

Ideally I would like the output to look like this

Thank you!

[atcodedog05 · Accepted solution]

Hi @suds144 

Here is how you can do it.

Workflow:

Hope this helps : )

[suds144]

Thank you so much! I just ran it and it came together so nicely.

[suds144]

I know we reached a solution for this but I have another question in regards to this data set.

What if information relating to each society +student ID is on a different excel sheet (in the same document). Is there a way alteryx can split this data so it is on the same excel sheet?

Thank you!

[atcodedog05]

Hi @suds144 

Can you provide some sample input and expected output It will help us get a better understanding of the usecase.

We will be happy to help : )

[suds144]

So currently my output file looks like this: Two data sets in one excel File (one in each sheet). The first one relates to the sports society. The second one relates to Academic societies

 

How I want my output to look like:

Thank you

[atcodedog05]

Hi @suds144 

can you provide the excel files.