Data from 4 columns to populate into varied report - formula or VB?

Hello
Please help an amateur, I have gone cross eye trying to come up with a simple solution and ended up confusing myself more than anything. Alas I am out of my depth. Could someone please help.

I have 4 columns of data that need some fancy work to turn into an easy report or table or whatever.

Excerpt of data attached. Also shown revised layout shown at the bottom

3 results required:

1. If column C has text other than (0 or No PM) then copy A B C to REPORT Worksheet. (no blanks.) NB. Column c is actually a vlookup formula.
2. If column A:B has s then copy A B D to REPORT Worksheet
3. If column A:B has r then copy A B D to REPORT Worksheet

I agree, this is probably ridiculously easy for some of you and yes I am a little embarrassed, but would really appreciate a kick in the right direction.

Thanks, looking forward to any assistance.
Michelle

Like this?

Wow thanks, yes this is a fantastic starting point. Thank you. Thank you

I'm going to have a crack at getting the rest of it done myself by tweaking your script. Hopefully it won't be a major disaster, I'll come back for more assistant if I fail.

Thank you again for putting me on the right path. Much appreciated