List all the combinations of a group of cells containing letters, but not numbers

**tanaka1986** · 02-09-2008, 08:17 AM

Hi

I have 7 cells containing strings but not numbers on a row.
Now I want to list out all the combinations of drawing out 3 cells out of these 7 cells while the remaining cells that haven't been drawn out could also be listed out one column next to the drawn cells.

For example, I got 7 cells like this.

A B C D E F G (each letter in ONE cell)

And I want to list out all the combinations like this:

ABC DEFG
ABD CEFG
ABE CDFG
ABF CDEG

etc.

For more details, please refer to the attached sample (an .xls file being zipped).

Is there any macro that can make it?
Thank you so much!!!!

All the best
Mike

**MickG** · 02-09-2008, 11:25 AM

Mr Excel - Tanaka1968 _ String Combinations
Hi Tanaka1986
Place Code in Mt Macro Or Command Button
This code will do what you want, if you place you strings of Values in One Cell.
E.g. :- Place the letter "ABC" in Range "A1" run the code an Input box will appear.
Select Cell "A1", Click "OK".
All possible combination will be displayed in a Suitable size range, offset (1) column.
i.e.:- If Value in "A1", Results Start from "B1".
I Hope this meets your needs

Please Login or Register  to view this content.

Regards Mick

**tanaka1986** · 02-09-2008, 11:45 AM

Thank Mick for your prompt reply!!

But I am sorry that actually I am a newbie in using Macro of Excel.

I don't even know how to place the code into so called "mt macro" or "command button" but I tried to add a new module in the VB editor inside Excel by copying your codes there. After that, There was nothing I could find from the list of Macro that I could use however.

Could you please tell me what to do in more details about how to use your codes? (I am using Excel 2003)

I know how to use your code now...Thank!!

But, I still got some problems. Please refer to the following points.

Perhaps it was my fault misleading your understanding towards my question because I wanted to simplify the case by using ABCDEFG to illustrate my example. In fact, I have got 7 cells, each of which contains like an English words like "AD", "SC", "INTEGRAT"...etc.

For example I got 7 cells like this:

AD SC INTEGRAT BOOK APPLE CAT BEE (7 cells here)

And I want to find out all the combinations that if I picked 3 words out of these 7 words each time and present such three words in the first three cells and those remaining words in the latter cells while there was an empty cell in between separating two groups of words (picked vs. unpicked).

Referring to my sample .xsl you could see what I wanted to do while each of those letters represents one English word respectively.

Once again, I really appreciate your help and please forgive me if there is still anything unclear. I would be very happy to explain it if you need any further clarification.

Best regards
Mike

PS: I tried your codes with A1 containing ABCDEFG and it worked but the results coming out seemed to be not so accurate that there were 40 sets of combinations (out of all the results being generated) being just "FFFFFFF".
Anyway, Thank you so much for the help!!

**MickG** · 02-09-2008, 12:31 PM

HiTanaka1986
Give this read.
Don't be put of by its size

Inserting a Command Button in worksheet and Allocating Code

Open your workbook at the Sheet Number with your Data that you want to Manipulate.
Click View, Tools, Control Box,---- Control Box Menu Appears on sheet.
Slide you cursor over the Tool Box until you find a Command Button.
Click It then click Somewhere on the sheet.-- Command Button appears on sheet.
The Command Button will have handles round its edge, This is so you can Position it on the sheet.
Click the command Button "Hold the Mouse Click Down" and Drag the Command Button to where you want it.
Double Click the Command Button, The VB Editor window will appear.
You can also open the Editor By clicking Alt + F11, but if you double click the Command Bar the Editor will open in the procedure relating specifically to your Command Bar.
If the VB Editor window has two panes the right pane is where you must paste your code.
The left pane can be "Project Window" or "Properties Window, Click "Ctrl+R" if not showing.
Paste your code just under the words "Private Sub CommandButton1_Click() " in the Right hand pane.
If you have done this correctly. Scroll to the bottom of the code and you should see the words "End Sub"
To ensure you code is Running, Paste the bit of code below into your Code ,between the lines "Next cut" And "End Sub"
On the VB Toolbar you will see a Green Triangular shapes icon,.
This is to change the VB Editor mode from "Run Mode" to "Design Mode "
Click this Icon, The Small blue square to its left will change from light blue to dark blue, or Vice Versa. Before you close the Editor make sure this Square is "Dark Blue" i.e. (Reset)
Close the Editor. Select the Command Button and Click it.
When the code Runs the Msgbox should appear With The Message " "Transfer Complete" ,if this doesn't happen Open Open the VB Editor "Alt + F11" and click the "Reset ( Blue Square) and or The "Green Triangle" on the Tool bar . The Blue Square should be "Dark Blue ", in order to run the code..
NB:- If you want to get back into this specific code through the Command Button.
Get the Control Box menu back on the screen, Click the green triangle, When you slide the cursor over your Command button , The cursor shape will change to a "Arrow Headed Cross" , you will then be able to double click it to view your code.
Always "Reset" (Blue Square & Green Triangle) the Code before closing the VB Editor
If you prefer, you can forgo all this hassle by putting the code in an MT macro, with a key combination (Like Ctrl+"A") to run it.
Don't be daunted by all this, when you get the hang of it, it will seem quite simple

Please Login or Register  to view this content.

Any problems call back.
Regards Mick

**tanaka1986** · 02-09-2008, 12:55 PM

Originally Posted by MickG

HiTanaka1986
Give this read.
Don't be put of by its size

Inserting a Command Button in worksheet and Allocating Code

Open your workbook at the Sheet Number with your Data that you want to Manipulate.
Click View, Tools, Control Box,---- Control Box Menu Appears on sheet.
Slide you cursor over the Tool Box until you find a Command Button.
Click It then click Somewhere on the sheet.-- Command Button appears on sheet.
The Command Button will have handles round its edge, This is so you can Position it on the sheet.
Click the command Button "Hold the Mouse Click Down" and Drag the Command Button to where you want it.
Double Click the Command Button, The VB Editor window will appear.
You can also open the Editor By clicking Alt + F11, but if you double click the Command Bar the Editor will open in the procedure relating specifically to your Command Bar.
If the VB Editor window has two panes the right pane is where you must paste your code.
The left pane can be "Project Window" or "Properties Window, Click "Ctrl+R" if not showing.
Paste your code just under the words "Private Sub CommandButton1_Click() " in the Right hand pane.
If you have done this correctly. Scroll to the bottom of the code and you should see the words "End Sub"
To ensure you code is Running, Paste the bit of code below into your Code ,between the lines "Next cut" And "End Sub"
On the VB Toolbar you will see a Green Triangular shapes icon,.
This is to change the VB Editor mode from "Run Mode" to "Design Mode "
Click this Icon, The Small blue square to its left will change from light blue to dark blue, or Vice Versa. Before you close the Editor make sure this Square is "Dark Blue" i.e. (Reset)
Close the Editor. Select the Command Button and Click it.
When the code Runs the Msgbox should appear With The Message " "Transfer Complete" ,if this doesn't happen Open Open the VB Editor "Alt + F11" and click the "Reset ( Blue Square) and or The "Green Triangle" on the Tool bar . The Blue Square should be "Dark Blue ", in order to run the code..
NB:- If you want to get back into this specific code through the Command Button.
Get the Control Box menu back on the screen, Click the green triangle, When you slide the cursor over your Command button , The cursor shape will change to a "Arrow Headed Cross" , you will then be able to double click it to view your code.
Always "Reset" (Blue Square & Green Triangle) the Code before closing the VB Editor
If you prefer, you can forgo all this hassle by putting the code in an MT macro, with a key combination (Like Ctrl+"A") to run it.
Don't be daunted by all this, when you get the hang of it, it will seem quite simple

Please Login or Register  to view this content.

Any problems call back.
Regards Mick

Thanks for your teaching! Now I know how to use Macro codes.
But can you help me with my problem that I just clarified?

It would be really appreciated!!
Thank you in advance for the help!

Best regards
Mike

**MickG** · 02-09-2008, 01:09 PM

Mr Excel - Tanaka1968 _ String Combinations/2
Hi Tanaka1986
You are quite right. I wrote the original code for something else, and it didn't cater for 7 numbers.
In the code you will see , on the first Lines "Ray(1 To 6000)" change the "6000" for "60000".
Half way down the code you will see another Line:- "Dim cut As Variant, pst, FinRay(1 To 5000)
Change the "5000" for "60000".
From you reply, am I correct in thinking that you want all the combinations of the 3 works that you select, placed in three columns, then separated by a single Column you want, all the combinations of the remaining four words, placed in the next four columns. I'll work on it from that assumption, but if you could confirm this by showing me a couple of lines from the final two lists, out of the 7 words you quoted
"AD SC INTEGRAT BOOK APPLE CAT BEE (7 cells here)"
it would help.
Regards Mick

**tanaka1986** · 02-09-2008, 02:32 PM

Hi Mick

Thank for your reply.

Yeah...you almost got what I wanna do.

"am I correct in thinking that you want all the combinations of the 3 works that you select (Yes. I just want all the possible combinations of 3 words out of those 7 words being shown in the first 3 columns), placed in three columns, then separated by a single Column you want (Yes! While the first 3 cells are determined by the macro and shown in the first 3 columns the remaining 4 words would be shown in the latter cells on the same row while there is an empty cell in-between like my sample's),all the combinations of the remaining four words (I don't care if there are really all the combinations of them as long as all the three-word combinations have been shown and the 4 corresponding remaining words follow correctly., placed in the next four columns. "

Finally, I think the generated results would be of 35 rows. That is the answer of combin(7,3). It is simply to draw 3 out of 7 and list all the combinations of such "3" while the remaining "4" could follow behind an empty column.

Example:

7 cells from A1 to G1
AD SC INTEGRAT BOOK APPLE CAT BEE

Desired Results:
AD SC INTEGRAT (empty cell) BOOK APPLE CAT BEE
AD SC BOOK (empty cell) INTEGRAT APPLE CAT BEE
AD SC APPLE (empty cell) INTEGRAT BOOK CAT BEE
AD SC CAT (empty cell) INTEGRAT BOOK APPLE BEE
......
AD INTEGRAT BOOK (empty cell) SC APPLE CAT BEE
AD INTEGRAT APPLE (empty cell) SC BOOK CAT BEE
......
etc.

The four latter words are just the total words minus the first three words being picked and it doesn't matter how their orders are.

I hope this can help you understand my problem and thank you again for your help.

Best regards
Mike

**MickG** · 02-09-2008, 02:53 PM

Mr Excel - Tanaka1968 _ String Combinations/3
Hi Tanaka1968,
I have assumed my assumptions on my previous post were correct.
I think this code is what you want.
Place the code in a Command Button
Place your data text in separate cells along the top row of the worksheet.
Data :- AD-SC-INTEGRAT-BOOK-APPLE-CAT-BEE
Leave a column Gap between rows 4 & 5 say.
(You can modify your use of the code, when you've got it working)
Click the command Button -- Input box appears.
Highlight (select) the range for Range "A1 To A4"
Click "OK"
All combinations of the highlighted range are displayed below Row (1)
Do the same for Range "A6 to A8"
Combinations displayed.
The results will always be in the rows underneath the Rows selected

Please Login or Register  to view this content.

Regards Mick

**tanaka1986** · 02-09-2008, 03:19 PM

Originally Posted by MickG

Mr Excel - Tanaka1968 _ String Combinations/3
Hi Tanaka1968,
I have assumed my assumptions on my previous post were correct.
I think this code is what you want.
Place the code in a Command Button
Place your data text in separate cells along the top row of the worksheet.
Data :- AD-SC-INTEGRAT-BOOK-APPLE-CAT-BEE
Leave a column Gap between rows 4 & 5 say.
(You can modify your use of the code, when you've got it working)
Click the command Button -- Input box appears.
Highlight (select) the range for Range "A1 To A4"
Click "OK"
All combinations of the highlighted range are displayed below Row (1)
Do the same for Range "A6 to A8"
Combinations displayed.
The results will always be in the rows underneath the Rows selected

Please Login or Register  to view this content.

Regards Mick

Hi Mick,
Thank for your macro, though it's not the one I want.

But it would be definitely of great help for me later I believe!

If you have seen my latest reply, you would know what I want more clearly, I hope.
I am sorry that perhaps I didn't describe my problem clearly enough!!

I just want to show all the results of combin(7,3) while the 7 refers to seven words in seven cells respectively, while for each set of 3-word combination results (using 3 cells on a row) I want the 4 remaining words follow after the gap of an empty column.

Probably there would be 35 rows for the results.

Hope this time you would understand.

Thank you!!

Best regards
Michael

**MickG** · 02-09-2008, 03:56 PM

Hi Mike,
I'm sorry, there still seems to be some confusion,
You say Quote:- Yes. I just want all the possible combinations of 3 words out of those 7 words being shown in the first 3 columns), placed in three columns, then separated by a single Column you want (Yes! While the first 3 cells are determined by the macro and shown in the first 3 columns the remaining 4 words would be shown in the latter cells on the same row while there is an empty cell in-between like my sample's),all the combinations of the remaining four words (I don't care if there are really all the combinations of them as long as all the three-word combinations have been shown and the 4 corresponding remaining words follow correctly., placed in the next four columns. End Quote.
But, from your Example you seem to show a list in the first 3 Columns, Not of all the combinations of the First 3 in these (3 Columns.), but the first 3, of a possible combination of the 7 total words.
If it where a combination of the first 3 in these 3 Columns, each row would have the same words in a different order.
Please explain
If thats what you want, you could select the entire seven words
The combination would appear underneath, then you could insert a blank column at column (5) or where ever you chose.

Regards Mick

**tanaka1986** · 02-09-2008, 04:28 PM

Thanks for your reply, Mick!

"But, from your Example you seem to show a list in the first 3 Columns, Not of all the combinations of the First 3 in these (3 Columns.), but the first 3, of a possible combination of the 7 total words." <-- Not the first 3, of a possible combination of the 7 total words, BUT all the three-word combinations that could be generated from these 7 words [ Combin(7, 3) ]

I want all the possible combinations of combin(7,3). (I should have said in this way, I am sorry!)

But I want one thing more.
That is apart from the combinations of three words being selected out (Probably I should get 35 combinations), the remaining four words that don't show up in the first three columns could be following after an empty column and I don't care their orders.

That means on the same row, there would not be any duplicated word.

Again,
Example:

7 cells from A1 to G1
AD SC INTEGRAT BOOK APPLE CAT BEE

Desired Results: (Probably 35 rows would be generated because there are 35 combinations of 3 words out of 7 words)

AD SC INTEGRAT (empty cell) BOOK APPLE CAT BEE
AD SC BOOK (empty cell) INTEGRAT APPLE CAT BEE
AD SC APPLE (empty cell) INTEGRAT BOOK CAT BEE
AD SC CAT (empty cell) INTEGRAT BOOK APPLE BEE
......
AD INTEGRAT BOOK (empty cell) SC APPLE CAT BEE
AD INTEGRAT APPLE (empty cell) SC BOOK CAT BEE
......
etc.

Thanks!!

Best regards
Mike

**tanaka1986** · 02-09-2008, 10:58 PM

I am sorry that I just read your sentences wrong so I said "Exactly!!"

It should be this:

What I want is Not the first 3, of a possible combination of the 7 total words, BUT all the three-word combinations that could be generated from these 7 words [ Combin(7, 3) ].

And the one thing more that you could see from my previous reply.

Thank you!!
Best regards
Mike

**mikerickson** · 02-10-2008, 05:34 AM

This User Defined Array Function might help.

If your seven words are in A1:G1 and you want them displayed in A3:H37 (35 rows X 8 columns):

1) Select A3:H37
2) Type =Choose3(A1:G1)
3) Press Ctrl+Shift+Return (Cmd+Return for Mac) to enter the array formula.

This will work with any wordsRange of more than 3 cells.
(Note:Rows in resultsRange = COMBIN(wordsRange.Cells.Count , 3) ; Columns in resultsRange = wordsRange.Cells.Count + 1 )

Put this in a normal code module.

Please Login or Register  to view this content.

**tanaka1986** · 02-10-2008, 07:38 AM

Hi mikerickson

Thanks for your reply and help!

I copied your codes into the module and I typed the "=Choose3(A1:G1)" into the cell A3 as you told while highlighting the 35 x 8 range and Yes the cell A3 got the words in cell A1.

However, when I pressed Ctrl + Shift + Enter, nothing happened.

Can you please help me with this?
Thank you!!

Regards
Mike

**mikerickson** · 02-10-2008, 02:46 PM

Array formulas need to be entered by pressing Ctr+Shift+Enter

Select the large range

Type "=Choose3(A1:G1)" (no quotes, no Return)

Press Ctr+Shift+Return simultaniously.

You should see {=Choose3(A1:G1)} in the formula bar.

(The { } shows that it is an array formula.)

Another way to do it would be to put this (non-array) in A3
=INDEX(choose3($A$1:$G$1),ROWS($A$3:A3),COLUMNS($A$3:A3))
and drag it right and down.

**tanaka1986** · 02-11-2008, 01:45 AM

Originally Posted by mikerickson

Array formulas need to be entered by pressing Ctr+Shift+Enter

Select the large range

Type "=Choose3(A1:G1)" (no quotes, no Return)

Press Ctr+Shift+Return simultaniously.

You should see {=Choose3(A1:G1)} in the formula bar.

(The { } shows that it is an array formula.)

Another way to do it would be to put this (non-array) in A3
=INDEX(choose3($A$1:$G$1),ROWS($A$3:A3),COLUMNS($A$3:A3))
and drag it right and down.

Yea...I have got it working now.
Really cool..... It could exactly do what I want....

Mikerickson, Thanks for your great help!!

Btw, as now your array formula works for combin(x, 3), is it possible to make it work for like combin(x,y), which means I could even define y?

If that could be done, it would be really appreciated because I may have to always find combinations like that while y would even change as well sometimes.

Anyway, thanks for your help!!

Best regards
Michael

PS: Thank Mick for your help as well....Really appreciate it!

**mikerickson** · 02-11-2008, 02:33 AM

This is the same function with an added argument.

=ChooseN(A1:G1,3) is the same as =Choose3(A1:G1)

The size of the full array depends on both the choiceSize and the size of wordsRange.

You might try putting an Array =ChooseN(A1:G1, K1) and then change K1.

Please Login or Register  to view this content.

Later,
I added two optional arguments, rowIndex and columnIndex.

-If these arguments are not present, =ChooseN(wordsRange,choiceSize) returns an array as described. And has to be entered as an array formula with a multi-key-press entry.

-If both rowIndex and columnIndex are present, they return a single word from the middle of the array.
=ChooseN(wordsRange,choiceSize,2,3) is not an array formula and only needs normal input.
It returns the Row 2, Column 3 entry of the array.

-If either rowIndex or columnIndex are 0 or omitted, ChooseN returns either a row or column array similar to the way INDEX acts with a 0 row_number or column_number.

**tanaka1986** · 02-11-2008, 02:20 PM

Mikerickson, Thank you so much!!

They work so perfectly!!
It would save me lots of time!

You could totally understand what I wanted and give me the exact solution so quickly.
Really appreciate your help!!

Thanks!!

Best regards
Mike

**MickG** · 02-12-2008, 06:38 AM

Hi Mike
I hadn't forgotten you, its just taken a bit of time to write what you wanted.
I think this should do.
When you run this code, a "Input box" appears, Select the range/Row of cells with you string of words. (As per the example select all 7 cells)
Click "OK" another input box appears , Type in the inputbox, the Number (Say 3) of words out of your list that you want to find Sub combinations of, .click "OK"
New List appears in left hand columns, followed by an MT column, and then the remaining words out of the list.in the right hand columns
This new list will be directly under the Data List.
I think you now what I mean.
You can try running this code this for quite a large list, and virtually any number for the Sub group, as long as its less than the List length.
The proviso is that all the words are different, obviously.

Good Luck

Please Login or Register  to view this content.

Regards Mick

List all the combinations of a group of cells containing letters, but not numbers

LinkBack

Thread Tools

Rate This Thread

Display

List all the combinations of a group of cells containing letters, but not numbers

String Combinations

Thanks, Mick

Pasting Code

Thank you so much!!

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions