PDA

View Full Version : VBA code identify rows that have exceeded a certain value in a cumulative chain



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

Bob Phillips
12-17-2015, 07:02 AM
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))