Removing duplicates, with a small twist. Need immediate help! Please

[modfather]

I'll get right to it: I have a spreadsheet that has data like this:

101 Zakia Alameri Gavan 128 1
101 Zakia Alameri Gavan 129 2
101 Zakia Alameri Gavan 130 3
101 Zakia Alameri Gavan 231 4
101 Zakia Alameri Gavan 132 5
101 Zakia Alameri Gavan 133 6
101 Zakia Alameri Gavan 134 7
101 Zakia Alameri Gavan 902 8
102 Meghan Albamonte Gavan 5 1
102 Meghan Albamonte Gavan 6 2
102 Meghan Albamonte Gavan 7 3
102 Meghan Albamonte Gavan 8 4
103 Makulumy Alexander-Hill Claddagh 328 1
103 Makulumy Alexander-Hill Claddagh 329 2
103 Makulumy Alexander-Hill Claddagh 331 4

In case this doesn't look right, I have 6 columns of data. The last column isn't really necessary. The first column is a "student ID" which will always be in numerical order and the 2nd, 3rd and 4th columns will be the same for each row as long as the first column doesn't change. It's the 5th column that presents the problem. What I want to do is delete the duplicate records, but show each of the repeated 5th column, hopefully comma-seperated. What I'd like the above data to look like is:

101 Zakia Alameri Gavan 128, 129, 130, 231, 132, 133, 134, 902
102 Meghan Albamonte Gavan 5, 6, 7, 8
103 Makulumy Alexander-Hill Claddagh 328, 329, 331

I can just hide the last column, which isn't really necessary.

Is there anyway I can do this?

Thanks.
Modfather

 

[plantj]

If your data was in columns A..F then in G put the following formula...

=IF($A2=$A1,CONCATENATE($G1,",",$E2),$E2)

...and copy it down

This builds the comma list.

Then in H put...

=$A2<>$A3

...and copy it down.

This gives TRUE for the rows you now need to keep. You could use either AutoFilter or Sort to pull them out and hey presto!