Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Using text values in Pivot Tables

Hi Eric,

I understand your frustration with the pivot table not recognizing your alpha codes as text. However, there is a solution to this problem.

When you create a pivot table, Excel automatically tries to determine the data type of each column. In your case, it is recognizing the column with the alpha codes as numeric data because it contains numbers as well. To fix this, you can force Excel to recognize the column as text by adding a single quote (') before each alpha code.

Here's how to do it:
  1. In your source spreadsheet, insert a new column next to the column with the alpha codes.
  2. In the first cell of the new column, type the formula
    Formula:
    "'"&A1 
    (assuming your alpha codes are in column A). This will add a single quote before the alpha code in cell A1.
  3. Copy the formula down the entire column to apply it to all the alpha codes.
  4. Use the new column with the single quotes as the data source for your pivot table instead of the original column.

Now, when you create your pivot table, Excel will recognize the column with the single quotes as text and will display the alpha codes as is.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
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

  #4   Report Post  
Posted to microsoft.public.excel.misc
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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
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



  #6   Report Post  
Posted to microsoft.public.excel.misc
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

  #7   Report Post  
Posted to microsoft.public.excel.misc
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


  #8   Report Post  
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')
  #9   Report Post  
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.
  #10   Report Post  
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!!


  #11   Report Post  
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.
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

Not what I wanted to hear either, but if someone has a new solution to this.. I'd like something that would simply put my text into a list, either each item on a different line or comma separated.

My reasoning is that my church uses a schedule software that while it's great for the tech savvy, the print versions of schedules are lacking, with each person on their own line with fields for date, time, and service, and position they hold, I'd like to aggregate this data so that all people service on one day would be in there own box, and do this all in a way that a non-tech-savvy person could do each month. I thought pivot tables would be easy enough.

On Tuesday, September 22, 2009 at 3:10:02 PM UTC-5, Eric wrote:
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


  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

Seems like this has still not been implemented by Google. I am having to add a vlookup field as a separate field next to the pivot. I was hoping that max() would work on text fields as well.


On Tuesday, September 22, 2009 at 6:42:02 PM UTC+1, 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


  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')
  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, 15 May 2019 14:24:31 UTC-3, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


Tried this. Seemed like a good idea. But I just get "error" in each box.


  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, May 15, 2019 at 10:24:31 AM UTC-7, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


Thanks! This worked for me. Any idea how to make multiple appear if there are two or more text results? Right now it looks like this returns whatever is the top one in the data (not the most common), but I would like it to return all distinct.

--
-------------------------------------------------------
essenceglobal.com
<https://www.essenceglobal.com
Facebook
<http://www.facebook.com/essenceglobalÂ*€¢Â*Twitter
<http://www.twitter.com/essenceglobalÂ*€¢Â*YouTube
<http://www.youtube.com/essenceglobalÂ*€¢Â*Instagram
<http://www.instagram.com/essence_global
  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, May 15, 2019 at 10:24:31 AM UTC-7, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


Thanks! This worked for me. Any idea how to make multiple appear if there are two or more text results? Right now it looks like this returns whatever is the top line in the dataset (not the most common), but I would like it to return all distinct.
  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, May 15, 2019 at 1:24:31 PM UTC-4, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


This worked exactly for what I needed! I had a table of all unique values that I wanted to show in the rearranged pivot format.

Thank you!
  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

This worked exactly for what I needed!
This is the formula I used

=TO_TEXT(Project & char(10) & Description)

Thank you!

  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, May 15, 2019 at 7:24:31 PM UTC+2, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


Thank you!!!!!!!!!! Clear answer that works quick and great!!!!

--
Â**Firebird Tours*
Â* Â* Â* Â* Â*Award-winning Tours to EuropeÂ*
Â* Â* Â* Â* Â*Â*
www.firebirdtours.com <https://www.firebirdtours.com


  #21   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

The =TO_TEXT('field_title') solution worked for me.
But does anyone know how to display concatenated values if there are multiple values in multiple cells of that column?

thank-you!

On Tuesday, September 22, 2009 at 10:42:02 AM UTC-7, 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


  #22   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, May 15, 2019 at 1:24:31 PM UTC-4, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


This is VERY helpful. Thank you for this creative solution. Hopefully, in the future Google makes this a directly supported feature.

I will note if your original column header has a line break (carriage return) in it as such:

Sales
Person

then you will get the dreaded #ERROR! "Formula parse error".

There is a workaround I have found. Temporarily, change the column header you want to reference in the Calculated Field into a single word. In the case above, let's say we changed it to 'Person' (removing the word Sales and the following carriage return from the example above). Now go create the Calculated Field. In this case, the formula would be

=TO_TEXT('Person')

The field will be created in the Pivot Table without error. Now for the interesting part, you can go change the column name back to the original, and Google Sheets is then smart enough to handle the change.

Thanks so much to for the original idea to use the T0_TEXT function. This is a really creative solution.

  #23   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, 15 May 2019 18:24:31 UTC+1, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


Brilliant! Thank you!
  #24   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, May 15, 2019 at 10:54:31 PM UTC+5:30, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


Great... your solution saved a lot of time for me.
  #25   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Monday, August 3, 2020 at 4:34:49 AM UTC+2, wrote:
On Wednesday, May 15, 2019 at 10:54:31 PM UTC+5:30, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')

Great... your solution saved a lot of time for me.


Hey all, adding on top of what's already shared here, I figured out how to combine all values. 🎉

=TO_TEXT(CONCATENATE(Feedback))

I also needed to add new lines to all entries so that they make more sense.


  #26   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, 15 May 2019 at 22:54:31 UTC+5:30, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')

It's a great formula. Worked well. Thanks
I tried "JOIN" but failed. Can you show magic with this too?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Tables-Text Values? Eden397 Excel Discussion (Misc queries) 0 March 5th 09 02: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 03:52 PM.

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

About Us

"It's about Microsoft Excel"