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

Back to list

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.