PDA

View Full Version : Solved: How to sum a range of cell values based on common data field



bananatang
10-09-2009, 07:45 AM
HI,

I really could do with some assistance in solving this situation.

i have a worksheet which contains student data. Each pupil will have a row which contains data relating to their attendance.

Some pupils will have more than one row of data against their name. What i am trying to have is a single row for each student. In order to do that i need to consolodate certain cell values together from each duplicate pupils records into a single record.

Each student will have a Unique pupil No. (UPN, Column E) for easy indentification of all pupils who have more than one row of data.

If a pupil is on the worksheet has more than one row of data

i need column K which is called "Possible Sessions" cell values to be added together for pupils that have the same UPN.

Plus

i need each column from R to AD cell values to be added together.

I would like the new amended cell values to be inserted into the duplicate students row which contains a blank value in cell P which is called On Roll/Off Roll

AND FINALLY

The duplicate pupil rows to be deleted. The rows to be deleted will have a text value called "Off Roll" in column P

I have attached an example for easier understanding.

Thanks

BT

MaximS
10-11-2009, 09:03 AM
Hi BT,

Hopefully I've solved your problem.

Please see attached for details.

bananatang
10-12-2009, 02:10 AM
HI MaximS

Thank you very much for your help.

I will check your code with real data and let you know. It looks good based on the sample data.

Once again, thanks very much for sharing your knowledge.

BT

bananatang
10-14-2009, 04:17 PM
Hi maximS

I have tested the code you provided and it works great. Thanks for much for your help.

I will now go ahead and disect your code to determine how it works

Once again, thank you.

BT