COMBINATIONS with repetitions???

[garrettsmale]

Hey ,
I’ve been trying to complete a project, but I’ve been stuck on this dilemma that’s been holding me back for quite some time, and I’m running out of ideas

I’m trying to create lists of every combination of certain letters and also numbers for string sets of 6 through..(idk maybe 10 is long enough). I basically need a combinations generator with repetition formula.

For example:
If I were to use letters “a, b, c, d, e, f, g” and I wanted all the combinations of these letters in string sets of 3, I’d get this:

aaa, aab, aac, aad, aae, aaf, aag, aba, abb, abc, abd, abe, abf, abg, aca, acb, acc, acd, ace, acf, acg, ada, adb, adc, add, ade, adf, adg, aea, aeb, aec, aed, aee, aef, aeg, afa, afb, afc, afd, afe, aff, afg, aga, agb, agc, agd, age, agf, agg, baa, bab, bac, bad, bae, baf, bag, bba, bbb, bbc, bbd, bbe, bbf, bbg, bca, bcb, bcc, bcd, bce, bcf, bcg, bda, bdb, bdc, bdd, bde, bdf, bdg, bea, beb, bec, bed, bee, bef, beg, bfa, bfb, bfc, bfd, bfe, bff, bfg, bga, bgb, bgc, bgd, bge, bgf, bgg, caa, cab, cac, cad, cae, caf, cag, cba, cbb, cbc, cbd, cbe, cbf, cbg, cca, ccb, ccc, ccd, cce, ccf, ccg, cda, cdb, cdc, cdd, cde, cdf, cdg, cea, ceb, cec, ced, cee, cef, ceg, cfa, cfb, cfc, cfd, cfe, cff, cfg, cga, cgb, cgc, cgd, cge, cgf, cgg, daa, dab, dac, dad, dae, daf, dag, dba, dbb, dbc, dbd, dbe, dbf, dbg, dca, dcb, dcc, dcd, dce, dcf, dcg, dda, ddb, ddc, ddd, dde, ddf, ddg, dea, deb, dec, ded, dee, def, deg, dfa, dfb, dfc, dfd, dfe, dff, dfg, dga, dgb, dgc, dgd, dge, dgf, dgg, eaa, eab, eac, ead, eae, eaf, eag, eba, ebb, ebc, ebd, ebe, ebf, ebg, eca, ecb, ecc, ecd, ece, ecf, ecg, eda, edb, edc, edd, ede, edf, edg, eea, eeb, eec, eed, eee, eef, eeg, efa, efb, efc, efd, efe, eff, efg, ega, egb, egc, egd, ege, egf, egg, faa, fab, fac, fad, fae, faf, fag, fba, fbb, fbc, fbd, fbe, fbf, fbg, fca, fcb, fcc, fcd, fce, fcf, fcg, fda, fdb, fdc, fdd, fde, fdf, fdg, fea, feb, fec, fed, fee, fef, feg, ffa, ffb, ffc, ffd, ffe, fff, ffg, fga, fgb, fgc, fgd, fge, fgf, fgg, gaa, gab, gac, gad, gae, gaf, gag, gba, gbb, gbc, gbd, gbe, gbf, gbg, gca, gcb, gcc, gcd, gce, gcf, gcg, gda, gdb, gdc, gdd, gde, gdf, gdg, gea, geb, gec, ged, gee, gef, geg, gfa, gfb, gfc, gfd, gfe, gff, gfg, gga, ggb, ggc, ggd, gge, ggf, ggg

(ideally, it’d be a vertical list, but just to save space)

and numbers would be like this:

(shorter sample)
111, 112, 113, 114, 115, 116, 117, 121, 122, 123, 124, 125, 126, 127, 131, 132, 133, 134, 135, 136, 137, 141, 142, 143, 144, 145, 146, 147, 151, 152, 153, 154, 155, 156, 157, 161, 162, 163, 164, 165, 166, 167, 171, 172, 173, 174, 175, 176, 177, 211, 212, 213, 214, 215, 216, 217, 221, 222, 223, 224, 225, 226, 227, 231, 232, 233, 234, 235, 236, 237, 241, 242, 243, 244, 245, 246, 247, 251, 252, 253, 254, 255, 256, 257, 261, 262, 263, 264, 265, 266, 267, 271, 272, 273, 274, 275, 276,…. Etc.

but that makes sense right?

Now, I’d easily be able to achieve this through using this AWESOME website (http://textmechanic.com/Combination-Generator.html) , but for some reason the window keeps freezing up (even when I do the direct save thing) everytime I want to generate string sets of 6 or greater. (maybe my comps RAM is too low?)


Now that I have described my general required computation, I’d like to spill my exact desired result, because even the site above gave me more information than I needed.

I really only need the first letter(or number) starting set of combinations, and the rest is actually arbitrary to me because I only want the combinations that have that certain Letter/or number in it. (please stay with me.. heres an example):

So.. in the combinations list I gave up there, I’d really only want the first round of combinations (which happens to be starting with “a”)
VVV
aaa, aab, aac, aad, aae, aaf, aag, aba, abb, abc, abd, abe, abf, abg, aca, acb, acc, acd, ace, acf, acg, ada, adb, adc, add, ade, adf, adg, aea, aeb, aec, aed, aee, aef, aeg, afa, afb, afc, afd, afe, aff, afg, aga, agb, agc, agd, age, agf, agg,



alright,, so theres that.

Now to make things even more complicated, do you notice how there are actually repeated strings? like strings that have the same exact letters in them, but are just in a different order? For example.. the “abc” string on the top middle.. well it has its brother or sister “acb”.. i don’t want that...

Actually i’m making this too complicated, and I can just delete those on my own time without the complex extras in a formula.. idk



But just to give you a really good idea, here are some things that I found that were really close to helping me, but just couldn’t for some reason or another..

Was so close!
https://sites.google.com/site/e90e50fx/home/combinatorics-using-excel-formulas-and-examples
almost there..
http://www.mrexcel.com/forum/excel-questions/436932-all-possible-combinations-repeats-allowed.html
OMG if only I didn’t have a mac/was running excel 2011
Combinations Generator Excel Download - ExcelFreelance.com
more teasing..
Excel - Multiple Combination Generator - Hello People... - Free Excel Help
…
http://www.mrexcel.com/forum/excel-questions/422699-help-combination-formula-excel07.html
I just suck at excel and im completely lost with formulas and macros..
Excel - Multiple Combination Generator - Hello People... - Free Excel Help


so.. I hope I have made sense..

but either way, if you’re reading this, I thank you so very much for even taking the time.. it’s really awesome that these types of sites can even exist were people take the time out of their own day to help someone out and teach them new things out of pure kindness and the desire to help and serve

that being said, I would totally be glad to compensate somehow for anyone that can solve my problem

THANK YOU

 

[shg]

There's a workbook at https://app.box.com/s/47b28f19d794b25511be that will do that.

 

[Jim Gordon]

If I understand your goal correctly, I think you could paste the data into a workbook, save it as .xls. Then use Microsoft Query to join the table to itself and use a DISTINCT operator. For Example:

SELECT DISTINCT Sheet1.Three FROM Sheet1, Sheet1 AS Sheet1_1 WHERE (Sheet1.Three=Sheet1_1.Three)