loop through data to get all variations.

[hopkinsc]

Hi everyone.

I need help with writing a script to read through my data and generate a new table for all variations .

Here is a sample of how the data looks

CC From      CC To             EC From       EC To                 Description

01051	01061		854-	890-	Servicemen
01205	01205		850-	852-	Servicemen
01205	01205		854-	890-	Servicemen
01008	01009		853-	853-	Materials and consumables
01008	01009		901-	928-	Materials and consumables
01008	01009		931-	944-	Materials and consumables
01008	01009		905-	913-09	Materials and consumables
01008	01009		957-	957-	Materials and consumables
01008	01009		963-	981-	Materials and consumables
01012	01012		853-	853-	Materials and consumables
01012	01012		901-	928-	Materials and consumables

So what i want to do is get all variations of CC From - CCTo in one field rather than 2.

eg

the first line is 01051 01061, so in the new field i would have the following..

01051

01052

01053

01054

01055

01056

01057

01058

01059

01060

01061

Then loop through again to do line 2 etc etc

I need all of the relationships still to be there too, so when 01053 is selected, i still want to see EC details from 854-890 and for servicemen.

I want to do the same for ECFrom and ECTo but these fields contain additional characters.

Eg

905 to 913-09 , i would need a table containing

905

906

907

908

909

910

911

912

913-00

913-01

913-02

913-03

913-04

913-05

913-06

913-07

913-08

913-09

Can anyone help please? .

[Jason_Michaelides · Accepted solution]

Well that was a challenge!  See attached.  There are probably a few too many steps and the script could be smartened up a bit but you get the idea...

[hopkinsc]

Thanks a lot Jason... I am just about to leave the office, but i will look at this at home and give you feedback tomorrow

.

[hopkinsc]

Damn you are good!

Thanks very much for your help!!

[hopkinsc]

Hi jason, Just a quick question on your script..

What does zi mean? I have seen it a few times in different examples..

As you can probably tell, i am still learning!

Thanks

Chris.

[Jason_Michaelides]

It's just a variable name - doesn't mean anything really!  If you did a find and replace changing zi to dolphins it would still work.  I thik "i" is a common numerator in programming parlance and zi has just made its way from one community post to another...