Cross tab table

[liem]

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

 

[Shane Devenshire]

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.

 

[Fred Smith]

The best advice is to use a pivot table. Why don't you want to?

Regards,
Fred.

 

[liem]

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

The best advice is to use a pivot table. Why don't you want to?

Regards,
Fred.

 

[Fred Smith]

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.

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

 

[Herbert Seidenberg]

Excel 2007
For those who want to see a Pivot Table
with traffic lights:
http://www.mediafire.com/file/i5w3wmgzhjj/02_21_09a.xlsx

 

[Shane Devenshire]

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

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

 

[Shane Devenshire]

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

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