I have the following list of item


The numbers at the side of some items indicate that they are alternatives. So, in the previous example B, C and D are alternatives, and F and G are alternatives. Due to the presence of these groups of alternative items the list corresponds to the following lists


I have to write a Mathematica script which takes an Excel table like the first as input and gives the second table as output. Of course the number of total items of the first table is random, the number of groups of alternative items is random and the number of item inside each group of alternative items is random. Could you suggest me how I can set up that script?


closed as unclear what you're asking by Daniel Lichtblau, bbgodfrey, Wjx, gwr, Young Sep 2 '16 at 18:14


  • $\begingroup$ Could you elaborate a bit? As I understand you mean that B C and D can be used interchangeably, but what exactly does it mean? Why is the width of the second column 6? It is unclear how the second matrix corresponds to the first. So the number indicates the frequency in which the letters alternate? $\endgroup$ – Vahagn Tumanyan Sep 1 '16 at 13:58
  • $\begingroup$ Hello thank you for your answer. For example the first table could be a list of item that i can ship to a customer: B,C,D are alternatives to each other and F,G are alternative to each other. Combining those alternative items the resulting lists of items that I can potentially ship is given by the columns of the second table. I have 6 columns corresponding to 6 item list as the result of the combination of 3 alternatives (B,C,D) and 2 alternatives (F,G)--> 3x2=6. I hope to being more clear. Thank you $\endgroup$ – Mazuego Sep 1 '16 at 14:24
  • $\begingroup$ Is the representation of the the alternative items in the shipping table following some kind of a rule? For example why are the B,C,D's put in pairs and are not presented in some other configuration. Same question applies to the F,G's. $\endgroup$ – user42582 Sep 1 '16 at 14:34
  • $\begingroup$ No, they don't follow any kind of rule. The alternative items are identified by a number at each side only. Furthermore, please note that the alternatives may not be subsequent items as the example. $\endgroup$ – Mazuego Sep 1 '16 at 14:40
  • $\begingroup$ @mazuego so as I understand the number of columns equals the the length of the list of alternatives with most elements times the maximum number on the left? Also do you mind changing up the order? like write F after A or something like that? $\endgroup$ – Vahagn Tumanyan Sep 1 '16 at 14:46

EDIT: I edited the answer so that now it conforms with the format of the imported .xls file.

You Import the data with

data = Import["data.xls"]

{{{"A", ""}, {"B", 1.}, {"C", 1.}, {"D", 1.}, {"E", ""}, {"F", 2.}, {"G", 2.}, {"H", ""}}}

That's a list with one element: a list of the tuples, so let's do

list = First@data


I will employ the function removeFrom from this answer:

removeFrom[b_List, a_List] := Module[{f}, f[_] = 0;

Find positions of non-alternatives:

complPos = Partition[Position[list, ""][[All, 1]], 1]

{{1}, {5}, {8}}

Positions of alternatives:

pos = removeFrom[Partition[Range[Length@list], 1], complPos]

{{2}, {3}, {4}, {6}, {7}}

Non-alternative elements:

ones = Flatten@Table[list[[i, 1]], {i, complPos}]

{"A", "E", "H"}

Alternatives with their indices:

twos = Flatten[Table[list[[i]], {i, pos}], 1]

{{"B", 1.}, {"C", 1.}, {"D", 1.}, {"F", 2.}, {"G", 2.}}

Strip them of the indices and group the corresponding alternatives:

grouped = GatherBy[twos, Last]
elems = Table[grouped[[i, All, 1]], {i, 1, Length@grouped}]

{{{"B", 1.}, {"C", 1.}, {"D", 1.}}, {{"F", 2.}, {"G", 2.}}}

{{"B", "C", "D"}, {"F", "G"}}

Make all possible combinations of the alternatives:

tuples = Tuples[elems]

{{"B", "F"}, {"B", "G"}, {"C", "F"}, {"C", "G"}, {"D", "F"}, {"D", "G"}}

Make all combinations including the non-alternatives:

result = Map[Sort, Table[Join[tuples[[i]], ones], {i, 1, Length@tuples}]]

{{"A", "B", "E", "F", "H"}, {"A", "B", "E", "G", "H"}, {"A", "C", "E", "F", "H"}, {"A", "C", "E", "G", "H"}, {"A", "D", "E", "F", "H"}, {"A", "D", "E", "G", "H"}}

Display it as a table where the rows are the possibilites:


or if you want the columns to be the answers:


If you want the result to be really in the form you posted, i.e. with empty places in the table, then do

allElems = Sort@Flatten@Join[ones, elems]


del = Table[Flatten@Table[
Position[allElems, removeFrom[allElems, result[[j]]][[i]]], {i, 1,
  Length@removeFrom[allElems, result[[j]]]}], {j, 1, Length@result}]

{{3, 4, 7}, {3, 4, 6}, {2, 4, 7}, {2, 4, 6}, {2, 3, 7}, {2, 3, 6}}

final = Table[ReplacePart[allElems, 
Table[del[[j, k]] -> {}, {k, 1, Length@del[[j]]}]], {j, 1, Length@result}]

{{"A", "B", {}, {}, "E", "F", {}, "H"}, {"A", "B", {}, {}, "E", {}, "G", "H"}, {"A", {}, "C", {}, "E", "F", {}, "H"}, {"A", {}, "C", {}, "E", {}, "G", "H"}, {"A", {}, {}, "D", "E", "F", {}, "H"}, {"A", {}, {}, "D", "E", {}, "G", "H"}}

And finally (for possibilities in rows)


or (for answers in columns)


NOTE: Keep in mind that C and D are protected symbols; if you use strings "C" and "D" like above then there is no problem, but the code may crash if you decide on some point to change strings into expressions. That's why I used in the first version of my answer lower-case letters a,b,c,.... Additionally, I have a concern regarding the indices in data/list: in MMA there's a substantial difference between 1 and 1.. If the programme that was used to produce the .xls file made an error and instead of 1. placed in some place, e.g., 1.00000000000000001, the code will not give the correct answer.

  • $\begingroup$ If run the command list = Import["data.xls"] I have {{{"A", ""}, {"B", 1.}, {"C", 1.}, {"D", 1.}, {"E", ""}, {"F", 2.}, {"G", 2.}, {"H", ""}}} and the script doesn't work. How can I put the data in the format you suggested? $\endgroup$ – Mazuego Sep 2 '16 at 9:07
  • $\begingroup$ I slightly changed the code so that it handles the data in the format it's imported. $\endgroup$ – corey979 Sep 2 '16 at 10:06
  • $\begingroup$ It works!!! Thank you! $\endgroup$ – Mazuego Sep 2 '16 at 13:19

enter image description here

Assume you have the previous input which corresponds to the table

groupped={{"A", "G"}, {"E", "F"}, {"B", "H"}, {"C", "D"}}

Note how the first list corresponds to items with no alternatives while the following lists contain the sets of alternative options.


If table={{"A", Null}, {"B", 2}, {"C", 3}, {"D", 3}, {"E", 1}, {"F", 1}, {"G", Null}, {"H", 2}}

is the input then

groupped = Part[SortBy[GatherBy[table /. Null -> 0, Last], #[[-1]][[-1]] &], All, All, 1]


The following code produces all the possible combinations of items:

{first, rest} = {First[#], Rest[#]} &@groupped; Flatten[Outer[Flatten[{first, {##}}] &, Sequence @@ rest], 2]

In this particular example, the output is

{{"A", "G", "E", "B", "C"}, {"A", "G", "E", "B", "D"}, {"A", "G", "E", "H", "C"}, {"A", "G", "E", "H", "D"}, {"A", "G", "F", "B", "C"}, {"A", "G", "F", "B", "D"}, {"A", "G", "F", "H", "C"}, {"A", "G", "F", "H", "D"}}

or in tabular form

enter image description here

