Pivot Tables Case Sensitive Grouping

Hi 

I have a pivot table which is group items even if the text in the field is 
not the same case - ie I have abc123 and ABC123 - The Pivot Table is grouping 
it one the line rather than displaying two lines.

Anyone come across this before ???  I've looked for some options around this 
but can't see to disable the case insensitive grouping.

Thanks


0
scott7693 (519)
8/23/2006 1:48:02 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1057 Views

Similar Articles

[PageSpeed] 24

You could create a function that returns a unique value for each text string
and use that as the grouping column of the pivot table. Post back here if
you want such a function

Gerry
"Mick Scott" <Mick Scott@discussions.microsoft.com> wrote in message
news:B578D163-C27F-4E88-A1CF-FD44604987F9@microsoft.com...
> Hi
>
> I have a pivot table which is group items even if the text in the field is
> not the same case - ie I have abc123 and ABC123 - The Pivot Table is
grouping
> it one the line rather than displaying two lines.
>
> Anyone come across this before ???  I've looked for some options around
this
> but can't see to disable the case insensitive grouping.
>
> Thanks
>
>


0
Gerry3278 (36)
8/24/2006 10:30:59 AM
That's pretty much the way excel compares strings--it ignores case in most cases
<bg>.

Depending on what you're doing, you could use another column and include that in
your pivottable:

=if(exact(a2,upper(a2)),"Upper",if(exact(a2,lower(a2)),"Lower","Mixed"))

But 123-#*&^*% will be marked as Upper.



Mick Scott wrote:
> 
> Hi
> 
> I have a pivot table which is group items even if the text in the field is
> not the same case - ie I have abc123 and ABC123 - The Pivot Table is grouping
> it one the line rather than displaying two lines.
> 
> Anyone come across this before ???  I've looked for some options around this
> but can't see to disable the case insensitive grouping.
> 
> Thanks

-- 

Dave Peterson
0
petersod (12004)
8/24/2006 12:39:53 PM
Reply:

Similar Artilces:

dynamically update pivot table data source
I am trying to determine if there is a way to dynamically update the data source underlying a pivot table. For example, I have a data sheet that grows by one year each month and this sheet populates a pivot table. Each time I add a new row, I have to redefine the source data range for the pivot table. Can this action be done automatically? I have seen something like this to update graphs using the series function... just not for pivot tables. Thanks in advance. chaz You can use a dynamic formula to define the source range for your Pivot Table. There are some instructions here: ...

HLookup, multiple tables and range names in other cells
Hello, I am beginning to discover Excel with the aid of various textbooks but I am stumped on one problem and don't wish to embark on VBA custom functions unless I have to. I am intending to use HLookup with multiple tables (over 15) in the same worksheet. So here is my problem, =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) The 'table_array' part can be a range or the name of a range, if I have a cell that is calculated to produce the name of a range eg. an INDEX and MATCH formula, how can I get 'table_array' to accept this without errors? eg.=HLOOKUP(A...

Getting arrows for borders/gridlines on Tables in PowerPoint; how.
Every time I create a table in PowerPoint and I change one of the cells borders - the border becomes an arrow. Is their something I can do to stop this from happening. It's really getting annoying. Thanks to anyone who helps. pablobandito <pablobandito@discussions.microsoft.com> was very recently heard to utter: > Every time I create a table in PowerPoint and I change one of the > cells borders - the border becomes an arrow. Is their something I > can do to stop this from happening. It's really getting annoying. Suggestion #1: Ask the question where the PowerP...

Incidents/Cases ticket number
Hi all, How can I change the generator for automatic cases ticket number ? By default appears CAS-XXXX, how can i change the prefix and reset the numerator ? Thanks, Hugo ...

scatter plots using pivot table data
In older versions of Excel, you could directly create xy scatter plots using pivot table data. In Excel 2000, the chart wizard says you can't use an xy scatter plot with pivot table data. Why????? Is there any way around this limitation? Dan - Don't tell the pivot table you're making a regular chart. Select a cell away from the PT and start the chart wizard. Select the XY Scatter type you want in step 1. In step 2, use the Series tab to select the ranges for the names, X values, and Y values of each series in your chart. Make sure you don't select one of the PT but...

color coded cases
Making use of colors to code cases (based on severity or age for example) would be an extremely useful feature. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=400a233e-268...

Comparison Table
Hi, I am looking for a Comparison Table that compaires Sales Logix ,Accpac and Microsoft CRM features. Could anybody refer me to such chart or table? Thanks, Ali Hi AM: Can you shoot me an email at jstraumann@<nospam>.cogeco.ca remove the <nospam> John. "A.M" <IHateSpam@sapm123.com> wrote in message news:%23YfA%230$FEHA.740@tk2msftngp13.phx.gbl... > Hi, > > I am looking for a Comparison Table that compaires Sales Logix ,Accpac and > Microsoft CRM features. > Could anybody refer me to such chart or table? > > Thanks, > Ali > > ...

Deleted Case, time allocation still showing in Contract line
Hi I have deleted a Case and the Time Allocation is still showing in the Contract line. How can I remove/update the Contract Lines, Allotments Used field Many thanks Martin ...

Setting up a group
I have not worked with outlook, except to send an email, but I was asked to set up a distribution list so that I can email a file to multiple clients. I have set up the email addresses in excel. Is there a way to set up Outlook so that I can set up the fields that I need such as name, address, client no, email address., contact person. I tried to import into a group under contacts, but when i get to the step that says it will could take several minutes to import, the next button is grayed out. As you can tell I am out of my element on this. Any help will be appreciated. Thank you....

Data Form from tables
Hi, very new to Access, I have project i am stuck on I have designed 4 tables: 1. Hardware 2. Supplier 3. IT Technicians 4. Staff details I think I have made correct Primary and foreigh keys as in Hardware ID, Staff ID, IT Technicians ID and Supplier ID as primary keys and foreign keys in relevant tables I need to make another table that stores fault logs and a user form to input this data. I have tried variuous methods, to no real avail. I want the staff details to be at top so that when user types in the ID there name and details appear and underneath they can typ...

Can multiple Recordset objects be open concurrently using the same Access DB table???
Yes. - Tim "Mark Rance" <mrr@pcisys.net> wrote in message news:101qojpivaanr2c@corp.supernews.com... > > ...

automatically assign new users to a group
Is there a way that I can assign the new users I create in Active Directory automatically to one of my groups? I'm using exchange 2000 / server 2000. I've looked at the default recipient policy, but can't quite figure out how to do it. Thanks, Glenn I do not think that is posible, at least using the GUI. You can use the command line tools to make some bat files. >-----Original Message----- >Is there a way that I can assign the new users I create in Active Directory >automatically to one of my groups? > >I'm using exchange 2000 / server 2000. > >I...

Pivot text data
Is there a way to organize data based on text fields. Here is what I am trying to do. I have two columns of data, for example, consider the data below: _Day_ _Time_ _Subject_ Saturday 4pm Call you Sunday 5 pm Call me I want to create a pivot report from this data. So that the data would look like this: _Saturday_ _Sunday_ 4 pm Call you 5pm Call me -- sheikhzada ------------------------------------------------------------------------ sheikhza...

Lookup tables #2
want to use lookup tables as poor man's db. have about 200 uniquely numbered stores, each time a call comes in we log it by that number, and each "record" has 11 "fields" or columns. if the list is sorted in ascending order, how can i use a a vlookup in combo with macro to go in and key off the unique number and bring all the rows of data back identified with that unique number to one page? Stores may have only one "record" and some may 15. formula would look at the keyed in number...say 101 and go to lu table and start with 101 and repeat until it reach...

Removing Pivit Table Items
I'm way over my head here, so bear with me - I have a pivot table for prescription drug charges. The ROW column has the 12 months. When I first set this up, there were several other subheadings (drug store/size/etc.) which appeared, correctly, after the 'months' listing. I have now revised the chart so that all I want are the 12 months. However, try as I might, the old subheadings persist in appearing in the row list. In addition, a couple of new items have appeared, as far as I know out of thin air - 'formula1" and 'blank' How can I delete these extra...

Update multi-select control to table
Access 2007 on Vista I am using a form to set variables for the creation of a report. One of the controls is a multi-select combo box. There is a table which stores the choices from the form and provides the Record Source for the report. Following the completion of the choices in the multi-select combo box and clicking on the OK button at the bottom of the control, focus moves to another control. I have determined that the table does not immediately update the multi-value selections to the table, but does so correctly when the form is closed. Exiting the form and returni...

How do i enable "Group" & "Ungroup" in a protected sheet
1 -I have grouped data in my excel sheet by using the Group rows function. 2- When i protect the sheet, the goup and Ungroup button (the + sign at the left of the sheet), won't work. Question: Is there a way to proctect the sheet and keep the Group and ungroup (+ sign)function normally. Thank you Hi aFAIK you can't -- Regards Frank Kabel Frankfurt, Germany "Fadi Haddad" <Fadi Haddad@discussions.microsoft.com> schrieb im Newsbeitrag news:F3E712BE-6430-4D6E-9502-ED4A3A66A8F0@microsoft.com... > 1 -I have grouped data in my excel sheet by using the Group rows fun...

Linking cells to Pivot Tables in Office XP "Get Pivot" frustrations
I was recently upgraded to Office XP at work. I've encountered one frustrating new feature. I can no longer link a cell to a pivot table cell and copy this linked cell across the row. For example, in the linked cell formula,instead of the linked cell referring to the pivot table cell as "C11" it refers to it as "GETPIVOTDATA("PMPM",$I$18,"Rpt_Date",DATE(2003,11,1),"renew_current",DATE(2004,1,1))". This may return the correct value for this intial cell but if I try to copy this link to the next cell over (across the row), I get the same val...

Restrict access to groups in Exchange/Outlook?
Hi, Is it possible to restrict access to groups in Exchange or Outlook? This is what I want: If I have a group called 'All Users' with all users in it, is it possible to allow only the group 'Managers' to use this group? Or the following scenario: If I have a group called 'Group 1', is it possible to allow only the group 'Group 1' to use this group. (And the same for 'Group 2', 'Group 3' etc.) Is this possible? Best regards, Dennis. You can do it from Exchange/AD. There is a delivery restrictions tab in Exchange Administrator in the case o...

Grouped sheets page formatting will copy the header
When I select all sheets in a group and format the pages (in page setup), it copies the header from the first worksheet to all the worksheets. How can I stop this from happening? Don't edit the header while the sheets are grouped together - this includes just selecting the header to have a look at it, as Excel will assume that you have changed something and will then apply it to all the grouped sheets. Hope this helps. Pete On Dec 7, 7:09=A0pm, James Trujillo <James Truji...@discussions.microsoft.com> wrote: > When I select all sheets in a group and format the...

Update table 01-21-08
I have two tables with same fields (ID, Date, Time, Field1, Field2, ...). Table1 is with all data, and Table2 with only newest data. How to copy just missed rows from Table2 to Table1 (missed ID). Thenks! On Mon, 21 Jan 2008 00:52:01 -0800, Igor G. <IgorG@discussions.microsoft.com> wrote: >I have two tables with same fields (ID, Date, Time, Field1, Field2, ...). >Table1 is with all data, and Table2 with only newest data. Then you almost certainly have a misdesigned set of tables. Storing the same data redundantly in two tables Is A Bad Idea. >How to copy just missed rows fr...

Grouping dates in PivotTable/Chart
My data set includes data from approximately 3 years (daily data). I'd like to use the group data function in PivotTables/Charts to get weekly, monthly etc. averages. Unfortunately, if I choose "month", it groups ALL Januarys together and doesn't separate them out by year. Can I get individual monthly averages with this feature? Is there a better way to get individual weekly, monthly, quarterly and yearly average graphs quickly and easily? Thank you! Heidi Hi You can choose Months and Years to group the pivot table so that it displays the yearly and then month...

insert data into table
Hi There, I'm using following code to insert data into my DB but it doesn't work: try { int tmp = 0; tmp = this.routeTableAdapter1.InsertRoute(Int32.Parse(this.DestNum.Text), this.Destination.Text, Int32.Parse(this.LineNum.Text)); MessageBox.Show("inserted " + tmp.ToString() + " elements into the route table."); MessageBox.Show("Update() returns: " + routeTableAdapter1.Update(myPID_DB_DataSet).ToString()); myPID_DB_DataSet.AcceptChanges();...

Pivot Table Not Refreshing Properly?
After creating a PivotTable, I discovered that there were several errors in the underlying data which caused improper values to show up in the PivotTable field drop-down lists. I have since re-generated the underlying data and replaced it in the Excel workbook. However, upon "refreshing" the PivotTable against the new data, I find that the 'improper values' still show up in the drop-down lists despite the fact that they no longer exist in the data! How can I remove the bad values from the drop-down lists without entirely rebuilding the PivotTable? Thank you for any help! ...

RSS feed for this discussion group not working anymore
Can anyone explain why did this discussion group RSS feed stop working in the past week? -- Leo ...