RocketMan on 15/10/2010 at 00:51
Ok really quick question about excel and I'd be real happy if someone had an answer for me. I have to generate thousands upon thousands of part numbers wherein all permutations are listed. Each chunk of the long string of alphanumeric characters is like a variable and can take multiple values so I have to start at the end of the string and work my way left cycling through all possible permutations. It's a bit hard to explain but lets say it's: 12A70-X56-2B. The next one would be 12A70-X56-2C and so on until K for example. Then I start with the next digit, going from 2 to 9 for example and for each of those I still have to cycle through the letters I just did and so on and so forth until I work my way left to the beginning.
I know that excel can automate this in much the same way that it figures out how to increment numbers if cell A1 is 1, A2 is 2 and A3 is 3 and you highlight those cells and drag the corner down, it will fill in 4,5,6,7, etc. However this part number is much more complicated and I will need to increment pieces in the middle which may be 1 or more characters, may be alpha or numeric, etc. In otherwords I need to increment wildcards within the part number. I know you can do this by writing a program using VBA but I don't want to do that. I believe there's a formula/command that can be entered into the cell to achieve the same effect. Does anyone know how to do this? Thanks.
RocketMan on 15/10/2010 at 02:09
Right. This is exactly what I'm trying to do on a more complicated level. I will have several sets available to me {A1, A2, A3, A4}, {B1, B2, B3}, etc. each of which represents the possible states of each piece of the part number. It'd be like going through the 7 days of the week for an entire year and listing it in the form: MonthWeekDay. Since this part number contains about a dozen variables, each of which may have half a dozen states or so, the total number of part numbers is astronomical and I think it would take me quite some time to create them all manually. A few searches on google looked promissing at first but I was unable to adapt the specific examples to suit my needs because they were not generic enough and I wasn't able to figure out how the formula worked. :(
Mortal Monkey on 15/10/2010 at 15:31
Quote Posted by RocketMan
I will have several sets available to me {A1, A2, A3, A4}, {B1, B2, B3}, etc. each of which represents the possible states of each piece of the part number.
So, given the digits A, B and C, you want C to change with every unit, B to change with every N<sub>C</sub> units, and A to change every N<sub>B</sub>*N<sub>C</sub> units.
* Make another column D with the natural numbers.
* Colum C is equal to D translated (index an alphabet column, or use CHAR()).
* Column B is equal to D divided by N<sub>C</sub>, floored then translated.
* Column A is equal to D divided by N<sub>B</sub>*N<sub>C</sub>, floored then translated.
BEAR on 15/10/2010 at 15:34
Maybe this would be easier done in java or c++ or some other language, output to a tab delimited text file and then imported into excel?
Mortal Monkey on 15/10/2010 at 17:01
Well, here's a little Perl program I cooked up:
Code:
#!/usr/bin/perl
use strict;
use warnings;
use constant 'DELIMITER' => "\t";
$0 =~ /([^\\\/]*)$/;
die "Usage: $1 <start> <end>\n" unless @ARGV == 2;
my @start = split //, shift @ARGV;
my @end = split //, shift @ARGV;
die "Length of <start> is not equal to length of <end>.\n" unless @start == @end;
print join('', @start);
my @permutation = @start;
my $digit = -1;
while (1)
{
if ($permutation[$digit] eq $end[$digit])
{
$permutation[$digit] = $start[$digit];
$digit--;
last if -$digit > @end;
}
else
{
$permutation[$digit]++;
$digit = -1;
print &DELIMITER . join('', @permutation);
}
}
RocketMan on 16/10/2010 at 00:10
Hey guys. Thanks for replying...I'm just checking this now but I had used the method Bear described.....wayyyyy easier to make a java program output to csv than to screw around with excel. I think it's still value added to learn how to do it in excel though. You don't always have a JDK at your disposal. I had to get it approved for installation this morning. You'd think that this sort of thing (arbitrary sequences and series) would be a commonly used feature and that it'd be easy to implement.
Mortal Monkey on 16/10/2010 at 01:37
Quote Posted by RocketMan
wayyyyy easier to make a java program output to csv than to screw around with excel.
Well, duh. I figured you weren't the programmer type though, what with using Excel in the first place.
RocketMan on 16/10/2010 at 03:53
Quote Posted by Mortal Monkey
Well, duh. I figured you weren't the programmer type though, what with using Excel in the first place.
Unfortunately it's the tool of choice where I work and half the ppl there haven't even heard of Java.
zombe on 16/10/2010 at 11:27
Quote Posted by RocketMan
You don't always have a JDK at your disposal.
But you do have a browser and ~notepad everywhere => javascript rules :)
