A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Using text values in Pivot Tables



 
 
Thread Tools Display Modes
  #1  
Old September 22nd 09, 06:42 PM posted to microsoft.public.excel.misc
Eric
external usenet poster
 
Posts: 1,670
Default Using text values in Pivot Tables

I am creating a simple pivot table with one field of values, numbers to be
summed, and a second field, occasional two letter alpha codes which are purely
descriptive and refer to the number in the same column, "row".
In the source spreadsheet, I have formatted the column which contains these
codes as "Text". Text is supposed to remain unchanged; however when that
column of data is entered into the pivot table as a value, it is handled as
though it were numeric data, and these two letter codes do not appear.
Because the software apparently recognizes the values as non-numeric it
assigns a "Count" descriptor by default, but this only converts each instance
of a the text values to "1", thus losing their original identity.
All the possible definers of the field are numeric operators like "Count" and
"Sum", which do not apply to an alpha code, which is just supposed to be
represented literally. Further I see no way to manipulate this data field to
be recognized as text in the pivot table itself.
How do I get the pivot table to just take this alpha data as is?
Thanks,
Eric
Ads
  #2  
Old September 22nd 09, 06:51 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 5,942
Default Using text values in Pivot Tables

The text field only makes sense in either the row or column headers. If you
place it in the data section then it will return the most logical aggregation
which is a count as sum does not make sense with text.

In the data section of the pivot table what exactly were you wanting it to do?
--
HTH...

Jim Thomlinson


"Eric" wrote:

> I am creating a simple pivot table with one field of values, numbers to be
> summed, and a second field, occasional two letter alpha codes which are purely
> descriptive and refer to the number in the same column, "row".
> In the source spreadsheet, I have formatted the column which contains these
> codes as "Text". Text is supposed to remain unchanged; however when that
> column of data is entered into the pivot table as a value, it is handled as
> though it were numeric data, and these two letter codes do not appear.
> Because the software apparently recognizes the values as non-numeric it
> assigns a "Count" descriptor by default, but this only converts each instance
> of a the text values to "1", thus losing their original identity.
> All the possible definers of the field are numeric operators like "Count" and
> "Sum", which do not apply to an alpha code, which is just supposed to be
> represented literally. Further I see no way to manipulate this data field to
> be recognized as text in the pivot table itself.
> How do I get the pivot table to just take this alpha data as is?
> Thanks,
> Eric

  #3  
Old September 22nd 09, 07:37 PM posted to microsoft.public.excel.misc
Eric
external usenet poster
 
Posts: 1,670
Default Using text values in Pivot Tables

Thanks for your response Jim.
I don't want the text codes to do anything. They are just descriptors of
some of the numbers in the same column, row, when those numbers have met
various external criteria. It is important information that I need to be
able to see as is with no conversion. They would not function properly as
column or row labels, they are data that need to appear next to the
particular numbers they qualify. I have the sinking feeling that for some
reason pivot tables can only deal with Values numerically. Even though it
would be straightforward to just place them where they belong unmolested.
Eric

"Jim Thomlinson" wrote:

> The text field only makes sense in either the row or column headers. If you
> place it in the data section then it will return the most logical aggregation
> which is a count as sum does not make sense with text.
>
> In the data section of the pivot table what exactly were you wanting it to do?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Eric" wrote:
>
> > I am creating a simple pivot table with one field of values, numbers to be
> > summed, and a second field, occasional two letter alpha codes which are purely
> > descriptive and refer to the number in the same column, "row".
> > In the source spreadsheet, I have formatted the column which contains these
> > codes as "Text". Text is supposed to remain unchanged; however when that
> > column of data is entered into the pivot table as a value, it is handled as
> > though it were numeric data, and these two letter codes do not appear.
> > Because the software apparently recognizes the values as non-numeric it
> > assigns a "Count" descriptor by default, but this only converts each instance
> > of a the text values to "1", thus losing their original identity.
> > All the possible definers of the field are numeric operators like "Count" and
> > "Sum", which do not apply to an alpha code, which is just supposed to be
> > represented literally. Further I see no way to manipulate this data field to
> > be recognized as text in the pivot table itself.
> > How do I get the pivot table to just take this alpha data as is?
> > Thanks,
> > Eric

  #4  
Old September 22nd 09, 07:48 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 5,942
Default Using text values in Pivot Tables

Your only choice is row and column headers. Pivot tables aggregate data in a
cube. The data secton shows the aggregation of those fields. It can not show
text associated with those aggregations. The reason being is that the
aggregation may contain values with more than one descriptor at which point
what should it show.

A simple example...
Region / Location / Sales

If I place the region in the left column and sales in the data section then
the aggregation of the region will have multiple locations assocatied each
data value. Now if I add location to the data section what should it show???
--
HTH...

Jim Thomlinson


"Eric" wrote:

> Thanks for your response Jim.
> I don't want the text codes to do anything. They are just descriptors of
> some of the numbers in the same column, row, when those numbers have met
> various external criteria. It is important information that I need to be
> able to see as is with no conversion. They would not function properly as
> column or row labels, they are data that need to appear next to the
> particular numbers they qualify. I have the sinking feeling that for some
> reason pivot tables can only deal with Values numerically. Even though it
> would be straightforward to just place them where they belong unmolested.
> Eric
>
> "Jim Thomlinson" wrote:
>
> > The text field only makes sense in either the row or column headers. If you
> > place it in the data section then it will return the most logical aggregation
> > which is a count as sum does not make sense with text.
> >
> > In the data section of the pivot table what exactly were you wanting it to do?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Eric" wrote:
> >
> > > I am creating a simple pivot table with one field of values, numbers to be
> > > summed, and a second field, occasional two letter alpha codes which are purely
> > > descriptive and refer to the number in the same column, "row".
> > > In the source spreadsheet, I have formatted the column which contains these
> > > codes as "Text". Text is supposed to remain unchanged; however when that
> > > column of data is entered into the pivot table as a value, it is handled as
> > > though it were numeric data, and these two letter codes do not appear.
> > > Because the software apparently recognizes the values as non-numeric it
> > > assigns a "Count" descriptor by default, but this only converts each instance
> > > of a the text values to "1", thus losing their original identity.
> > > All the possible definers of the field are numeric operators like "Count" and
> > > "Sum", which do not apply to an alpha code, which is just supposed to be
> > > represented literally. Further I see no way to manipulate this data field to
> > > be recognized as text in the pivot table itself.
> > > How do I get the pivot table to just take this alpha data as is?
> > > Thanks,
> > > Eric

  #5  
Old September 22nd 09, 09:10 PM posted to microsoft.public.excel.misc
Eric
external usenet poster
 
Posts: 1,670
Default Using text values in Pivot Tables

Thanks very much Jim for your explanation. It's not what I wanted to hear,
but it's what I needed to know.
Eric

"Jim Thomlinson" wrote:

> Your only choice is row and column headers. Pivot tables aggregate data in a
> cube. The data secton shows the aggregation of those fields. It can not show
> text associated with those aggregations. The reason being is that the
> aggregation may contain values with more than one descriptor at which point
> what should it show.
>
> A simple example...
> Region / Location / Sales
>
> If I place the region in the left column and sales in the data section then
> the aggregation of the region will have multiple locations assocatied each
> data value. Now if I add location to the data section what should it show???
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Eric" wrote:
>
> > Thanks for your response Jim.
> > I don't want the text codes to do anything. They are just descriptors of
> > some of the numbers in the same column, row, when those numbers have met
> > various external criteria. It is important information that I need to be
> > able to see as is with no conversion. They would not function properly as
> > column or row labels, they are data that need to appear next to the
> > particular numbers they qualify. I have the sinking feeling that for some
> > reason pivot tables can only deal with Values numerically. Even though it
> > would be straightforward to just place them where they belong unmolested.
> > Eric
> >
> > "Jim Thomlinson" wrote:
> >
> > > The text field only makes sense in either the row or column headers. If you
> > > place it in the data section then it will return the most logical aggregation
> > > which is a count as sum does not make sense with text.
> > >
> > > In the data section of the pivot table what exactly were you wanting it to do?
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Eric" wrote:
> > >
> > > > I am creating a simple pivot table with one field of values, numbers to be
> > > > summed, and a second field, occasional two letter alpha codes which are purely
> > > > descriptive and refer to the number in the same column, "row".
> > > > In the source spreadsheet, I have formatted the column which contains these
> > > > codes as "Text". Text is supposed to remain unchanged; however when that
> > > > column of data is entered into the pivot table as a value, it is handled as
> > > > though it were numeric data, and these two letter codes do not appear.
> > > > Because the software apparently recognizes the values as non-numeric it
> > > > assigns a "Count" descriptor by default, but this only converts each instance
> > > > of a the text values to "1", thus losing their original identity.
> > > > All the possible definers of the field are numeric operators like "Count" and
> > > > "Sum", which do not apply to an alpha code, which is just supposed to be
> > > > represented literally. Further I see no way to manipulate this data field to
> > > > be recognized as text in the pivot table itself.
> > > > How do I get the pivot table to just take this alpha data as is?
> > > > Thanks,
> > > > Eric

  #6  
Old October 13th 09, 02:51 PM posted to microsoft.public.excel.misc
Richard A
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

It should show the max() or min() values, evaluated alpha-numerically, which
are options for Excel pivots and which work in SQL (even Access) despite
being aggregate functions there, too. Is Microsoft working on an add-in for
Excel that will allow this?

"Jim Thomlinson" wrote:

> Your only choice is row and column headers. Pivot tables aggregate data in a
> cube. The data secton shows the aggregation of those fields. It can not show
> text associated with those aggregations. The reason being is that the
> aggregation may contain values with more than one descriptor at which point
> what should it show.
>
> A simple example...
> Region / Location / Sales
>
> If I place the region in the left column and sales in the data section then
> the aggregation of the region will have multiple locations assocatied each
> data value. Now if I add location to the data section what should it show???
> --
> HTH...
>
> Jim Thomlinson


  #7  
Old July 22nd 15, 05:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

> In the data section of the pivot table what exactly were you wanting it to do?

I want to aggregate text values using concatenate instead of count. Or even to apply javascript-like join method on the array of aggregated text values.

Array.join('</li>\n<li>')
  #8  
Old October 14th 16, 05:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, July 22, 2015 at 9:45:14 AM UTC-7, wrote:
> > In the data section of the pivot table what exactly were you wanting it to do?

>
> I want to aggregate text values using concatenate instead of count. Or even to apply javascript-like join method on the array of aggregated text values.
>
> Array.join('</li>\n<li>')


Concatenate is also what I'm looking for.
  #9  
Old November 10th 16, 10:48 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

Or sometimes there is only one value per cubicle. I am struggling with such a scenario now. And it is a text field!!
  #10  
Old January 14th 17, 04:36 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

This does seem like an oversight on part of google..
I think the pivot should allow text values, and just display #N/A if multiple values match the column/row pair.

Given a table with a "unique key" of two columns, it would be very nice to be able to quickly pivot that into a readable grid.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Tables-Text Values? Eden397 Excel Discussion (Misc queries) 0 March 5th 09 01:27 PM
Calculating from values in pivot tables Owen Peck Excel Worksheet Functions 3 September 13th 07 03:31 PM
Pivot Tables - Ranking Values Jeff Reese Excel Discussion (Misc queries) 1 May 1st 07 03:44 PM
Pivot tables with zero/null values Todd1 Excel Discussion (Misc queries) 4 March 28th 06 12:45 PM
Supress Zero Values on Pivot Tables AROR Excel Discussion (Misc queries) 0 April 21st 05 01:23 AM


All times are GMT +1. The time now is 02:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.