Speaking just of the OG version...that's a tricky one. The character himself is a typical stick-up-ass mommy's boy with delusions of grandeur who only appears onscreen in his actual physical form like two times in the current-year story. The buildup of in-world hype surrounding the character is quite good, though; from his sword mysteriously sticking out of the back of your primary antagonist up to that point, to Cloud's line of "his strength is unreal, he's far more powerful in reality than any story you've heard about him," to the shot of him leaving a tree-impaled Midgar Zolom in his wake, etc. He's a character who's much cooler to have other characters talk about than to actually appear on-screen.
I think it's fair to say though that he wouldn't be half as memorable if Aeris' death was factored out of the equation.
---
Simple questions deserve long-winded answers that no one will bother to read.