Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table
I have a table like this:
Type Number AA 1 aa 2 Aa 3 aA 4 When I use the Pivot wizard i get this result: Sum of Number Type Total AA 10 Grand Total 10 Is there a setting that will enable the Pivot to recognize the upper and lower case types as unique ? Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table
Carl,
A comment first - using exact strings with the exception of case is a prescription for data corruption. BAD, BAD idea. Far better to have completely unique values for the type values. That said, if you need to work with existing data, either do a replace (check the match case option) and insert new values for the type, or use a formula like this in a new column =IF(EXACT(B2,"AA"),1,IF(EXACT(B2,"aa"),2,IF(EXACT( B2,"Aa"),3,IF(EXACT(B2,"aA"),4,"NA")))) to categorize your types, then use that category as the first of your row fields, with type as the second. Excel will group the 1s, 2s etc if you do it that way. HTH, Bernie MS Excel MVP "carl" wrote in message ... I have a table like this: Type Number AA 1 aa 2 Aa 3 aA 4 When I use the Pivot wizard i get this result: Sum of Number Type Total AA 10 Grand Total 10 Is there a setting that will enable the Pivot to recognize the upper and lower case types as unique ? Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions |