如何将2个单独的数组组合到excel中的扩展组合列表中

I've been looking for it everywhere but I can't seem to find the answer although it seems an regular problem.

我一直在寻找它,但我似乎无法找到答案,虽然这似乎是一个常见的问题。

I'm trying to automate the duplication of cells in excel. I have two lists: list 1 with values 1,2,3,4 and the second list is with values a,b,c,d. Now I want to have a file where for every value in list 1, the values in list two are duplicated in excel. So:

我正在尝试自动化excel中的单元格重复。我有两个列表:列表1的值为1,2,3,4,第二个列表的值为a,b,c,d。现在我想要一个文件,对于列表1中的每个值,列表2中的值在excel中重复。所以:

1 - a
1 - b
1 - c
1 - d
2 - a
2 - b
2 - c
2 - d
3 - a
...

I'm wondering if there's a function within excel or if not a macro that I could use to solve this? For this short list it is of course easy to do with autofill, but when the list consists of a few hundred values, it gets more complicated...

我想知道excel中是否有一个函数,或者我是否可以使用它来解决这个问题?对于这个简短的列表,它当然很容易使用自动填充,但当列表包含几百个值时,它会变得更复杂......

2 个解决方案

#1

This should be really easy to understand ...

这应该很容易理解......

Open VBE ALT+F11 and right click VBA project. Insert a Module and copy-paste the below code. Hit F5 to run

打开VBE ALT + F11并右键单击VBA项目。插入模块并复制粘贴以下代码。按F5运行

Sub Main()

    Dim number As Range
    Dim letter As Range

    For Each number In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        For Each letter In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
            With Sheet2
                .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = number
                .Range("B" & .Range("b" & Rows.Count).End(xlUp).Row + 1) = letter
            End With
        Next
    Next

End Sub

The above code assumes that your sheet1 looks like this

上面的代码假设你的sheet1看起来像这样

and the results will be on Sheet2 like this

结果将像这样在Sheet2上

#2

You can do this also with a few formulas/support columns without VBA:

您也可以使用一些没有VBA的公式/支持列来执行此操作:

Let's assume your first category is in column A, starting in A2 and your category is in column B.

假设您的第一个类别位于A列,从A2开始,您的类别在B列。

1. Determine the record count for each category, e.g. in C1: =COUNTA($A:$A)-1 (assuming a header row) and C2 equivalent

确定每个类别的记录数,例如在C1:= COUNTA($ A:$ A)-1(假设标题行)和C2等效

2. Place two support column, e.g. in E and F) - E will hold the row for the first category and F the Id for the second category. Place the following formula in E2: =IF(ISTEXT(E1),1,IF(F2=1,E1+1,E1)) and this in F2: =IF(ISTEXT(F1),1,IF(F1=$C$2,1,F1+1))

放置两个支撑柱,例如在E和F)中 - E将保留第一类的行,F为第二类的Id。在E2中放置以下公式:= IF(ISTEXT(E1),1,IF(F2 = 1,E1 + 1,E1)),这在F2:= IF(ISTEXT(F1),1,IF(F1 = $) C $ 2,1,F1 + 1))

3. Add two more columns for the final result - G for category one with formula =INDEX(A:A,E2+1) and H for category 2 with the formula =INDEX(B:B,F2+1).

为最终结果再添加两列 - 对于类别1,G为公式= INDEX(A:A,E2 + 1),H为类别2,公式= INDEX(B:B,F2 + 1)。

4. Now simply copy the formulas for the columns E:H down for a many rows as required (number of rows required is =C1*C2

现在只需根据需要将E:H列的公式复制多行(所需行数= C1 * C2)

In the end it'll look something like this:

最后它看起来像这样:

You can download the file here.

您可以在此处下载该文件。