0

I am attempting to pivot some data using Excel; however, the text (non-numeric) values are being converted to zero (0). The format of the cells of the entire worksheet are in Text; I have tried other formats including all cells as General, and a variety of combinations involving both formats. The actual values (including both text and numeric) are visible when selecting the little down arrow that appears to the right of Value when the mouse-pointer hovers over it in the Fields Section to the right of the main field.

What must I do to get the text values to appear?

Pivot Table

Erg
  • 69
  • 7
  • What is your question? – Ron Rosenfeld Nov 05 '17 at 01:04
  • 1
    It seems that my question 'What must I do to get the text values to appear?' succeeding the summary and preceding the image is somehow unclear; therefore, I apologize and further state that in place of the zero (0) values under Column1, Column2, and Column3 in the pivoted table should be the text values abc, def, and ghi from the original table. I hope that makes sense. – Erg Nov 05 '17 at 01:20
  • I understand that. But what if there is more than one text value in a given Group/Column ? – Ron Rosenfeld Nov 05 '17 at 01:37
  • 1
    @pnuts You can pivot and choose to not aggregate with `Get & Transform`. But if you have multiple entries, for the same Group and Column number, that won't work. – Ron Rosenfeld Nov 05 '17 at 02:25

1 Answers1

1

If there is no more than one value for each Group/Column pair, and if you have Excel 2010 or later, you can use Get & Transform or Power Query to Pivot the Name column and chose the Do Not Aggregatge option.

If you have multiple entries for each Group/Column pair, you will need a different solution.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Ok; so looks like I am using Excel 2007 but if I get a newer version then I will look into the solutions involving Get & Transform or Power Query but I am surprised by such a limitation. There will only be a single entry for each Group/Column pair. – Erg Nov 05 '17 at 03:06
  • It is exasperating to see that my question has been down-voted for some reason; does it 'not show any research effort', or is it 'unclear or not useful'? I did research online to find a solution to this issue but I do not know in what way or to what extent such research is expected to be documented or conveyed that would satisfy the one with a such a propensity to down-vote; also, I am at a loss at how I am to make the question much clearer, and finally the question and answer is certainly useful to me if not to others. I would not have made the effort to post here otherwise. – Erg Nov 05 '17 at 03:34
  • Apparently, there is little recourse to such an action and is acceptable punishment due someone who attempted to seek advice from the 'experts'. – Erg Nov 05 '17 at 03:34
  • @Erg I suggest a thick skin. I recently had a year old accepted answer of mine that had 6 upvotes receive a downvote with no stated reason. Some people are like that – Ron Rosenfeld Nov 05 '17 at 03:43
  • @Erg You could develop a VBA solution to do what you require. I would use collections (or dictionaries) and a user defined object. You could probably develop a formula solution, but my guess is it would be complex and difficult to maintain. – Ron Rosenfeld Nov 05 '17 at 11:43
  • @pnuts It was for the Q which had six upvotes. It was a programming issue. (The A was by me) and, just checking, it was two years old: [Wrong Excel window in focus after workbook open](https://stackoverflow.com/questions/34463010/wrong-excel-window-in-focus-after-workbook-open) – Ron Rosenfeld Nov 05 '17 at 13:00