asked on

Excel VBA Script for generating combinations

Hello Experts,

I need help with a script to generate a table of all PartID combinations with the following constraints:
Each PartID has a SeriesID = 1 to 7.
Each PartID has PartTypeID = A, B, C or D.
PartTypeA are connected to PartTypeB
PartTypeB are connected to PartTypeC
PartTypeC are connected to PartTypeD

The attached spreadsheet shows the possible combinations for Series 1, which I did manually. I would like a script that would can do it for all Series.

Hope the example is clear!

Cheers,

-Tom
combodata-ee.xlsx

Bill Prew

So, you want a routine that reads down the "data" sheet, and generates both the "twopartcombo" and "fourpartcombo" sheets?

~bp

tomfolinsbee

ASKER

yes, that's right.

tomfolinsbee

ASKER

Update:
I no longer need the two part combo sheet.
I only need the four part combo sheet.
Cheers,

tomfolinsbee

ASKER

I've moved the project to Access. So now looking for a query instead of a script.

Restating the problem:

I have a table with three fields: SeriesID, PartTypeID and PartID.
(showing records only for SeriesID = 1)
1      1      3
1      1      7
1      2      6
1      2      8
1      3      4
1      4      5

I'm looking for a query that will return all possible combinations of PartID subject to contraint that every combination must have same Series and one and only one of each PartType.

Thanks!

Tom

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tomfolinsbee

ASKER

Sorry, I should have mentioned that when I moved to access, I changed the output table to ComboID, SeriesID, PartID, PartTypeID

Thanks for your help with this!

Tom

Bill Prew

So you would want to see 4 lines for the same ComboID then?

~bp

tomfolinsbee

ASKER

Yes, that's right.

tomfolinsbee

ASKER

bp, thanks for your help with this. It's exactly what I asked for in the original question.

For my application, I used your query to make a table (Model). I then used Union query to merge the four columns into one colum.

Cheers!

Bill Prew

Great, sorry I hadn't gotten back to do more yet, but glad that helped.

~bp