Extracting data from different cells and arranging them in different columns

[Ekta]

Dear All, 

I have a input file with data of many students, and The data of each student is stacked down with each student information arranged horizontally above the data, (attached is the input file) and i would like to extract all the student information and arrange them as separate columns as shown in the output file.

Input file could contain the data of 100 + students, please help me with the workflow to get the output using input file as input.

I tried a lot but failed:(

Thanks in advance

[TarunDeep]

 Hi, Your output file seems to be of similar structure. Do you need it in that format or do you want to use data in some other useful format? just wondering how you would be using your output file.

[Ekta]

Hi, thanks a lot for the reply..

my input file is having data of multiple people and the column starts from row row 10, above this we are having some information about the student and this gets repeated for each student .

i would like my output should be having columns capturing student information just like columns in row 10 not above it.

 i am bit confused how i can do it as the information is all stacked down , normally when i have multiple people data i create macro to clean the data and get a single output .

  i want my input format to convert into output format

[TarunDeep]

If structure of the sudent data is same for all the students, then you can esily parse the data by creating rowID and then converting it to table as required. 

I do not see consistancy in data-structure for all the students!

[wdavis]

Hi @Ekta 

I have started building out a workflow for you which hopefully you can continue to match all of your fields in the data set.

In order for you to structure the data in Alteryx, it will work best to create new columns for each of the metrics such as 'Query by' and 'Student No'. 

To do this, I have used a Multi-Row formula which allows us to identify a specific element of one Field and then copy it down until we hit the next student field.

One thing I have changed in your Input file is to check the box 'first row contains data' so that we are always referencing the first field and not a company name.

To continue this, you will need to build out more Multi-Row formulas for the rest of your headers and use the Filter tools to remove any Null rows that you do not require.

Let me know if this makes sense or you need any further assistance.

Thanks

Will

[TarunDeep · Accepted solution]

Hi @Ekta,

Please check the attached solution which gives the desired output. 

Just need to segregate and get the field names from those groups.

Please mark it a solution if it solves your problem. 

Thanks,

TD

[Ekta]

Thanks a lot 🙂