lgare
12-17-2015, 05:29 AM
Hi,
I have a data set of around 4000 individuals where each individual has multiple rows pertaining to scores in exams. Each row for each student contains an exam grade, and I have a column displaying the amount of credits the exam is worth (‘Exam Credit’). I need to identify the ‘best’ 100 credits worth of exam results for each student. I have sorted the data so that each students’ exam scores are in highest to lowest order. Can someone suggest some VBA code that will cumulatively add each students’ credit up to 100, then mark any subsequent exam rows with a N/A (or a #N/A or leave blank), as is shown in the ‘Cumulative Credit’ column. Any suggestions much appreciated.
I am currently using Office 365 with VBA version 7.1
Person Number
Exam Credit
Exam Score
Cumulative Credit
XXXXXX001
20
11
20
XXXXXX001
20
10
40
XXXXXX001
40
8
80
XXXXXX001
20
4
100
XXXXXX001
20
4
N/A
XXXXXX002
20
15
20
XXXXXX002
60
13
80
XXXXXX002
20
7
100
XXXXXX002
20
7
N/A
XXXXXX002
20
6
N/A
XXXXXX003
40
13
40
XXXXXX003
20
13
60
XXXXXX003
40
12
100
XXXXXX003
20
10
N/A
XXXXXX004
20
13
20
XXXXXX004
80
13
100
XXXXXX004
20
9
N/A
I have a data set of around 4000 individuals where each individual has multiple rows pertaining to scores in exams. Each row for each student contains an exam grade, and I have a column displaying the amount of credits the exam is worth (‘Exam Credit’). I need to identify the ‘best’ 100 credits worth of exam results for each student. I have sorted the data so that each students’ exam scores are in highest to lowest order. Can someone suggest some VBA code that will cumulatively add each students’ credit up to 100, then mark any subsequent exam rows with a N/A (or a #N/A or leave blank), as is shown in the ‘Cumulative Credit’ column. Any suggestions much appreciated.
I am currently using Office 365 with VBA version 7.1
Person Number
Exam Credit
Exam Score
Cumulative Credit
XXXXXX001
20
11
20
XXXXXX001
20
10
40
XXXXXX001
40
8
80
XXXXXX001
20
4
100
XXXXXX001
20
4
N/A
XXXXXX002
20
15
20
XXXXXX002
60
13
80
XXXXXX002
20
7
100
XXXXXX002
20
7
N/A
XXXXXX002
20
6
N/A
XXXXXX003
40
13
40
XXXXXX003
20
13
60
XXXXXX003
40
12
100
XXXXXX003
20
10
N/A
XXXXXX004
20
13
20
XXXXXX004
80
13
100
XXXXXX004
20
9
N/A