Challenge 60: Excelling and Varying
TASK #1 › Excel Column
Reviewed by: Ryan Thompson
Write a script that accepts a number and returns the Excel Column Name it represents and vice-versa.
Excel columns start at A and increase lexicographically using the 26 letters of the English alphabet, A..Z. After Z, the columns pick up an extra “digit”, going from AA, AB, etc., which could (in theory) continue to an arbitrary number of digits. In practice, Excel sheets are limited to 16,384 columns.
Example
Input Number: 28>Output: AB
Input Column Name: AD>Output: 30
The simplest way would be to just create a hash that would have the approprite mapping, and to reverse it, you’d just reverse keys and values.
sub all_excel () {
my $done = {};
my $output = {};
my $key = 1;
for my $i ( '', 'A' .. 'Z' ) {
for my $j ( '', 'A' .. 'Z' ) {
for my $k ( 'A' .. 'Z' ) {
my $col = join '', $i, $j, $k;
next if $done->{$col}++;
$output->{$key} = $col;
$key++;
}
}
}
return $output;
}
That’s all well and good, I suppose, but it seems like that would take memory. We can be smarter than that, can’t we?
From Number To Column Name
A few notes: We must consider what A means, especially in context of 28 <=> AB, which means A == 1, but AA is the equivalent of 10 in Base Excel not 11. This means that we have to distinguish A to Z from A_ to Z_, etc.
But basically 123 equals 3 + 10*2 + 1*102, and so AAA is 0 + 1 * 26 + 1 * 262 converted to A-Z.
This, again, looks like a job for recursion.
my %alpha = map { state $c = 0; $c++ => $_ } 'A' .. 'Z';
my %ahpla = reverse %alpha;
sub to_excel_col1 ( $i, $f = 0 ) {
$i = int $i;
croak 'out of range' if $i < 0 || $i > 16384;
croak 'out of range' if $i == 0 && $f == 0;
$i -= 1 unless $f;
my $mod = $i % 26;
my $num = int $i / 26;
my $l = $f ? $alpha{ $mod - 1 }
: $alpha{$mod};
return join '', to_excel_col1( $num, 1 ), $l if $num > 0;
return $l;
}
There And Back Again
As we we divide a number to find the number and thus the letter for the column previously, here we convert then multiply.
Above, I create a hash named %alpha that’s { "A" => 1, "B" => 2 ... } and, when I wanted { 1 -> "A", 2 => "B" ... }, I used reverse and named it %ahpha, which might not be the best naming convention.
# looking back at this, I'm reminded why we should
# use more verbose variable names.
sub from_excel_col1 ( $c, $f = 0 ) {
$c =~ s/\W//g;
$c = uc $c;
my @c = split //, $c;
my $o = 0;
my $l = pop @c;
my $v = $ahpla{$l};
!$f && $v++;
$o += $v;
if ( scalar @c ) {
my $d = join '', @c;
my $e = from_excel_col1( $d, 1 );
$o += 26 * ( 1 + $e );
}
return $o;
}
Code and Results
#!/usr/bin/env perl
use strict;
use warnings;
use utf8;
use feature qw{ postderef say signatures state switch };
no warnings qw{ experimental };
use Carp;
my $all = all_excel();
my $lla->%* = reverse $all->%*;
my %alpha = map { state $c = 0; $c++ => $_ } 'A' .. 'Z';
my %ahpla = reverse %alpha;
for my $i ( sort { $a <=> $b } 1 .. 40, 100, 1000, 10000 ) {
my $e = to_excel_col1($i);
my $r = from_excel_col1($e);
my $ch1 = $all->{$i};
my $ch2 = $lla->{$e};
say join "\t", '--', $i, $e, $r, '', $ch1, $ch2;
}
# first row is different, because instructions assume
# we start with row 1, but things become so much easier
# with a zero index
sub to_excel_col1 ( $i, $f = 0 ) {
$i = int $i;
croak 'out of range' if $i < 0 || $i > 16384;
croak 'out of range' if $i == 0 && $f == 0;
$i -= 1 unless $f;
my $mod = $i % 26;
my $num = int $i / 26;
my $l = $f ? $alpha{ $mod - 1 } : $alpha{$mod};
return join '', to_excel_col1( $num, 1 ), $l if $num > 0;
return $l;
}
sub from_excel_col1 ( $c, $f = 0 ) {
$c =~ s/\W//g;
$c = uc $c;
my @c = split //, $c;
my $o = 0;
my $l = pop @c;
my $v = $ahpla{$l};
!$f && $v++;
$o += $v;
if ( scalar @c ) {
my $d = join '', @c;
my $e = from_excel_col1( $d, 1 );
$o += 26 * ( 1 + $e );
}
return $o;
}
sub all_excel () {
my $done = {};
my $output = {};
my $key = 1;
for my $i ( '', 'A' .. 'Z' ) {
for my $j ( '', 'A' .. 'Z' ) {
for my $k ( 'A' .. 'Z' ) {
my $col = join '', $i, $j, $k;
next if $done->{$col}++;
$output->{$key} = $col;
$key++;
}
}
}
return $output;
}
-- 1 A 1 A 1
-- 2 B 2 B 2
-- 3 C 3 C 3
-- 4 D 4 D 4
-- 5 E 5 E 5
-- 6 F 6 F 6
-- 7 G 7 G 7
-- 8 H 8 H 8
-- 9 I 9 I 9
-- 10 J 10 J 10
-- 11 K 11 K 11
-- 12 L 12 L 12
-- 13 M 13 M 13
-- 14 N 14 N 14
-- 15 O 15 O 15
-- 16 P 16 P 16
-- 17 Q 17 Q 17
-- 18 R 18 R 18
-- 19 S 19 S 19
-- 20 T 20 T 20
-- 21 U 21 U 21
-- 22 V 22 V 22
-- 23 W 23 W 23
-- 24 X 24 X 24
-- 25 Y 25 Y 25
-- 26 Z 26 Z 26
-- 27 AA 27 AA 27
-- 28 AB 28 AB 28
-- 29 AC 29 AC 29
-- 30 AD 30 AD 30
-- 31 AE 31 AE 31
-- 32 AF 32 AF 32
-- 33 AG 33 AG 33
-- 34 AH 34 AH 34
-- 35 AI 35 AI 35
-- 36 AJ 36 AJ 36
-- 37 AK 37 AK 37
-- 38 AL 38 AL 38
-- 39 AM 39 AM 39
-- 40 AN 40 AN 40
-- 100 CV 100 CV 100
-- 1000 ALL 1000 ALL 1000
-- 10000 NTP 10000 NTP 10000
TASK #2 › Find Numbers
Reviewed by: Ryan Thompson
Write a script that accepts list of positive numbers (@L) and two positive numbers $X and $Y.
The script should print all possible numbers made by concatenating the numbers from @L, whose length is exactly $X but value is less than $Y.
Example
Input:
@L = (0, 1, 2, 5);
$X = 2;
$Y = 21;Output:
10, 11, 12, 15, 20
Some points here. 11 shows us that digits repeat. Using 1 in the ones spot doesn’t keep it out of the tens spot.
But 00 and 01, two valid numbers, do not count as length 2, so there’s a cast to integer.
The Code and the Results
#!/usr/bin/env perl
use strict;
use warnings;
use utf8;
use feature qw{ postderef say signatures state switch };
no warnings qw{ experimental };
my ( $x, $y, @l ) = grep { $_ >= 0 } map { int $_ } @ARGV;
$x //= 2;
$y //= 21;
@l = ( 0, 1, 2, 5 ) unless scalar @l;
say qq{X: $x };
say qq{Y: $y };
say q{L: } . join ', ', @l;
my @vars = get_variations( \@l, $x );
say qq{All variations of length $x:\n\t} . join ", ", @vars;
@vars = get_lt_variations( \@l, $x, $y );
say qq{All variations of length $x that are < $y:\n\t} . join ", ", @vars;
exit;
sub get_lt_variations ( $arrayref, $x, $y ) {
return grep { $x == length $_ && $_ < $y } get_variations( $arrayref, $x );
}
sub get_variations ( $arrayref, $depth ) {
my $output = [];
return $arrayref->@* if $depth <= 1;
for my $i ( 0 .. -1 + scalar $arrayref->@* ) {
my $s = $arrayref->[$i];
push $output->@*,
map { int $s . $_ } get_variations( $arrayref, $depth - 1 );
}
return $output->@*;
}
X: 2
Y: 21
L: 0, 1, 2, 5
All variations of length 2:
0, 1, 2, 5, 10, 11, 12, 15, 20, 21, 22, 25, 50, 51, 52, 55
All variations of length 2 that are < 21:
10, 11, 12, 15, 20