不排除多列中值的Excel排列 [英] Excel permutations without repetition from values in multiple columns

问题描述

  AB 
 Abc 12:34 
 Def 56： 78 
 Ghi 90:12 
 Jkl 34:56 
 ... 
  

  CDEF 
 Abc 12:34 Def 56:78 
 Abc 12:34 Ghi 90:12 
 Abc 12:34 Jkl 34:56 
 Def 56:78 Ghi 90:12 
 Def 56:78 Jkl 34:56 
 Ghi 90:12 Jkl 34:56 
 ... 
  

说明：

列A和B可以包含文本和数字的任意组合（如果这一点很重要），这个例子只显示最常见的结构。它应该仅在下一步的行上创建组合，我。即Abc ... Def ...就够了，不应该有Def ... Abc ...。

有很多例子，但是我正在努力寻找一个适用于多个列的VBA的版本，不会重复组合。

这里有一个简单的例子。但是，它只有一列，它也重复值：

http://www.mrexcel.com/forum/excel-questions/412952-create-list-all-pair-combinations.html ＃post2046893

提前谢谢。

解决方案

鉴于我们在会话中讨论的内容，以下是VBA中的一个解决方案：

  Sub CreatePermutation（）
 
 Dim FirstCell As Integer 
 Dim SecondCell As Integer 
 Dim NumRows As Integer 
 Dim OutputRow As Long 
 
'获取列A中的总行数
 NumRows =单元格（ActiveSheet.Rows.Count，1）.End（xlUp）.Row（）
 
'要在行1开始输出
 OutputRow = 1 
 
对于FirstCell = 1到NumRows  -  1 
对于SecondCell = FirstCell + 1到NumRows 
 
'放入数据从第一个单元格进入列C& D 
 Cells（OutputRow，3）.Value = Cells（FirstCell，1）.Value 
 Cells（OutputRow，4）.Value = Cells（FirstCell，2）.Value 
 
'将第二个单元格的数据放入E& F 
单元格（OutputRow，5）.Value = Cells（SecondCell，1）.Value 
 Cells（OutputRow，6）.Value = Cells（SecondCell，2）.Value 
 
'移动到下一行输出
 OutputRow = OutputRow + 1 
 
下一个SecondCell 
下一个FirstCell 
 End Sub 
  

希望能够完成您要做的工作。

What I have in Excel document:

A     B    
Abc   12:34
Def   56:78
Ghi   90:12
Jkl   34:56
...

What I want to achieve with those values:

C    D      E    F
Abc  12:34  Def  56:78
Abc  12:34  Ghi  90:12
Abc  12:34  Jkl  34:56
Def  56:78  Ghi  90:12
Def  56:78  Jkl  34:56
Ghi  90:12  Jkl  34:56
...

Explanation:

Columns A and B can contain any combination of text and numbers (if that is important at all), this example only displays the most common structure. It should create combinations only for rows "on the way down", i. e. "Abc...Def..." is enough, there shouldn't be "Def...Abc...".

There are many examples around, but I am struggling to find a version of such VBA that works with multiple columns and doesn't repeat combinations.

Here is one example that is simple. But, it's for only one column and it also repeats values:

http://www.mrexcel.com/forum/excel-questions/412952-create-list-all-pair-combinations.html#post2046893

Thank you in advance.

解决方案

Given what we discussed in the conversation, here's a solution in VBA:

Sub CreatePermutation()

Dim FirstCell As Integer
Dim SecondCell As Integer
Dim NumRows As Integer
Dim OutputRow As Long

    ' Get the total number of rows in column A
    NumRows = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row()

    ' You want to start outputting in row 1
    OutputRow = 1

    For FirstCell = 1 To NumRows - 1
        For SecondCell = FirstCell + 1 To NumRows

            ' Put in the data from the first cell into columnc C & D
            Cells(OutputRow, 3).Value = Cells(FirstCell, 1).Value
            Cells(OutputRow, 4).Value = Cells(FirstCell, 2).Value

            ' Put in the data from the second cell into column E & F
            Cells(OutputRow, 5).Value = Cells(SecondCell, 1).Value
            Cells(OutputRow, 6).Value = Cells(SecondCell, 2).Value

            ' Move to the next row to output
            OutputRow = OutputRow + 1

        Next SecondCell
    Next FirstCell
End Sub

Hope this accomplishes what you're looking to do.

这篇关于不排除多列中值的Excel排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！