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