i have a file with 15350 rows with 3 unique numbers across 3 columns, i want a list of all the combinations of 2 pairs = 6 numbers. ie 15350 x 15350 = over 200million rows.

Are you printing the list? Or storing in a file? Or finding the occurrences of certain combinations?

i want it on file

i have the initial file with 15350 rows of data if you need that to solve the macro?

yes, excel workbook

Is this what you mean? In Cell A1 you have a number, example: 1

In Cell B1 you have a number, example: 2

In Cell C1 you have a number, example: 3You now need Six additional columns:

D - E - F - G - H - I

In D1 enter the formula: = A1 & B1 result 12

In E1 enter the formula: = A1 & C1 result 13In F1 enter the formula: = B1 & A1 result 21

In G1 enter the formula: = B1 & C1 result 23In H1 enter the formula: = C1 & A1 result 31

In I1 enter the formula: = C1 & B1 result 32MIKE

no, i have 3 numbers in row 1, across 3 columns, ABC.

i want all the combinations of 6 numbers which is 15350 x 15350.have you got an email address i can send you the excel document to, or is there a place i can upload the document?

i have 3 numbers in row 1, across 3 columns, ABC.A B C D E F G H I 1) 10 20 30 1020 1030 2010 2030 3010 3020 =A&B =A&C =B&A =B&C =C&A =C&B

i want a list of all the combinations of 2 pairsIf this is not what you mean, post an example.

MIKE

A B C

R1 1 2 3

R2 1 2 4

R3 1 2 5i want a macro that will save all the combinations on to file or somewhere on the document. all the combinations in this example = R1&R2, R1&R3, R2&R1, R2&R3, R3&R1, R3&R2 resulting in the following 6 numbers in a row: 123124, 123125, 124123, 124125, 125123,125124

6 numbers, each in their own row, with each number in a separate column

OK, I get it now. I can think of no easy way, using a formula, to get the complete matrix of numbers,

and unfortunately my macro skill are just above nil.Sorry.

MIKE

First you said:

resulting in the following 6 numbersin a row: 123124, 123125, 124123, 124125, 125123,125124Then you said:

re:

6 numbers, each in their own row, with each number in a separate columnSo, is it all six numbers "in a row" (a single row) or all six numbers in their own row? You can't have it both ways.

If they are to be both in their own row

andin a separate column, then are you asking for this:A B C D E F 1 123124 2 123125 3 124123 4 124125 5 125123 6 125124And so on...

In other words, very long and very wide?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Just an idea. I would eliminate the distraction of three columns.

Combine columns A & B & C into one column

Then it becomes a problem of

D1 & D2

D1 & D3

D1 & D4Which might be a bit more manageable.

MIKE

Oh sure, Mike...take care of the easy stuff! ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

i meant each number of the 6 in different columns but the same row,

the next 6 numbers in the next or a different row, like this:E F G H I J

1 2 3 1 2 4

1 2 3 1 2 5

Well, that changes everything!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I believe this should give you what your looking for.

The only problem, is you will not have enough columns in one worksheet.

So you will have to spread it over a couple of sheets.To start with, eliminate the distraction of three columns.

Combine columns A & B & C into one column D using the formula:

=A1&B1&C1

Drag down your 15350 rows.

Next, insert a row at the top of your sheet and insert the following:

A B C D E F ... VRL VRM 1) D2 D3 D15349 D15350In cell D2 enter the formula: =INDIRECT(E$1)&$D3

Drag the formula Down your 15350 rows.

Drag the formula Across to column VRMThat should give you a matrix of all the numbers, in single cells.

To break them out of the single cell and put the numbers in their own columns, you will need to use the

Text To Columnfunction from the task bar.

But first, you need to break apart the matrix into several sheets, or use the empty rows at the bottom of you current worksheet, else you won't have enough columns for the Text to Columns to work.See how that works out.

MIKE

You can only have a bit over 1 million rows in an Excel spreadsheet. No way to get all of your numbers in a single spreadsheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

that is fantastic mate!

you are a genius!i am working on something big, if it all goes to plan, i will track you down and give you a briefcase full of cash.

i never thought of using formulas, thats brilliant, nice one!

give you a briefcase full of cash.I always like presents. ;-)

Glad I could help.

MIKE

Just a quick heads up. The matrix as presented will contain some doubles,

by that I meanthe same cellwill be counted twice.IE:

Cell # F2 should have the formula: =INDIRECT(F$1)&$D3

which will resolve itself out as D3&D3

same cell counted twice.Cell # G3 should have the formula: =INDIRECT(G$1)&$D4

which will resolve itself out as D4&D4

same cell counted twice.The same applies for Cells, H4, I5, J6, K7, but I think you see the pattern.

Not sure if you want them like that, if not then you can just delete them.

MIKE

I've got a macro written that deals with the "doubles" but the problem related to the number of rows that are available still exists. The OP is looking for an output of over 2MM rows, but you can only use 1,048,576 rows in a spreadsheet.

Until I hear back from the OP on that issue, there no sense in posting a macro that's going to break when it reaches the number of rows (and possibly columns) limit within Excel.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

yes, the doubles are fine, i can use the doubles.

thank you all very much, great help, everyone will get a share of the profits, if it all works out!

sounds like he wants to print out all the lotto number possibilities

Ask Your Question

Weekly Poll