Removing Duplicates in one column and summarizing data

**guard23** · 02-09-2012, 12:54 PM

http://dl.dropbox.com/u/5892902/names2012.xlsI have a 5 column document with about 3000 entries that I need help streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.

An example source file is found here:
http://dl.dropbox.com/u/5892902/names.xls

As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only recieved standing for 1 or 2 terms.

I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.

IDEAL COLUMN HEADINGS:

NAME | Grade | Term1 HonourRoll | Term1 Effort | Term2 HonourRoll | Term2 Effort |

etc etc etc

**arlu1201** · 02-09-2012, 01:21 PM

Its better if you attach the file than we downloading it from a link.

**guard23** · 02-09-2012, 01:38 PM

Attached here

**bebo021999** · 02-09-2012, 01:45 PM

Pls check my attachment to see if it meets your need.

**guard23** · 02-09-2012, 02:14 PM

Wow! THat is exactly what I wanted (Beb021999). Was hoping I could ask one more thing:

1) I made an earlier reply with a file called "names2012.xls" - can you do the same for the entire document?
2) Some of the Effort Honour Rolls are coming up with a "0." Not sure why? Can that be changed so it's either an E or nothing?

**bebo021999** · 02-09-2012, 02:33 PM

Oh, with such huge data like this, I belive that will causes CPU burning!
This can be solved with VBA solution but which unfornately I am unfamiliar with!
Any other help with macro solution?