G
Guest
I have the Table with over 15K records, with field 1 and 2, with text data,
for example:
---------
1 2
---------
A1 ABC
A1 DEF
A1 GHI
B1 ABC
C1 ABC
C1 DEF
....
I created a pivot table of above which gives me the unique values for field
FIELD "1" and the number of records for Field "2". Following is the result
when sorted on Field 1.
-----------------
1 2 TOTAL
-----------------
A1 ABC 1000
DEF 1200
GHI 500
------------------
A1 TOTAL 2700
------------------
B1 ABC 6000
------------------
B1 TOTAL 6000
------------------
C1 ABC 2000
DEF 1000
------------------
C1 TOTAL 3000
------------------
....
Now, I want to find the the maximum number of unique distinct values of
field 2 for field 1. If I select Field 1 and go to Pivot Table Advanced
Options, and set the AutoSort Option to descending, using field Count of F2,
then I get a sort of the number of Record, as follows:
-----------------
1 2 TOTAL
-----------------
B1 ABC 6000
------------------
B1 TOTAL 6000
------------------
C1 ABC 2000
DEF 1000
------------------
C1 TOTAL 3000
------------------
A1 ABC 1000
DEF 1200
GHI 500
------------------
A1 TOTAL 2700
-------------------
....
But I want the sort on the max number of count of distinct values of F2,
i.e something which gives me the following result:
-----------------
1 2 TOTAL
-----------------
A1 ABC 1000
DEF 1200
GHI 500
------------------
A1 TOTAL 2700
-------------------
C1 ABC 2000
DEF 1000
------------------
C1 TOTAL 3000
------------------
B1 ABC 6000
------------------
B1 TOTAL 6000
....
How do I do this in Excel 2003? I know I can do a Advanced Filter on the
table directly to find unique values and then do a Pivot on the results. The
problem with this approach is that it does not depict the total number of
records correctly, since many records will be missing, thus giving inaccurate
% of totals in the result. So, please advice if there is any way to do this
in Excel 2003. Thanks!!!
for example:
---------
1 2
---------
A1 ABC
A1 DEF
A1 GHI
B1 ABC
C1 ABC
C1 DEF
....
I created a pivot table of above which gives me the unique values for field
FIELD "1" and the number of records for Field "2". Following is the result
when sorted on Field 1.
-----------------
1 2 TOTAL
-----------------
A1 ABC 1000
DEF 1200
GHI 500
------------------
A1 TOTAL 2700
------------------
B1 ABC 6000
------------------
B1 TOTAL 6000
------------------
C1 ABC 2000
DEF 1000
------------------
C1 TOTAL 3000
------------------
....
Now, I want to find the the maximum number of unique distinct values of
field 2 for field 1. If I select Field 1 and go to Pivot Table Advanced
Options, and set the AutoSort Option to descending, using field Count of F2,
then I get a sort of the number of Record, as follows:
-----------------
1 2 TOTAL
-----------------
B1 ABC 6000
------------------
B1 TOTAL 6000
------------------
C1 ABC 2000
DEF 1000
------------------
C1 TOTAL 3000
------------------
A1 ABC 1000
DEF 1200
GHI 500
------------------
A1 TOTAL 2700
-------------------
....
But I want the sort on the max number of count of distinct values of F2,
i.e something which gives me the following result:
-----------------
1 2 TOTAL
-----------------
A1 ABC 1000
DEF 1200
GHI 500
------------------
A1 TOTAL 2700
-------------------
C1 ABC 2000
DEF 1000
------------------
C1 TOTAL 3000
------------------
B1 ABC 6000
------------------
B1 TOTAL 6000
....
How do I do this in Excel 2003? I know I can do a Advanced Filter on the
table directly to find unique values and then do a Pivot on the results. The
problem with this approach is that it does not depict the total number of
records correctly, since many records will be missing, thus giving inaccurate
% of totals in the result. So, please advice if there is any way to do this
in Excel 2003. Thanks!!!