RE: Generating Column Numbers on Two Variables

Stephens, Donald Sat, 03 Dec 2005 17:46:09 -0800

Hello everyone, wanted to re-submit the pseudo-code below as perl code that compiles and runs. I made some typos in my earlier email, sorry. Attached are the results, also please see routine "gen_col_num" for the logic.

# Results:

Result: [1,A] = 1
Result: [1,B] = 2
Result: [2,A] = 3
Result: [2,B] = 4
Result: [5,A] = 9
Result: [5,C] = 11 # works great on your special case Deane R.
Result: [6,A] = 12
Result: [6,B] = 13

# source code

use strict;

my ( $maxLetter ) = "c";
my ( $col ) = 0;

$col = &gen_col_num( 1, "A" );
print( "Result: [1,A] = $col\n" );

$col = &gen_col_num( 1, "B" );
print( "Result: [1,B] = $col\n" );

$col = &gen_col_num( 2, "A" );
print( "Result: [2,A] = $col\n" );

$col = &gen_col_num( 2, "B" );
print( "Result: [2,B] = $col\n" );

$col = &gen_col_num( 5, "A" );
print( "Result: [5,A] = $col\n" );

$col = &gen_col_num( 5, "C" );
print( "Result: [5,C] = $col\n" );

$col = &gen_col_num( 6, "A" );
print( "Result: [6,A] = $col\n" );

$col = &gen_col_num( 6, "B" );
print( "Result: [6,B] = $col\n" );

# Note(s): generate column number
sub gen_col_num {
my( $num, $letter ) = @_;
my( $maxRowValue, $result ) = ( 0, 0 );

if ( $num > 5 ) {
$maxRowValue = $num * ( ord(uc($maxLetter)) - 64 ) - 4;
} else {
$maxRowValue = $num * ( ord(uc($maxLetter)) - 64 ) - ( $num - 1 );
}

$result = $maxRowValue - ( ord(uc($maxLetter)) - ord(uc($letter)) );

return $result;
}

The same logic as I mentioned below applies. I made some typo mistakes below, my apologies for the confusion. :-)

Donald S.

From: Stephens, Donald
Sent: Saturday, December 03, 2005 3:03 PM
To: [EMAIL PROTECTED]; [email protected]
Subject: RE: Generating Column Numbers on Two Variables

If you treat the $num as rows and the $letter as columns you can get a table like:

A B C

_________

1 | 1 2 3

2 | 4 5 6

3 | 7 8 9

4 | 10 11 12

5 | 13 14 15

6 | 16 17 18

7 | 19 20 21

But because your letter ( or column ) count is not the same per row you really need this:

A B C

_________

1 | 1 2 3

2 | 3 4 5

3 | 5 6 7

4 | 7 8 9

5 | 9 10 11

6 | 12 13 14

7 | 15 16 17

So the trick is for each row ( or num for you ) calculate the MAX number ( i.e. 6,C = 14 ) and offset based on the column ( i.e. column B is 1 less then C, so subtract one giving 6,B = 13 ). Then one more thing is to treat a special case for the row ( or num for you ) when row is 6 or higher.

here is pseudo code:

$maxLetter = "C"; # maximum number of columns you need ideally

if ( $num > 5 ) {

$maxRowValue = $num * ( Asc( $maxLetter ) - 64 ) - 4;

} else {

$maxRowValue = $num * ( Asc( $maxLetter ) - 64 ) - ( num - 1 );

}

$result = $maxRowValue - ( Asc( $maxLetter ) - Asc( $maxLetter ) );

*Because I used 64, please make sure that your letter ( $maxLetter ) is capital for the offest to work.

Is this cleaner, only 7 lines :-)

Hope this helps. Also it may be easier if you visualize how Excel turns "A" into 1 and "AA" into 27. Thanks.

Donald Stephens

-----Original Message-----
From: [EMAIL PROTECTED] on behalf of [EMAIL PROTECTED]
Sent: Fri 12/2/2005 1:50 PM
To: [email protected]
Cc:
Subject: Generating Column Numbers on Two Variables

Gurus,

I've fiddled with this table and so far a reasonable, or at least pretty, solution's evaded me. Given the values of the first two columns as inputs, I need the column number specified in the third as my output. Note that there's an oddball value for $letter in row number 11--this is what's throwing me the curve. I've got a lumpy looking chunk of code that does it, but I'd prefer something cleaner... Any ideas?

$num $letter $col
1 A 1
1 B 2
2 A 3
2 B 4
3 A 5
3 B 6
4 A 7
4 B 8
5 A 9
5 B 10
5 C 11
6 A 12
6 B 13

My lumpy code:

if (($num < 6) && ($letter ne 'C')) {
$x = ($letter eq 'B' ? 0 : 1;
$col = ($num * 2) - $x;
}
elsif ($num == 5) {
$col = 11;
}
else {
$x = ($letter eq 'B') ? 1 : 0;
$col = 12 + $x;
}

Can anyone suggest a prettier alternative?

Thanks,

Deane





-----------

The information contained in this e-mail message, and any attachment, is confidential and for use solely by the intended recipient.  If you have received this message in error, please delete this message immediately.  Although Moody's KMV makes every effort to protect its computing environment from malicious code, Moody's KMV is not responsible for any virus or other type of suspect code that may be transferred via this e-mail message.

_______________________________________________
ActivePerl mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

RE: Generating Column Numbers on Two Variables

Reply via email to