| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross tab table
I do not want to use Pivot function
I have a table of 5000 row with five column Division District Salesman Factor result If I want to recap the result by Saleman by District by division what is the easy way to turn data to cross tabs . How can i turn these data to Cross tab with the user be able pick a Division-district it will show Factor John Jeff Robin Diane Howard Scott ------------more salesman sales R R G Y G G profit G Y Y Y Y Y GP% Y Y R R R R -- thanks liem |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross tab table
Hi,
We probably should understand what problems arise when you use a pivot table so we can address a solution that avoids those, because most alternates to a pivot table will be more complex to set up and maintain then the pivot table any problem you would have with the pivot table will probably be much more severe with an alternate solution. -- If this helps, please click the Yes button Cheers, Shane Devenshire "liem" wrote: I do not want to use Pivot function I have a table of 5000 row with five column Division District Salesman Factor result If I want to recap the result by Saleman by District by division what is the easy way to turn data to cross tabs . How can i turn these data to Cross tab with the user be able pick a Division-district it will show Factor John Jeff Robin Diane Howard Scott ------------more salesman sales R R G Y G G profit G Y Y Y Y Y GP% Y Y R R R R -- thanks liem |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross tab table
The best advice is to use a pivot table. Why don't you want to?
Regards, Fred. "liem" wrote in message ... I do not want to use Pivot function I have a table of 5000 row with five column Division District Salesman Factor result If I want to recap the result by Saleman by District by division what is the easy way to turn data to cross tabs . How can i turn these data to Cross tab with the user be able pick a Division-district it will show Factor John Jeff Robin Diane Howard Scott ------------more salesman sales R R G Y G G profit G Y Y Y Y Y GP% Y Y R R R R -- thanks liem |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross tab table
Because I have to set up the validation list of 8 divisions , so the user
pick Florida division it will show the format cross tab Division validation list Factor District saleman 1 saleman 2 saleman 3 saleman 4 saleman 5------ to 20 Sales R Y G G Y R Profit Y G R Y R R GP% Y R Y R R R I have 20 factors and R/Y/R is the rating Red/Yellow/Green to indicated they do a good job or bad job for each month. Pivot table will not easy to set up on the location and format thanks liem "Fred Smith" wrote: The best advice is to use a pivot table. Why don't you want to? Regards, Fred. "liem" wrote in message ... I do not want to use Pivot function I have a table of 5000 row with five column Division District Salesman Factor result If I want to recap the result by Saleman by District by division what is the easy way to turn data to cross tabs . How can i turn these data to Cross tab with the user be able pick a Division-district it will show Factor John Jeff Robin Diane Howard Scott ------------more salesman sales R R G Y G G profit G Y Y Y Y Y GP% Y Y R R R R -- thanks liem |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross tab table
The alternative I see is to use data filtering. Autofilter will create a
dropdown on the District column where the user can choose the district he or she wants. Autofilter won't do any summaries, but it doesn't look like you need any. Regards, Fred. "liem" wrote in message ... Because I have to set up the validation list of 8 divisions , so the user pick Florida division it will show the format cross tab Division validation list Factor District saleman 1 saleman 2 saleman 3 saleman 4 saleman 5------ to 20 Sales R Y G G Y R Profit Y G R Y R R GP% Y R Y R R R I have 20 factors and R/Y/R is the rating Red/Yellow/Green to indicated they do a good job or bad job for each month. Pivot table will not easy to set up on the location and format thanks liem "Fred Smith" wrote: The best advice is to use a pivot table. Why don't you want to? Regards, Fred. "liem" wrote in message ... I do not want to use Pivot function I have a table of 5000 row with five column Division District Salesman Factor result If I want to recap the result by Saleman by District by division what is the easy way to turn data to cross tabs . How can i turn these data to Cross tab with the user be able pick a Division-district it will show Factor John Jeff Robin Diane Howard Scott ------------more salesman sales R R G Y G G profit G Y Y Y Y Y GP% Y Y R R R R -- thanks liem |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross tab table
Excel 2007
For those who want to see a Pivot Table with traffic lights: http://www.mediafire.com/file/i5w3wm...02_21_09a.xlsx |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross tab table
Hi,
Based on your data it looks to me as though a pivot table with 1. Division in the page area (this would provide the pick list for the division automatically) 2. Factor, District in the row area (you can remove subtotals since it looks like you don't need them) 3. Saleman in the column area 4. Results to the data area (you can sum, count, average,... here depending on your needs or you can switch between summary statistics in one step. Or you can even display two or more summary stats at the same time.) 5. Apply conditional formatting to the data area (pivot table support conditional formatting which will give you the G/Y/R coloring you want. would do just what you want. -- If this helps, please click the Yes button Cheers, Shane Devenshire "liem" wrote: Because I have to set up the validation list of 8 divisions , so the user pick Florida division it will show the format cross tab Division validation list Factor District saleman 1 saleman 2 saleman 3 saleman 4 saleman 5------ to 20 Sales R Y G G Y R Profit Y G R Y R R GP% Y R Y R R R I have 20 factors and R/Y/R is the rating Red/Yellow/Green to indicated they do a good job or bad job for each month. Pivot table will not easy to set up on the location and format thanks liem "Fred Smith" wrote: The best advice is to use a pivot table. Why don't you want to? Regards, Fred. "liem" wrote in message ... I do not want to use Pivot function I have a table of 5000 row with five column Division District Salesman Factor result If I want to recap the result by Saleman by District by division what is the easy way to turn data to cross tabs . How can i turn these data to Cross tab with the user be able pick a Division-district it will show Factor John Jeff Robin Diane Howard Scott ------------more salesman sales R R G Y G G profit G Y Y Y Y Y GP% Y Y R R R R -- thanks liem |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross tab table
Hi,
And here is the formula approach assuming your data is in the range A1:E6000 with titles on the first row and in the following order: Person Division Factor District Result In G1 I put the data validation drop down with the different Divisions, here I have picked Florida. G H I J .... Florida District Factor JM PQ 1 Sales 6 10 1 Profit 4 4 2 Sales 6 7 2 Profit 7 6 The title district is in cell G3 in the above layout. JM and PQ are two salepersons, more would go to the right. The formula in I4 is =SUMPRODUCT(--($B$2:$B$6000=$G$1),--($C$2:$C$6000=$H4),--($D$2:$D$6000=$G4),--($A$2:$A$6000=I$3),$E$2:$E$6000) This formula can be copied down and over as far as you need it. -- If this helps, please click the Yes button Cheers, Shane Devenshire "liem" wrote: Because I have to set up the validation list of 8 divisions , so the user pick Florida division it will show the format cross tab Division validation list Factor District saleman 1 saleman 2 saleman 3 saleman 4 saleman 5------ to 20 Sales R Y G G Y R Profit Y G R Y R R GP% Y R Y R R R I have 20 factors and R/Y/R is the rating Red/Yellow/Green to indicated they do a good job or bad job for each month. Pivot table will not easy to set up on the location and format thanks liem "Fred Smith" wrote: The best advice is to use a pivot table. Why don't you want to? Regards, Fred. "liem" wrote in message ... I do not want to use Pivot function I have a table of 5000 row with five column Division District Salesman Factor result If I want to recap the result by Saleman by District by division what is the easy way to turn data to cross tabs . How can i turn these data to Cross tab with the user be able pick a Division-district it will show Factor John Jeff Robin Diane Howard Scott ------------more salesman sales R R G Y G G profit G Y Y Y Y Y GP% Y Y R R R R -- thanks liem |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cross Reference Table Data Searching | Excel Worksheet Functions | |||
| contingency table or cross tabulation | Excel Discussion (Misc queries) | |||
| contingency table or cross tabulation | Excel Discussion (Misc queries) | |||
| What formula can I set up to do a cross lookup in an Excel table? | Excel Worksheet Functions | |||
| Cross table | Excel Discussion (Misc queries) | |||
Linear Mode
