VBA code identify rows that have exceeded a certain value in a cumulative chain

[lgare]

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

[Bob Phillips]

You can do it with a formula. Put this in D2 and copy down

=IF(SUMIF($A$2:$A2,$A2,$B$2:$B2)>100,"N/A",SUMIF($A$2:$A2,$A2,$B$2:$B2))