Hello experts,
I have data that looks like the following and I would like to split multiple fields to columns at the same time. Bearing in mind that some records would require the split to be into 2 columns, some to 3 and so on . I illustrate below
StudentID | Name | Title | position | Degree | DegreeDate | Degree University |
1 | a | x | y | BS, MSc, PhD | 2012,2014,2020 | AUC, AUC, NYU |
2 | b | w | u | BS, MSc | 2008,2012 | UT, UVA |
3 | c | m | n | BS | 2002 | PennState |
I want the output to look like this
StudentID | Name | Title | position | Degree_1 | Degree_2 | Degree_3 | DegreeDate | DegreeDate | DegreeDate | Degree University_1 | Degree University_2 | Degree University_3 |
1 | a | x | y | BS | MSc | PhD | 2012 | 2014 | 2020 | AUC | AUC | NYU |
2 | b | w | u | BS | MSc | 2008 | 2012 | UT | UVA | |||
3 | c | m | n | BS | 2002 | PennState |
Solved! Go to Solution.
Hey @Noorakroush
In these circumstances its always easier to deal with the data if you transpose it first:
Hi @Noorakroush
See attached.
Essentially what you need to do is create a shared column for each set of values and split the columns to rows.
Once you've done that it is just a matter of identifying the number of new column headers and you're all set.
Best,
Michael