using pivot table to crosstab text value data

[trippknightly]

I want to use pivot table to be able to show a report where, e.g., left
hand side are analysts assigned to projects, top is sponsors of
projects and values are the project names themselves. Any given
analyst could be working on 0 to n projects for any given sponsor. So
at ea intersection of analyst & sponsor the report provides a mini-list
of projects of interest to that pair of individuals.

There are other potential fields to include across the top, left or
page-field level, but the basic challenge is how to have something that
can dynamically present this kind of data.

I know access can do crosstabs but it has limitations (one column
heading, no such thing as page fields per se, for example).

Thoughts??? Thanks...

 

[Herbert Seidenberg]

Assume your data and layout looks like this

Analyst Sponsor Project ProNL ProL ProN
Smith Amden BP42 3 BP42 3
Miller Boyer GA24 5 GA24 5
Jones Cenan AT77 7 AT77 7
Cooper Dantus SC82 11 SC82 11
Smith Dantus SC82 11 BP65 13
Miller Amden BP65 13 GA12 17
Jones Boyer GA12 17 SC45 19
Cooper Cenan AT77 7 SC83 23
Miller Boyer GA12 17 SC84 29
Cooper Dantus SC45 19
Smith Dantus SC83 23
Smith Dantus SC84 29
Cooper Amden BP65 13

Name all columns with the suggested names.
Generate unique project names in ProL with Advanced Filter.
ProN are prime numbers.
ProNL has the formula
=VLOOKUP(Project,ProLroN,2,0)
Select the first 4 columns and generate a Pivot Table.
Layout: ROW=Analyst, COLUMN=Sponsor, DATA=Product of ProNL
Options: Uncheck Grand Total, AutoFormat; For empty cells show 1
The Pivot Table should look like this:

Analyst Amden Boyer Cenan Dantus
Cooper 13 1 7 209
Jones 1 17 7 1
Miller 13 85 1 1
Smith 3 1 1 7337

Assume Cooper/Amden is located at B6
Insert > Name > Define > Names in Workbook: ProSet
Refers To: =IF(MOD(B6,ProN)=0,ProL&", ","")
At B12 enter this formula and copy to E15
=INDEX(ProSet,1)&INDEX(ProSet,2)&INDEX(ProSet,3)
&INDEX(ProSet,4)&INDEX(ProSet,5)&INDEX(ProSet,6)
&INDEX(ProSet,7)&INDEX(ProSet,8)&INDEX(ProSet,9)
You should get this:
BP65, AT77, SC82, SC45,
GA12, AT77,
BP65, GA24, GA12,
BP42, SC82, SC83, SC84,