Excel Pivot Table Torture

Back to list

Python pandas can do it, but, of course, M$ Excel can't. When will Redmond learn from open s?

You want values that are plain text, not numbers. What can you do?

The first order of business is to create an accompanying column of numbers - a unique number for each text item. That is, if your column has

A
A
B
D
E
F
A
C

Then, you want

A 0
A 0
B 1
D 2
E 3
F 4
A 0
C 5

perl -n -e 'BEGIN{%table=(); $count=0} chomp; unless( defined $table{$_} ){   $table{$_} = $count++; } print "$_," and print $table{$_} and print "\n";' 

(Meaning, copy that one column into a text editor - aka NEdit - in Cygwin, and then pipe through the above perl script :) (See why you need unix? :)

Then, you run into the other roadblock. You add this column to your existing Table, and the Pivot Tables simply don't see it. WT*? Luckily, "how to refresh pivot table options" with Google delivers - and works..