jbowers221
New Member
- Joined
- Feb 16, 2018
- Messages
- 1
Hi all--
I need help on how to transpose a large set of data which contains: year, term, student id, and sports. It gets complicated because there are some students that played multiple sports each term.
Here's what the raw data looks like:
<tbody>
</tbody>*term 10 = fall; 20 = spring
*year = academic year
I want the format to look like this:
<tbody>
</tbody>
I tried the basic transpose method but it does not look like the one I have on the top. What kind of formula do I have to use for this?
Thanks in advance for your help!
jbowers
I need help on how to transpose a large set of data which contains: year, term, student id, and sports. It gets complicated because there are some students that played multiple sports each term.
Here's what the raw data looks like:
YEAR_CODE | TERM_CODE | ID NUM | SPORTS |
2013-2014 | 20 | 31364 | Tennis |
2014-2015 | 20 | 31364 | Tennis |
2015-2016 | 20 | 31364 | Tennis |
2013-2014 | 10 | 10235 | Soccer |
2014-2015 | 10 | 10235 | Soccer |
2013-2014 | 20 | 10235 | Tracks |
2015-2016 | 10 | 11866 | Basketball |
<tbody>
</tbody>
*year = academic year
I want the format to look like this:
ID NUM | 2013-14 | 2013-14 | 2014-15 | 2014-15 | 2015-16 | 2015-16 |
10 | 20 | 10 | 20 | 10 | 20 | |
31364 | Tennis | Tennis | Tennis | |||
10235 | Soccer | Tracks | Soccer | |||
11866 | Basketball |
<tbody>
</tbody>
I tried the basic transpose method but it does not look like the one I have on the top. What kind of formula do I have to use for this?
Thanks in advance for your help!
jbowers