CODEWITHSUNDEEP
excelopenoffice-calc
Thanasis Valto Kourpetis
Thanasis Valto Kourpetis

Reputation: 117

How to create two columns that match all values from a third in excel or OpenOffice?

I have one column with 10 cells, every cell have a different value in it. How can I create two columns that have every cell matching with the other 9.

Example :

1
2
3
4
5
6
7
8
9
10

Become

1 2
1 3
1 4
1 5
......
2 1
2 3
2 4
2 5
.....
10 1
10 2
10 3
10 4
10 5
10 6
10 7
10 8
10 9

Upvotes: 0

Views: 105

Answers (3)

FocusWiz
FocusWiz

Reputation: 533

I am not sure I read the same question as others did. I think your example was merely that, an example, and that these first 10 cells could contain anything and you wanted every permutation that could result. While I think that the other answers might work for the specific situation you describe, they may not work if you had other data in those cells. Hence I am offering a variation which uses a similar technique to reference the cells indirectly. The permutations of 2 objects from a set of 10 unique objects would result in 90 objects (which is why the above technique from Tom Sharpe references 90).

Assuming that you have your 10 items in A1 through A10, I would put the following formula in B1 and copy it down through B90:

=INDIRECT("R"&QUOTIENT(ROW()-1,9)+1&"C1",FALSE)

Also, I would use this formula in C1 and copy it down through C90:

=INDIRECT("R"&MOD(ROW()-1,9)+1+((MOD(ROW()-1,9)+1)>=QUOTIENT(ROW()-1,9)+1)&"C1",FALSE)

The result should give you something like what is shown in the attached matching your example.Example

Likewise, it would show the permutations of any values you had in A1 through A10 as shown in the second attached picture with words instead of the numbers 1 through 10. enter image description here

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34230

Or in A1:

=QUOTIENT(ROW()-1,9)+1

copied down to A90 just to be different.

Then in B1:

=MOD(ROW()-1,9)+1+((MOD(ROW()-1,9)+1)>=A1)

copied down to B90.

Upvotes: 0

pnuts
pnuts

Reputation: 59475

In Excel (without VBA or such like), one way:

In A1 and copied down to A100: =INT((ROW()+9)/10).

In B1 and copied down with Ctr to B10: 1.

Select B1:B10 and copy down with Ctrl to B100.

In C1 and copied down to C100: =A1=B1.

Select ColumnsA:B, Copy, Paste Special, Values.

Filter A:C ,select TRUE in ColumnC and delete all blue indexed (visible content) rows.

Delete ColumnC.

Upvotes: 0

Related Questions