Home |
Search |
Today's Posts |
#11
![]() |
|||
|
|||
![]()
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 |
#12
![]() |
|||
|
|||
![]()
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 |
#13
![]() |
|||
|
|||
![]()
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') |
#14
![]() |
|||
|
|||
![]()
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. |
#15
![]() |
|||
|
|||
![]()
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 <http://www.facebook.com/essenceglobalÂ*•Â*Twitter <http://www.twitter.com/essenceglobalÂ*•Â*YouTube <http://www.youtube.com/essenceglobalÂ*•Â*Instagram <http://www.instagram.com/essence_global |
#16
![]() |
|||
|
|||
![]()
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. |
#17
![]() |
|||
|
|||
![]()
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! |
#18
![]() |
|||
|
|||
![]()
This worked exactly for what I needed!
This is the formula I used =TO_TEXT(Project & char(10) & Description) Thank you! |
#19
![]() |
|||
|
|||
![]()
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 |
#20
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Pivot Tables-Text Values? | Excel Discussion (Misc queries) | |||
Calculating from values in pivot tables | Excel Worksheet Functions | |||
Pivot Tables - Ranking Values | Excel Discussion (Misc queries) | |||
Pivot tables with zero/null values | Excel Discussion (Misc queries) | |||
Supress Zero Values on Pivot Tables | Excel Discussion (Misc queries) |