Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
using pivot table to crosstab text value data
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
using pivot table to crosstab text value data
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,ProL:ProN,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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data table based on data table | Excel Discussion (Misc queries) | |||
Report choosing a Pivot table data | Excel Worksheet Functions | |||
Pivot Table Data - Troubleshoot needed | Excel Discussion (Misc queries) | |||
OLAP Pivot table - How to show items with no data ? | Excel Worksheet Functions | |||
Pivot table formats text data with 2 decimals | Excel Discussion (Misc queries) |