excel - To find all possible combinations of strings present in a column range (order does not matter ,repetition not allowed) -

i want possible combinations of values present in column range , print them in excel sheet:

please note order of combination not matter i.e ab=ba

here example of data in column1 combinations found:

f1 f2 f3 f4 

the possible combinations of these :

f1f2 f1f3 f1f4 f2f3 f2f4 f3f4 f1f2f3 f1f2f4 f1f3f4 f2f3f4 f1f2f3f4 

this first stack overflow answer:

this might not elegant approach, works. first eliminate repetitions in data. inclination use vbscript dictionary -- can in pure vba this. if have n distinct items -- count 0 2^n -1 in base 2, each of corresponds combination (subset). seem want throw out subsets of size less 2. wrote function this, sub test with. sub assumes data starts in a1 , contiguous. prints results in column b:

sub additem(c collection, x variant)     dim long     = 1 c.count         if c(i) = x exit sub     next     c.add (x) end sub  function base2(number long, width long) string     'assumes width long enough hold number     dim n long, long, r long, s string     dim bits variant     redim bits(1 width)     n = number     = width     while n > 0         r = n mod 2         n = int(n / 2)         if r > 0 bits(i) = 1         = - 1     loop     = 1 width         s = s & iif(bits(i) > 0, "1", "0")     next     base2 = s end function  'in follows items variant array of strings 'it returns variant array of strings consiting 'of combinations (of size > 1) of strings function combos(items variant) variant     dim long, j long, k long, m long, n long     dim b string, s string     dim onecount long     dim itemset new collection     dim retarray variant     = lbound(items) ubound(items)         additem itemset, items(i)     next     n = itemset.count     redim retarray(1 2 ^ n - n - 1)     = 0     j = 3 2 ^ n - 1         b = base2(j, n)         onecount = 0         s = ""         k = 1 n             if mid(b, k, 1) = "1"                 s = s & itemset(k)                 onecount = onecount + 1             end if         next k         if onecount > 1             = + 1             retarray(i) = s         end if     next j     combos = retarray end function  sub test()     dim r range, v variant, long, n long     set r = range("a1", range("a1").end(xldown))     n = r.cells.count     redim v(1 n)     = 1 n         v(i) = r.cells(i)     next     v = combos(v)     = 1 ubound(v)         range("b:b").cells(i).value = v(i)     next end sub