Text Combinations

[pr4t3ek]

I have the following set of Data for example.

Please Login or Register  to view this content.

I want to know what all combinations of the above texts there are.

i.e.

Please Login or Register  to view this content.

etc.. for all the combinations.

If i already have

Please Login or Register  to view this content.

I must NOT ALSO have..

Please Login or Register  to view this content.

I'd like a VBA script which I can use to make this happen. The list of values which to retrieve the original raw set of data will sit in cells A1 to A10.

I hope this makes sense?
Cheers

[xLJer]

Try out the attached Excel 2003 file that combines from 2 to 6 cell values in a delimited list that shows
all the unique combinations.

To accomplish this, the code goes through these steps:
- capture the cell values starting in Cell A1, from column 1, into a dictionary object
- put the values into an array
- start the loop and nested loop
- put the array values in one string, such as “Yes, You, Can, Do, It”
- check the dictionary and add this string and item count if string not already in the dictionary
(item count will be 5 in this case)
- perform a reverse analysis where the string is “It, Do, Can, You, Yes”
- add this string and item count to dictionary if it is not already there
- run through inner loop again and do same analysis, this time dropping 1st array element and continue
looping until analysis of 2 items has been completed
- Now outside of the inner loop, sort the array randomly and continue from the outer loop again,
performing the same analysis on a different text string.
- Discontinue looping when the expected number of unique combinations made has been reached.
- Capture dictionary data, including text and item counts, to arrays and write to Sheet1
- Sort the list and add group summary counts to the sheet. All done!


The secret to the success of this challenge is the Dictionary object, which worked perfectly for storing
unique lists of data.

Some controls on the worksheet give you the options of choosing the delimiter, or no delimiter,
space after delimiter or no space.

After completing several runs I observed (the obvious) that you always get the same number of rows returned
for the same number of items to be combined. That’s when I revised the looping code to stop the random analysis
when the dictionary record count reached the expected results. I hope the attached runs flawlessly for you.
Jerry

[pr4t3ek]

Hi!

Firstly, I just want to say thank you very much for your effort! I can see the depth you have gone to explain how your logic would work. So thanks again.

The problem I have with your code is for example in cell B2, you have 'Can, You' and in B5, you have 'You, Can'. To me; this 'combination' should only be displayed once. Preferably with 'You, Can' as I will play 'You' in A2 before A3.

I hope this makes sense and I hope you are able to advise further

Cheers

[xLJer]

See the Excel 2003 revised attachment that generates a list of text combinations,
and without duplicating any set of combined items. You can enter up to 10 items,
in cells A1 thru A10. I believe this will do what you described in your post.

The use of several dictionary objects allowed the macro to enforce uniqueness of the results.
The dictionary object can only store unique values.

[pr4t3ek]

Hi,

Is there a way to modify this so i can enter more than 10 items?

[xLJer]

Entering more than 10 items to find unique text combinations, and without duplication
in any combination set, would require a different solution than what I coded.

[JBeaucaire]

Translation... mark this thread as solved (See Thread Tools above) and start a new thread.

[shg]

There's a workbook that will do this for an arbitrary number of items (until you run out of rows) at https://www.box.com/s/b9b9fc06beb63b9562f9

[Winon]

@ JBeaucaire,

Hello Jerry,

Translation...

You are way too sharp! LOL.

[xLJer]

My plan was to give only what was asked for, text combinations up to 10 items.
Using the 10 digits (0-9) to represent whatever unique values were in column A, rows #1 thru #10,
made this a 10-items-or-less solution