不排除多列中值的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的版本,不会重复组合。
这里有一个简单的例子。但是,它只有一列,它也重复值:
提前谢谢。
鉴于我们在会话中讨论的内容,以下是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:
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屋!