list creation

[Darrell_Sarrasin via OfficeKB.com]

I have a list of 10 items. I want to take the 10 items and generate a list
of every possible combination.

Example, if we have three items it would show a list like:

Apple
banana
orange
apple banana
apple orange
etc

any help is greatly appreciated.

 

[smartin]

I have a list of 10 items. I want to take the 10 items and generate a list
of every possible combination.

Example, if we have three items it would show a list like:

Apple
banana
orange
apple banana
apple orange
etc

any help is greatly appreciated.

Here's a quick and dirty method for permutations taken two at a time.

Insert the following in A1:C1, name this range MyList

apple
banana
orange

Insert the following in A5:C13

A B C
------------------------------
1 1 apple apple
1 2 apple banana
1 3 apple orange
2 1 banana apple
2 2 banana banana
2 3 banana orange
3 1 orange apple
3 2 orange banana
3 3 orange orange

Formulae are:
(row 5)
A =1
B =1
C =INDEX(MyList,A5) & " " & INDEX(MyList,B5)

(remaining rows)
A =A5+(--B6=1)
B =MOD(B5,COUNTA(MyList))+1
C =INDEX(MyList,A6) & " " & INDEX(MyList,B6)


This is easily extended for as many items as you have.

Of course there are lots of refinements/considerations... how many items
do you want in combination (just 2? as many as possible?), or do you
want permutations? Probably you want to exclude duplicates, etc.

I think this might be easier (in a sense) to do this using Excel's
database query feature to create a cartesian product of the items... I
will experiment a little more.

 

[smartin]

I think this might be easier (in a sense) to do this using Excel's
database query feature to create a cartesian product of the items... I
will experiment a little more.

Indeed, it is much easier (in a sense) and you have more control over
the results using a database query in Excel. This might seem like a lot
of steps, especially if you are not familiar with the query editor, so
I've included lots of step-by-step for navigating. Here's we go:

First create a table like the following in Excel.

FRUITS COUNTER
apple 1
banana 2
orange 3
pomegranite 4
peach 5
cherry 6
elderberry 7
kumquat 8
tangerine 9
mango 10

Select the data and create a named range called "MyList". Save the workbook.

Create a New Database Query: (In Excel 2003) Data | Import External Data
| New Database Query. Databases: Excel Files, OK. Browse to the file you
just saved. "MyList" should appear in the Query Wizard. Click the
chevron > to insert this range. COUNTER and FRUITS should appear in the
"Columns" section. Next. Next. Next. Do not Finish, but pick "View data
or edit query in Microsoft Query", then Finish.

The Microsoft Query editor opens, showing you "MyList" as a table. Table
| Add Tables... , add MyList again, and confirm that you are adding this
table again. Close. Criteria | Add Criteria...

Field: MyList.COUNTER
Operator: is greater than
Value: MyList_1.COUNTER

Add. Close. Delete COUNTER from the query results, then drag FRUITS from
MyList_1 into the query results. File | Return Data to Microsoft Excel.
You are returned to Excel with the Import Data dialog asking where to
put the data. Select New worksheet. OK.

Done.

Now, the criteria I gave will return combinations (order of items does
not matter). If you want permutations (order of items matters) edit the
query: place the cursor in the results (in Excel), Data | Import
External Data | Edit Query. OK. "The query cannot be edited by the Query
Wizard" - OK. Edit the criteria value field to read <> MyList_1.COUNTER
then File | Return Data to Microsoft Excel again.