Excel 2003 VBA combinatorial algorithm

Topic

New Reply

[MoonView]

I am an amateur coder and could use some help from those of you who have more experience.  I have a subscription to office 365 but prefer to use my old standalone Excel 2003.  I am looking to create a macro to test each set of N unique pairs  from 2N items, where the pair order does not matter and each item is used once and only once in each set of pairs.  I have used several different AI systems to generate the algorithm to find all such pairs, but the results of each search, when included in my macro, resulted in errors that I was not able to correct.  If anyone has developed a working algorithm to solve this I would appreciate receiving a copy.  Thank you.

Examples for N=3 6 Items=ABCDEF

set1 AB|CD|EF

set2 AB|CE|DF

set3 AB|CF|DE

…

• This topic was modified 1 year, 6 months ago by MoonView.

Reply | Quote

Replies

Posting a screenshot of a spreadsheet, or a sample sheet would make this easier.

cheers, Paul

Reply | Quote

Chat-GPT-Code-and-Respons-
Paul, thank you for reviewing my post.  Phrasing a question to AI does not always produce the answer expected.  Since my original post I have been trying to ask my question in different ways.  I have finally received an answer that almost solves my problem.  The algorithm I received successfully runs to completion but doesn’t quite give me what I want.  Attached is a copy of Chat GPT’s response to my question.  I’ve run the macro as is except I changed the starting point for the display from “A1” to “A2”.  Attached is a copy of the macro’s results plus an expansion showing what I am seeking.  I think the algorithm is close enough to what I want but in my limited experience I suspect it would take me much longer to make the needed change where someone with more experience could look at the code and know right away what needs to be altered.  I’m hoping you or someone else who reads this post can help

Thank you again for your time in reviewing my original post and this reply.

Reply | Quote

Without the original data we are flying in the dark without instruments.

cheers, Paul

Reply | Quote

Paul,

Thanks again for responding.  I’m not sure what you mean by “original data”.  For my purposes, assume the data consists of the 14 unique items designated as 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 and 14.  I need the algorithm that I found to be corrected to produce all unique sets where each set consists of 7 unique pairs of 2 items and where each of the 14 items is used once and only once in each set and the order within a pair does not matter.  I can readily use that algorithm for the rest of what I need to do in my program.  I am also confident I can then modify the code for situations where I have 12 items or 16 items, which are the most likely situations I will encounter, although it will likely also work for any even number of items greater than 4.  Each pair in a set has an associated value.  My program is intended to find the set (or sets) that optimizes the sum of those values.

Reply | Quote

I mean, post a spreadsheet (without macros).

cheers, Paul

Reply | Quote

This is a message to all who have read my post and public replies by Paul and to Paul who has looked at my posts and has tried to find a way to help.  As I mentioned in one of my replies to Paul, the Chat GPT algorithm I finally received was close enough to what I was looking for that I believed it provided a good starting point to get the answer I needed.  I was concerned about messing with the code and not being able get better results.  I thought I needed to expand the number of loops in the nested loop process to expand the number of 2-item pairs in the output.  I even tried this approach but only managed to mess up the code without improving the process.

I then took a hard look at the output being generated by the original code and realized all of the information I needed was already being generated and some simple string manipulation would produce the pair results I was seeking.  That insight allowed me to expand the original code to construct a generic process that would find every unique set of 2-item pairs for any even item count from 6 items to 22 items, which is the complete range of item counts I will ever face in my current project.  My process will produce every unique set of 2-item pairs that meet the following conditions:

• any even number of items between and including the range of 6 items to 22 items
• the order of the items within a pair does not matter (my output of the pair A-B will always have A less than B)
• the order of the pairs does not matter (my output of the pairs A-B, C-D, E-F will always have A < C < E etc)
• every item will be used once and only once
• 6 items will produce 9 unique sets of 3 2-item pairs, 8 items will produce 23 unique sets of 4 2-item pairs, 10 items will produce 77 unique sets of 5 2-item pairs, 12 items will produce 203 unique sets of 6 2-item pairs, 14 items will produce 453 unique sets of 7 2-item pairs, 16 items will produce 895 unique sets of 8 2-item pairs, 18 items will produce 1,613 unique sets of 9 2-item pairs, 20 items will produce 2,707 unique sets of 10 2-item pairs and 22 items will produce 4,293 unique sets of 11 2-item pairs,

For anyone interested I am attaching an Excel 2003 .xls file with the macro you can use to test the code.  For testing purposes the code produces output to the test worksheet for every major step on the way to getting the unique set of 2-item pairs. For those of you who are much better coders than I am, I’m sure you can find ways to streamline and speed up the processes in my macro.

Reply | Quote