To me they become memorable when they do interesting things on their own that are different from what the average Sim would do.
Never had that yet in Sims 4, they are all the same regardless of traits.
In Sims 2 a glitched NPC became most memorable because any activity that got him to remove his clothes did not cause him to put clothes back on afterwards. Had to tell him to put an outfit on. Did stay perfectly censored, but the guy drove to work naked and was fine doing anything naked, no other sims saw him as naked either. He walked naked around kids.
One of my sims married him so I have no idea how he got this way.
But my most memorable Sim of my own creation was in Sims 3, a masochist named Johnny Eastwood.
He was kind of this bad boy rockstar type, I don't know his exact traits any more but he seriously acted the part.
Was one of my five starting Sims, in his house was also a mooching slob guy, a nerdy guy and a good and evil woman who I made total opposites.
Funnily enough when moving in the two women had a pleasant conversation initially, until they found out they where good and evil. They never got along again ever since.
But Johnny Eastwood kept flirting with the evil woman, who kept slapping him or even beating him up every time he tried. Sometimes he would come on to her right after she kicked his butt. Their relationship was pretty negative but he kept trying so I guess he liked pain. And he never made a pass at the good girl, so it really seemed to fit his rockstar personality.
In Sims 3 it seems the easiest of all games to make memorable Sims though, I do have a more recent one who is so much fun. She's flirty, childish, easily impressed, friendly and has a good sense of humour. Messy red hair, colourful clothes and she just goes around making friends, having fun and flirting with people. She was constantly invited for parties at university and developed a naughty reputation mainly from simply doing the stuff she was dared to do.
Now that I am playing another house hold, she quickly became friends with the ambitious diva of this house hold. Because she's easily impressed that seems to go well with a diva but she's quick to make friends with anyone.
Only people that dislike her are those who she fooled around with and see her with someone else.
Which seriously should not hurt her reputation since she is not in a relationship with anyone, but ah well. The reputation adds character.