i have a file with 15350 rows with 3 numbers in columns ABC

February 16, 2012 at 14:42:44
Specs: Windows 7

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.

See More: i have a file with 15350 rows with 3 numbers in columns ABC

Report •

#1
February 18, 2012 at 12:54:33
Are you printing the list? Or storing in a file? Or finding the occurrences of certain combinations?

Report •

#2
February 18, 2012 at 12:57:38
i want it on file
i have the initial file with 15350 rows of data if you need that to solve the macro?

Report •

#3
February 18, 2012 at 13:35:06
I think max 37 is talking about an Excel workbook?

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
February 18, 2012 at 13:37:45
yes, excel workbook

Report •

#5
February 18, 2012 at 14:32:39
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: 3

You 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 13

In F1 enter the formula: = B1 & A1 result 21
In G1 enter the formula: = B1 & C1 result 23

In H1 enter the formula: = C1 & A1 result 31
In I1 enter the formula: = C1 & B1 result 32

MIKE

http://www.skeptic.com/


Report •

#6
February 18, 2012 at 14:41:32
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?


Report •

#7
February 18, 2012 at 14:52:35
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 pairs

If this is not what you mean, post an example.

MIKE

http://www.skeptic.com/


Report •

#8
February 18, 2012 at 15:04:18

A B C
R1 1 2 3
R2 1 2 4
R3 1 2 5

i 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


Report •

#9
February 18, 2012 at 15:29:06
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

http://www.skeptic.com/


Report •

#10
February 18, 2012 at 16:30:01
First you said:

resulting in the following 6 numbers in a row: 123124, 123125, 124123, 124125, 125123,125124

Then you said:

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

So, 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 and in 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                                                    125124

And so on...

In other words, very long and very wide?

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


Report •

#11
February 18, 2012 at 16:32:39
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 & D4

Which might be a bit more manageable.

MIKE

http://www.skeptic.com/


Report •

#12
February 18, 2012 at 16:47:07

Report •

#13
February 18, 2012 at 16:47:08

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


Report •

#14
February 18, 2012 at 16:48:30

Report •

#15
February 18, 2012 at 17:24:12
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 D15350

In cell D2 enter the formula: =INDIRECT(E$1)&$D3

Drag the formula Down your 15350 rows.
Drag the formula Across to column VRM

That 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 Column function 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

http://www.skeptic.com/


Report •

#16
February 18, 2012 at 17:34:05
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.


Report •

#17
February 18, 2012 at 17:44:22
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!


Report •

#18
February 18, 2012 at 18:25:31
give you a briefcase full of cash.

I always like presents. ;-)

Glad I could help.

MIKE

http://www.skeptic.com/


Report •

#19
February 19, 2012 at 11:03:03
Just a quick heads up.

The matrix as presented will contain some doubles,
by that I mean the same cell will 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

http://www.skeptic.com/


Report •

#20
February 19, 2012 at 12:41:43
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.


Report •

#21
February 20, 2012 at 06:37:58

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!

Report •

#22
April 4, 2012 at 11:06:04
sounds like he wants to print out all the lotto number possibilities

Report •

Ask Question