Need VBA code to create all combinations of a routing.

Are the results that you want shown in columns G:I? If so why isn't the 7th combination that I created included?

Thanks Martin. Answering from my phone and not looking at my spreadsheet but I think station 3 only has 2 process centers so your rtg 7 wouldn't exist if that is indeed true.

You are correct. Can you tell me your thought process in creating G:I? In other words if you can spell out your mental process, I can duplicate it in code.

Sure. For one product, the number of total routings (TR) = NrStations x Product(Yn) where Y is NrPCs at Station n.
For Column G, the routing numbers are from 1 to TR.

Each routing has m steps where m = NrStations. That's what goes in column H.

The trick is for column I.  For the first routing. I start out with PC 1 for each station.
For routing 2, 3 and I incremented the PC for station 2.
For routing 4 and 5 and 6, I incremented the PC for station 3 to 2 (since there were only 2) and then went through and incremented station 2 again.

That gave me all the combinations for routings.

Thanks, I'll work on this tomorrow unless someone beats me to it.

One thing I need to do is to determine the number of stations. There are several ways to do that, but the easiest would be to look at the last value in column "C" but I can only do that if the stations always start at 1 and are increment by 1 as in the posted workbook. Will that always be the case or could for example there be 5 stations numbered 3, 4, 7, 9 and 10?

The number of stations and number of process centers at each station are givens but they can be variable. See the original entry on this question. The stations don't have individual numbers so they could be incremented however you choose. if you're thinking of putting numbers to stations. The order of stations in a routing will always be from top to bottom in the list. It's better to increment them by more than one, say by 5, so that stations can be inserted, if needed, in the routings after the routings are created. The enables easy insertion of stations without having to renumber the whole routing.

Thanks.

I apologize but I guess I don't really understand this:

For routing 2, 3 and I incremented the PC for station 2.
For routing 4 and 5 and 6, I incremented the PC for station 3 to 2 (since there were only 2) and then went through and incremented station 2 again.

Could you try again please?

Look at column I in the spreadsheet. Routing 1 uses PC1 at all stations--simple. For routings 2 and 3, I change only the PC number for Station 2. There are three PCs in station 2.  I have already used PC1 for Station 2 in Routing 1 so I make two more routings, all other Station PCs as PC1 and I change the PC for Station 2. In Routing 2, Station 2's PC is PC2. In Routing 3, Station 2's PC is PC3. So I have now used all combinations for all PCs at Station 2 where every other station's PC is 1.

I followed this logic until I covered all combinations for all Stations. Does that help?

I still don't get it but let's talk about this for a minute.

The stations don't have individual numbers so they could be incremented however you choose. if you're thinking of putting numbers to stations. The order of stations in a routing will always be from top to bottom in the list. It's better to increment them by more than one, say by 5, so that stations can be inserted, if needed, in the routings after the routings are created. The enables easy insertion of stations without having to renumber the whole routing.

What does "The stations don't have individual numbers" mean?

When you say "It's better to increment them by more than one, say by 5", which column are you talking about?

And incrementing by more than one probably won't be necessary because the code could, if you like, completely replace columns G:I any time a change is made to a column that you want to have trigger the replacement. I assume that those "trigger" columns are C:E but let me know.

Now back to the routing. These may be dumb questions but if I can get simple answers maybe I can understand the process.

• "Routing 1 uses PC1 at all stations" Why?
• "For routings 2 and 3, I change only the PC number for Station 2." Why?

You show these 6 combinations of numbers in column "I".
1111
1211
1311
1121
1221
1321

• Why for example is there no 1131?
• What are there 6 of them?

Martin, thanks for your persistence. In answer to your questions:
1) What does "The stations don't have individual numbers" mean?
2) When you say "It's better to increment them by more than one, say by 5", which column are you talking about?
>>> IGNORE the comments about individual numbers for stations and incrementing by more than one--too much information. I can take care of that once I get the routings.

3) "Routing 1 uses PC1 at all stations" Why? >>>> It's the simplest way to start.
4) "For routings 2 and 3, I change only the PC number for Station 2." Why? >>>> because I'm stepping through the combinations in order of stations. Station 2 has 3 PCs. Station 2's PC 1 was used in Routing 1 so I don't need to use it again when holding all other PC numbers to 1. See the first three combinations below.

You show these 6 combinations of numbers in column "I".
 1111
 1211
 1311
 1121
 1221
 1321
5) Why for example is there no 1131? >>> because there are only 2 PCs at station 3.
6) What are there 6 of them? >>>> that's the maximum number of combinations. The number of process centers at each station are 1,3,2,1 respectively. From combinatorial logic, the total number of combinations will be 1x3x2x1 = 6. (I think)

OK I think I'm getting it. Two more questions should clear it up.

• I assume that there are four numbers in each set because there are four stations in your workbook, and if there were 15 stations there would be 15 numbers in each set, correct?
• Why in this case is the fourth number always one?

1) correct
2) because there is only 1PC in the 4th station

Why isn't cell I9 = 2 and cell I21 = 1?

In routings 1, 2 and 3, I'm varying the process centers at Station 2(there are 3 PCs) and keeping every other station's PC as 1. That's why I9 is 1, it's for Station 3, not Station 2.

In routings 4, 5, and 6, Station 3 process center is set to 2 and I vary the process centers in Station 2. This is why I17, I21 and I25 are all 2.

Since Station 1 and Station 4 only have 1 PC, there's nothing to change for either of those.

Is it possible that my values are correct? Here's how I thought it worked. In column "I" there are sets of numbers and in the posted workbook there are 4 stations so each set has 4 numbers, the first number being for station 1, the second for station 2, the third for station 3 and the fourth for station 4.

Now since there is only one PC for station 1, cells I3, I7, I11, I15, I19 and I23 are all "1". There are three PCs for station 2 so I4 is "1", I8 is "2" and I12 is "3" and that pattern repeats in cells I16, I20 and I24. There are two PCs for station 3 so I5 is "1", I9 is "2" and that pattern repeats for I13 and I17, and again for I21 and I25. There is only one PC for station 4 so cells I6, I10, I14, I18, I22 and I26 are all "1".

I don't see an attachment with your numbers but, from your description, I9 would be 1 not 2.  In routings 1, 2 and 3, I'm varying the process centers at Station 2(there are 3 PCs) and keeping every other station's PC as 1. That's why I9 is 1, it's for Station 3, not Station 2.

You may have the same result (all 6 routings) but in a different order. I would have to see your spreadsheet to know.

Okay, I get the concept.  So what would the "input" to this macro code look like?  Trying to understand if it would need to determine the number of stations and centers, or if those are input as numbers, etc.

~bp

Well I have a workbook that works for the data in your workbook, but when I tried to expand it for more stations there were some errors. That workbook is attached. The red and green cells were manually added and represent what I feel are the desired results.
29011040.xlsm

Either but to keep it simple, assume the number of stations and process are given but can vary. Thanks.

I will continue to work on this until it can handle more/less data but let me ask you this, do you want the routes to be generated by manually running the macro (either by clicking a button or going to Visual Basic and running it from there) or automatically when a change is made to columns C, D, E (or some other set)?

From my perspective, the permutation logic is easy, just multiple nested loops, etc.  But before I write code I need to understand (see the real sample) of that the input sheet will look like that the macro will have to process to produce the output data.  I'm reluctant to code something until that is known since much of the logic will relate to that, and will change if that format changes.

~bp

See attached file for data format. In retrospect, it would be great if the code could determine the number of stations and process centers before creating the routing combinations.

Not sure if it makes any difference, but note that every part does not use all process centers at a station. Part_A uses 4 process centers at Station 12 but Part_B can only use 3.
RoutingsDataFormat.xlsm

Martin, Thanks for all your work. I am currently swamped with other stuff but will get to it to test the solution next week.

OK.

Sorry Martin, I have not looked at it yet. Just got back from a trip to Malaysia. Will get to it this week. thanks for your patience.

Thanks Martin. Finally got around to testing it. Looks good to me!  Thanks for your persistence and patience.

Ed

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016