every possible combination of the contents of 2 columns in excel

Suppose I have 2 Columns:

1st column(contains 1000 rows):

U-0001

U-0002 

2nd column(contains 10 rows):

B01

B02

B03

Now, I want to generate two columns like this(with 10*1000 = 10000 rows):

U-0001 B01

U-0001 B02

U-0001 B03

U-0002 B01

U-0002 B02

U-0002 B03

Solution 1:

this should do it:

Sub combineTwoCol()

    ' i,j,k are counters for first col, second col and the answer col in order.
    k = 1
    For i = 1 To 1000
        For j = 1 To 10
            Cells(k, "C").Value = Cells(i, "A").Value
            Cells(k, "D").Value = Cells(j, "B").Value 
            k = k + 1
        Next j
    Next i
End Sub

edited: you should notice that i assumed you have only those two columns is the file, if not change "A" and "B" to the corrosponding columns you need. and "C" and "D" to where you want the two output columns to be.

also if the 10 and 1000 are just examples and not really the values you can always find them dynamically like this:

'this return to the variable the last row in column A
LastRowColA = Range("A65536").End(xlUp).Row

and replace 1000 with LastRowColA

Solution 2:

Here's a formula version:-

=IF(ROW()-ROW($A$1)<COUNTA(A:A)*COUNTA(B:B),OFFSET($A$1,(ROW()-ROW($A$1))/COUNTA(B:B),0)&" "&OFFSET($B$1,MOD(ROW()-ROW($A$1),COUNTA(B:B)),0),"")

Enter it in (say) C1 and copy down.

Solution 3:

A dynamic array (spill) formula. Works with Excel 365 and probably in Excel 2019, and probably not with previous versions. You only need to enter the formula in one cell (optimally C1), and it populates the column with the data dynamically, no need to drag/copy it to other cells. It is also not a simple array formula, so no need for ctrl+shift+enter.

=INDEX(($A:$A),ROUNDUP(SEQUENCE(COUNTA($A:$A)*COUNTA($B:$B),1,1,1)/COUNTA($B:$B),0))&" "&INDEX($B:$B,MOD(SEQUENCE(COUNTA($A:$A)*COUNTA($B:$B),1,0,1),COUNTA($B:$B))+1)

Note: if you never want to drag the formula horizontally, you can remove the $ signs, it's just good practice to leave them in. And of course there is no need (nor possibility) to drag it vertically.

Detailed explanation:

-the formula has two INDEX functions, to get the arrays needed for concatenation, and a space between them

-INDEX is used to generate a cell reference from an array. would have loved to use INDIRECT, but that currently doesn't support spilling array formulas

-first INDEX generates the references to A column values. it generates an array as long as the number of total values (hence the COUNTA functions), in your 2*3 example an array of 6 length. it does this by creating a sequence that is this long, and filled from 1 to this max value. then it divides each number in the sequence by the number of values in A column, and rounds it up, thus creating (in this example) the array {1,1,1,2,2,2}. then index makes the references to column A, row taken from the array - thus completing the first part

-second index generates the references to B column values. it once again generates the proper length array with SEQUENCE and COUNTA, but here we start it from 0. This is important, because later we use MOD function, which returns division remainder, and that can only go from to COUNTA($B:$B)-1. So we add 1 to it after the MOD, and this is why we started the sequence at 0, so in the end the final array {1,2,3,1,2,3} starts with 1 -the INDEX functions in the end thus create a pair of A column and B column values. as you can see from the arrays that resulted, they will be {[A1 B1],[A1 B2],[A1 B3],[A2 B1],[A2 B2],[A3 B3]} and of course actually puts in their values, which is what we want in the end