| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Sort by Count the Max nos of Unique text values in Pivot Ta
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!!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to Sort by Count the Max nos of Unique text values in Pivot Ta
Hi,
Right click the field 2 label in the Row Area, --Field settings..----Advanced..., then change the AutoSort options into "ascending or descending". ll "ToExcelAtExcel дµÀ£º " 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!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count the # of unique occurences of a text in a column? | Excel Worksheet Functions | |||
| COUNT SEVERAL TEXT VALUES AUTOMATICALLY | Excel Worksheet Functions | |||
| Pivot Table (vlookup 2 column text values, return 1 value) | Excel Discussion (Misc queries) | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Count number of Unique values | Excel Worksheet Functions | |||
Hybrid Mode
