Data from 4 Columns into varied Report

[Missy O]

Hello
Please help an amateur, I’ve gone cross eye trying to come up with a simple solution. 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

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)
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

 

[NBVC]

Not sure if you are still looking for a suggestion......

Anyway, here's mine. See attached. I have assumed you are using Excel 2007 or later.

First, I would set up the result table horizontally so that you can allow for variable rows of each as I have shown.

Second, insert a row above the main data.

Third, add helper columns to the main data to identify the matches to your various criteria.

In E2:

=IF(AND(C2<>0,C2<>"No PM"),COUNT(E$1:E1)+1,"")

in F2:

=IF(OR(A2="r",B2="r"),COUNT(F$1:F1)+1,"")

in G2:

=IF(OR(A2="s",B2="s"),COUNT(G$1:G1)+1,"")

each copied down

Now formulas in reports:

in A27:

=IFERROR(INDEX(A$2:A$20,MATCH(ROWS($A$1:$A1),$E$2:$E$20,0)),"")

copied across and down the table.

in E27:

=IFERROR(INDEX(A$2:A$20,MATCH(ROWS($A$1:$A1),$F$2:$F$20,0)),"")

copied across ... you will need to change the INDEX range to D$2: D$20 in the third column... then copy down

in I27:

=IFERROR(INDEX(A$2:A$20,MATCH(ROWS($A$1:$A1),$G$2:$G$20,0)),"")

copied across ... you will need to change the INDEX range to D$2: D$20 in the third column... then copy down