How to do Combination in Numbers? Not the answer but to show all the combinations.

Hi Kenny,

Easy to do in a table:

Formula in B2 (and Fill Right and Fill Down) =$A2&B$1

I am guessing that you don't want the combinations in the cells that I (manually) filled with red (AA, BA etc).

Regards,

Ian.

I would do something like this.

First, create a set up table and name it "Setup":

In cell B3 enter the formula:

=2^B1

shorthand for this is:

B3=2^B1

this produces a value used to limit the combinations in another table.

The "other" table is named "Combinations" and is set up like this:

Make the first row a header row.

I named the items "A" thru "L"

A2=IF((ROW()−1)<Setup::$B$3, ROW()−1, "")

B2=COUNTIF(E2:P2, "<>"&0)

C2=IF(B2=Setup::$B$2,D2, "")

D3=IFERROR(SUBSTITUTE(E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2, "0", ""), "")

E2=IFERROR(IF(MID(NUMTOBASE($A2,2,12), 13−(COLUMN()−4), 1)="1", E$1, 0), "")

to finish filling across, select E2, copy,

select cells E2 thru P2, paste

to fill down, select cells A2 thru P2, copy

select cells A2 thru the end of column P, paste

now expand the table, by selecting rows 2 thru then of the table, then use the key combination <option>+ <down arrow> to add more rows.

select more rows off the rows except the header then repeat the key combination. I added 512 rows.

Number is not very good with large datasets, so as you add rows it may slow down.

The combinations are in column C.

you can select that column,, copy, then paste into another table using the menu item "Edit > Paste Formula Results" then sort ASCENDING. Then select the "empty" rows and delete, then sort ascending

Usually, it's more efficient to use a script, expecially when r gets larger than 3, but here's another way to do list combinations via formula.

In D1, filled down until you get blanks:

=IF(ROW(A1)>COUNTA(A)×COUNTA(B),"",INDEX(A,IF(MOD(ROW(A1),COUNTA(B))=0,ROW(A1)÷C OUNTA(B),INT(ROW(A1)÷COUNTA(B))+1))&", "&INDEX(B,IF(MOD(ROW(A1),COUNTA(B))=0,COUNTA(B),MOD(ROW(A1),COUNTA(B)))))

If you have two different lists of varying lengths, the results look like this:

And you can extend this to handle three lists, like this:

In E1, filled down:

=IF(ROW()≤COUNTA(A)×COUNTA(B)×COUNTA(C),INDEX(A,MOD(ROW()−1,COUNTA(A))+1)&", "&INDEX(B,MOD(INT((ROW()−1)÷COUNTA(A)),COUNTA(B))+1)&", "&INDEX(C,INT((ROW()−1)÷(COUNTA(A)×COUNTA(B)))+1),"")

For the logic behind the formulas see this thread (Excel, but applies to Numbers too).

SG

Here is an AppleScript solution that automatically gets the cell contents of column A, generates a combination list based on those column A values. It then adds a column, and populates that column with the combination values.

Tested with Numbers v5.1 on macOS High Sierra 10.13.6 (17G65).

We start with a table that looks like this:

and run this AppleScript:

use framework "Foundation"

use AppleScriptversion "2.4" -- Yosemite or later

use scripting additions

set cList to {}

set letters to ""

set n to 2

tell application "Numbers"

activate

tell front document

tell table 1 of first sheet

set cList to its column "A"'s cells's value where value is not missing value

end tell

end tell

end tell

-- Ruby cannot handle AppleScript lists directly, so convert to delimited string

-- and Ruby turns it into a specific delimited array that the combination method can use.

set letters to my aslist2str(cList, ",")

-- this line is wrapped

set combList to paragraphs of (do shell script "ruby -e 'puts ARGV.first.split(\",\").repeated_combination(ARGV.last.to_i).map(&:join)' " & letters & space & n)

tell application "Numbers"

activate

tell front document

tell table 1 of first sheet

add column aftercolumn "B"

tell column "C"

repeat with i from 1 to the count of combList

set the value of cell i to (item i of combList)

end repeat

end tell

end tell

end tell

end tell

return

on aslist2str(thelist, delim)

-- return delimited text string from AppleScript list

set nsary to current application'sNSArray'sarrayWithArray:thelist

return (nsary'scomponentsJoinedByString:delim) as text

end aslist2str

And afterward:

And of course if you just want pairs then the AppleScript solution can be simpler, something like this:

tell application "Numbers"

tell front document's active sheet's table 1

set aList to column "A"'s cells's value where value is not missing value

set thePairs to my getPairs(aList)

set row count to 1 + (count thePairs's paragraphs)

set theRow to header row count + 1 -- first non-header row

repeat with p in thePairs's paragraphs

set column "B"'s cell theRow's value to p

set theRow to theRow + 1

end repeat

end tell

end tell

to getPairs(aLst)

set n to aLst's length

set outStr to ""

repeat with i from 1 to (n - 1)

repeat with j from (i + 1) to n

set outStr to outStr & aLst's item i & ", " & aLst's item j & return

end repeat

end repeat

return outStr

end getPairs

Giving this:

SG

To arrive at non-repeated combinations, use the Ruby method combination instead of repeated_combination.

I prefer your AppleScript example. 😉