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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]YEAR_CODE
[/TD]
[TD]TERM_CODE
[/TD]
[TD]ID NUM
[/TD]
[TD]SPORTS
[/TD]
[/TR]
[TR]
[TD]2013-2014
[/TD]
[TD]20
[/TD]
[TD]31364
[/TD]
[TD]Tennis
[/TD]
[/TR]
[TR]
[TD]2014-2015
[/TD]
[TD]20
[/TD]
[TD]31364
[/TD]
[TD]Tennis
[/TD]
[/TR]
[TR]
[TD]2015-2016
[/TD]
[TD]20
[/TD]
[TD]31364
[/TD]
[TD]Tennis
[/TD]
[/TR]
[TR]
[TD]2013-2014
[/TD]
[TD]10
[/TD]
[TD]10235
[/TD]
[TD]Soccer
[/TD]
[/TR]
[TR]
[TD]2014-2015
[/TD]
[TD]10
[/TD]
[TD]10235
[/TD]
[TD]Soccer
[/TD]
[/TR]
[TR]
[TD]2013-2014
[/TD]
[TD]20
[/TD]
[TD]10235
[/TD]
[TD]Tracks
[/TD]
[/TR]
[TR]
[TD]2015-2016
[/TD]
[TD]10
[/TD]
[TD]11866
[/TD]
[TD]Basketball
[/TD]
[/TR]
</tbody>[/TABLE]
*term 10 = fall; 20 = spring
*year = academic year
I want the format to look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID NUM
[/TD]
[TD="align: center"]2013-14
[/TD]
[TD="align: center"]2013-14
[/TD]
[TD="align: center"]2014-15
[/TD]
[TD="align: center"]2014-15
[/TD]
[TD="align: center"]2015-16
[/TD]
[TD="align: center"]2015-16
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[/TR]
[TR]
[TD]31364
[/TD]
[TD][/TD]
[TD]Tennis
[/TD]
[TD][/TD]
[TD]Tennis
[/TD]
[TD][/TD]
[TD]Tennis
[/TD]
[/TR]
[TR]
[TD]10235
[/TD]
[TD]Soccer
[/TD]
[TD]Tracks
[/TD]
[TD]Soccer
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11866
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Basketball
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]YEAR_CODE
[/TD]
[TD]TERM_CODE
[/TD]
[TD]ID NUM
[/TD]
[TD]SPORTS
[/TD]
[/TR]
[TR]
[TD]2013-2014
[/TD]
[TD]20
[/TD]
[TD]31364
[/TD]
[TD]Tennis
[/TD]
[/TR]
[TR]
[TD]2014-2015
[/TD]
[TD]20
[/TD]
[TD]31364
[/TD]
[TD]Tennis
[/TD]
[/TR]
[TR]
[TD]2015-2016
[/TD]
[TD]20
[/TD]
[TD]31364
[/TD]
[TD]Tennis
[/TD]
[/TR]
[TR]
[TD]2013-2014
[/TD]
[TD]10
[/TD]
[TD]10235
[/TD]
[TD]Soccer
[/TD]
[/TR]
[TR]
[TD]2014-2015
[/TD]
[TD]10
[/TD]
[TD]10235
[/TD]
[TD]Soccer
[/TD]
[/TR]
[TR]
[TD]2013-2014
[/TD]
[TD]20
[/TD]
[TD]10235
[/TD]
[TD]Tracks
[/TD]
[/TR]
[TR]
[TD]2015-2016
[/TD]
[TD]10
[/TD]
[TD]11866
[/TD]
[TD]Basketball
[/TD]
[/TR]
</tbody>[/TABLE]
*term 10 = fall; 20 = spring
*year = academic year
I want the format to look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID NUM
[/TD]
[TD="align: center"]2013-14
[/TD]
[TD="align: center"]2013-14
[/TD]
[TD="align: center"]2014-15
[/TD]
[TD="align: center"]2014-15
[/TD]
[TD="align: center"]2015-16
[/TD]
[TD="align: center"]2015-16
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]20
[/TD]
[/TR]
[TR]
[TD]31364
[/TD]
[TD][/TD]
[TD]Tennis
[/TD]
[TD][/TD]
[TD]Tennis
[/TD]
[TD][/TD]
[TD]Tennis
[/TD]
[/TR]
[TR]
[TD]10235
[/TD]
[TD]Soccer
[/TD]
[TD]Tracks
[/TD]
[TD]Soccer
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11866
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Basketball
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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