Excel howto : Quickly ID a Column of Text Using a Perl Hack

Okay, that doesn't make sense. Here's what you want to do :


A
B
C
C
A
A
D
D
D
A

to turn to (you guessed it - for use in Pivot tables)

1
2
3
3
1
1
4
4
4
1

select all and pipe through (Yes, you see why you want Cygwin :)

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

This will give you
A,1
A,1
B,2
...

So you can easily paste this back in by doing the smart import..

Here's a braindead way to show text in Excel pivot table values area :

https://www.youtube.com/watch?v=wslp2BqHuz8

Shame on Contextures Inc.

Excel Pivot Table Torture

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..