Generate powerset in Excel?

[Greg Lovern]

Is there a quick way to generate a powerset in Excel?

A powerset is the set of all possible combinations of the items in a
set. For example, given the set a, b, and c, the powerset is:

a
b
c
ab
ac
bc
abc

(Technically a powerset also includes the null set, but I'm not
concerned about that here.)

That's easy enough to do manually with such a small set as this
example, but obviously becomes much more tedious and error prone to do
manually as the set becomes larger.

I could make three column headers in a sheet and enter all three items
under all three columns, plus one additional item in each field
indicating blank while still being counted as a record (such as the
string "blank"), and then do a Cartesian Product on those three fields
in MS Query from Excel. But that would return separate records for
each possible *sequence* of the same items, such as both ab and ba.
For my purposes, ab and ba would effectively be duplicates.

FWIW, the reason for this is I have a dropdown where a seller
indicates all shipping options they offer, in a comma-separated list,
from a list of all approved shipping options. Some sellers might only
offer one of the approved shipping options, some two, some all, etc.
But the list of all approved options occasionally changes (outside my
control), requiring an update of the dropdown list. A list made from a
Cartesian Product would be a huge number of dropdown items (and
therefore annoying for sellers to use) comared to a powerset.

Any thoughts?


Thanks,

Greg

 

[JP]

I did a search for "vba powerset" and this was the first hit:

http://www.tushar-mehta.com/excel/tips/powerset.html

--JP

Is there a quick way to generate a powerset in Excel?

A powerset is the set of all possible combinations of the items in a
set. For example, given the set a, b, and c, the powerset is:

a
b
c
ab
ac
bc
abc

-- snip --

 

[Greg Lovern]

There's nothing like posting a question to a newsgroup for thinking of
other ideas for solving a problem. After posting this, it occurred to
me to search for a web powerset calcuator. In fact there is one:

http://www.mathsisfun.com/sets/power-set-maker.html


That will do, at least as long as it is up on the web. But I'd prefer
a way to do it in Excel if there is one. Any ideas?


Thanks,

Greg