I've worked myself into a corner and could really use help with 2
separate problems - this will probably require programming that is
beyond my skills - but expert advise would be appreciated to determine
how I should approach this:

I have a master spreadsheet used both as a data entry sheet and also
for tracking documents associated collected for each individual (per
row).

I have names and addresses typed in simple rows and columns
(simplified):

Site # Name Role Facililty Review Board
101 Joe Brown PI <name&address <name&address
101 Jane Blue CO <left empty <left empty
101 Jack Smith SUB <left empty <left empty
102 Bob Black PI <name&address <name&address

PROBLEM #1

I have another worksheet that pulls the information into a formatted
report that only lists the site # and Review Board information.

It works great as long as there is a single row of information per site
#, but when I add names with the CO role, i end up with

Site # Review Board
101 <Review Board Name and address
101 BLANK
101 BLANK
102 <Review Board Name and address

I want to NOT display those blank lines IF they are duplicate site
numbers AND IF the role is NOT PI. (if the role is PI, I need the line
displayed with a blank cell if empty).

I will never know how many CO or Subs will be listed per site, if any.

PROBLEM #2 (more complicated I think!)

I have another worksheet that pulls information from the same master
sheet.

This report looks like this right now:

Site # Name Role Facililty
-----------------------------------------------------------------
101 Joe Brown PI <name&address
-----------------------------------------------------------------
101 Jane Blue CO <left empty
-----------------------------------------------------------------
101 Jack Smith SUB <left empty
-----------------------------------------------------------------
102 Bob Black PI <name&address
-----------------------------------------------------------------

What I need it to display is:

Site # Name Role Facililty
-----------------------------------------------------------------
101 Joe Brown PI <name&address
<blank Jane Blue CO <blank
<blank Jack Smith SUB <blank
-----------------------------------------------------------------
102 Bob Black PI <name&address
-----------------------------------------------------------------

I require the site number to be printed only once, and not for every
individual. And you can see, I need a line displayed between sites
instead of btween every individual.

There will ALWAYS be a PI listed. Sometimes there are COs and SUBs,
but again, there is no way to determine if or how many there will be
for any given site.

I may be way in over my head here - I'd appreciate any feedback!

Thanks in advance for bearing with me.