In Memory of Petruccio in Assassin's Creed 2. It was one of the most time consuming achievements I have ever gotten, and once I got the last feather I was more excited than when I actually completed the game haha
Some of the ones on the Orange Box, Little Rock Man was a great achievement to get, it wasnt hard just annoying. Then Mile High Club Generally ones that take a long time to get, still REALLY pissed off that im one agility orb short on Crackdown :(
I'm not entirely sure if it made me happier, or the amount of stress I experienced trying to get it going away has made it seem that way, but the achievement I have to give this to would be the Hard To The Core achievement for Dead Space 2. Quite possibly the most difficult thing I've played, and I'm on NG++ on Dark Souls.
While it's nowhere near as hard to get as others mentioned here, I was so happy when I got the No One Left Behind Achievement in Mass Effect 2. Just because I managed to delve into the Collector's Base, save my crew and get everyone out of there alive, all because I made the right decisions in the mission. It's not as hard to get as the other achievements here, but it was definitely the most satisfying achievement for me.
I grew quite attached to some of my crew members, such as Jack, Mordin, Legion and Thane, and I wanted to see them out alive, I wanted to see everyone out alive. That was why Mass Effect 3 was quite a gut punch for me (other issues about it aside), seeing some of my favourite Mass Effect 2 squadmates (and Love Interest) die. Mordin, Legion, Thane...Especially Thane :C
Theres two im still working on and both are in Madden '11. Pick up 6 where you have to fight for a fumble in your end zone and recover it for a touchdown. The other one is in the madden moments where i have to beat the saints in super bowl sunday with the colts with only a minute left on the clock. Both of them are making me tear my fucking hair out lol
The one achievement I got really made me happy, That I remember anyways, is the 'Totally Worth It' achievement on Halo Reach. That's the achievement where you have to get a double kill from the grave in a multiplayer game. I wasn't even going for it at the time. During a match I ended up getting the Covenant grnade launcher thingy (that one weapon that charges up to shoot four homing plasmas at once. I charged two shot and fired at two blue before I was killed. I immediately got mad that I died, but then the achievement popped up and I was shocked, then extremely happy.
I only have 2 achievements left to get on ME2 (those being 'bang a teammate' and 'finish on Insanity') and I have to admit, that one, and the one for getting to Dr. Amanda without being seen were two of the ones that made me happiest so far. Like ME1, however, the one for completing it on Insanity is going to make me whoop with joy when I get it.