# 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

#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 filei 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: 1In Cell B1 you have a number, example: 2In Cell C1 you have a number, example: 3You now need Six additional columns:D - E - F - G - H - IIn D1 enter the formula: = A1 & B1 result 12In E1 enter the formula: = A1 & C1 result 13In F1 enter the formula: = B1 & A1 result 21In G1 enter the formula: = B1 & C1 result 23In H1 enter the formula: = C1 & A1 result 31In I1 enter the formula: = C1 & B1 result 32MIKE

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 pairsIf this is not what you mean, post an example.MIKEhttp://www.skeptic.com/

Report •

#8
February 18, 2012 at 15:04:18
 A B CR1 1 2 3R2 1 2 4R3 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,1251246 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. MIKEhttp://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,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 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 columnThen it becomes a problem of D1 & D2D1 & D3D1 & D4Which might be a bit more manageable.MIKEhttp://www.skeptic.com/

Report •

#12
February 18, 2012 at 16:47:07
 Oh sure, Mike...take care of the easy stuff! ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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 J1 2 3 1 2 41 2 3 1 2 5

Report •

#14
February 18, 2012 at 16:48:30
 Well, that changes everything!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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&C1Drag 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)&\$D3Drag 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 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

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.MIKEhttp://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)&\$D3which will resolve itself out as D3&D3same cell counted twice.Cell # G3 should have the formula: =INDIRECT(G\$1)&\$D4which will resolve itself out as D4&D4same 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

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 •