Excel 2007-How to merge multiple records into one

[mag]

I needed help with merging multiple records into one.

In the attached spreadsheet I have the same user having access to multiple applications. What I need to do is compare the last, first names and the department and if they all match (b'coz there are some users with the exact last & first names, but in different depts) then merge the records into one and mark "Y" for all the applications the person has access to.

Also, in my worksheet I have last names in all caps, or sentence case etc. I tried converting it to proper case (sentence case-e.g.Martin). But I get an Invalid ref. error. Why?

I would greatly appreciate a quick response!



Many thanks in advance!