Excel中2列内容的所有可能组合 [英] every possible combination of the contents of 2 columns in excel

问题描述

假设我有2列:

第一列(包含1000行):

1st column(contains 1000 rows):

U-0001

U-0002 

第二列(包含10行):

2nd column(contains 10 rows):

B01

B02

B03

现在，我想生成两列(10 * 1000 = 10000行):

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

推荐答案

这应该做到:

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

您应该注意到，我假设您只有那两列是文件，如果没有更改，则将"A"和"B"更改为所需的相应列.以及"C"和"D"到您希望两个输出列所在的位置.

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.

如果10和1000只是示例，而不是真正的值，那么您始终可以像这样动态地找到它们:

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

，然后将1000替换为LastRowColA

这篇关于Excel中2列内容的所有可能组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！