rooirokbokkie
New Member
- Joined
- Aug 21, 2014
- Messages
- 19
I'm currently working with a pretty messy data set and I need to consolidate the information on one sheet so I can analyse it. There are actually multiple issues but I thought I'd start with the first one. The data set is pretty big (70 000 ish rows and 20 columns) so I can't randomly cut and paste stuff.
Luckily the sheet contains a unique student number for each student. The problem is this. In sheet 1 each row represents a subject for each student. So the number of times a student appears is a function of the number of subjects he/she has and other data (like faculty) is in a new column.
Sheet 1:
<tbody>
</tbody>
I need a data set where each student is represented on one row only and the columns are subjects. Ideally it should look something like sheet 2. The other problem is that there are over 300 subjects and at most a particular student will have maybe 20 of them. So there's going to be an awful lot of empty cells if I try to straight up convert the subjects found in the rows of sheet 1 to columns. I thought about splitting the data up with each faculty on a different sheet, but how can I migrate their subjects marks like that?
Sheet 2:
<tbody>
</tbody>
I'd really appreciate some help. I'd be willing to take a shot at vba if it gets the job done
Luckily the sheet contains a unique student number for each student. The problem is this. In sheet 1 each row represents a subject for each student. So the number of times a student appears is a function of the number of subjects he/she has and other data (like faculty) is in a new column.
Sheet 1:
Student Number | Subject | Mark | Faculty |
345689 | Calculus | 83 | |
345689 | Physics | 89 | |
345689 | Informatics | 86 | |
345689 | Statistics | 69 | |
345689 | Science | ||
178907 | Psychology | 76 | |
178907 | Social Work | 89 | |
178907 | Humanities | ||
563333 | Calculus | 77 | |
563333 | Linear Algebra | 66 | |
563333 | Physics | 91 | |
563333 | Engineering |
<tbody>
</tbody>
I need a data set where each student is represented on one row only and the columns are subjects. Ideally it should look something like sheet 2. The other problem is that there are over 300 subjects and at most a particular student will have maybe 20 of them. So there's going to be an awful lot of empty cells if I try to straight up convert the subjects found in the rows of sheet 1 to columns. I thought about splitting the data up with each faculty on a different sheet, but how can I migrate their subjects marks like that?
Sheet 2:
Student Number | Faculty | Calculus | Physics | Informatics | Statistics | Psychology | Social Work | Linear Algebra |
345689 | Science | 83 | 89 | 86 | 69 | - | - | - |
178907 | Humanities | - | - | - | - | 76 | 89 | - |
563333 | Engineering | 77 | 91 | - | - | - | - | 66 |
<tbody>
</tbody>
I'd really appreciate some help. I'd be willing to take a shot at vba if it gets the job done
Last edited: