从excel中的名称列表中产生所有组合 [英] Produce all combinations from a list of names in excel

问题描述

所以我已经有了排列列表，但我想将其转换为组合.因此，我只有一个名称列表"john"，"mike"，"tom"，并且这些名称已经转换为两列排列的排列"john mike"，"john tom"，"mike john"，"mike tom"，"tom john""汤姆·迈克"，那么我将如何删除冗余排列以将其转换为组合，或者制作一个新的宏以仅生成组合会更容易?

So I already have the list of permutations, but i'd like to convert it to combinations. So i have a single list of names "john" "mike" "tom", and these have already been converted to two columns of permutations "john mike" "john tom" "mike john" "mike tom" "tom john" "tom mike", so how would i go about either deleting the redundant permutations to convert it to combinations, or would it be easier to make a new macro to just generate combinations?

很明显，我要查找的新列表是约翰·迈克"，约翰·汤姆"，汤姆·迈克"

So to be clear, the new list i'm looking for would be "john mike" "john tom" "tom mike"

这是昨天给我的置换宏(

Here is the macro for permutations given to me yesterday (source).

Sub Permutation()

Dim NameList As Variant, NameVal As Variant, NameVal2 As Variant
Dim Iter As Long

NameList = Sheet3.Range("A1:A108").Value

Iter = 1
For Each NameVal In NameList
    For Each NameVal2 In NameList
        If NameVal2 <> NameVal Then
            Range("C" & Iter).Value = NameVal
            Range("D" & Iter).Value = NameVal2
            Iter = Iter + 1
        End If
    Next NameVal2
Next NameVal

End Sub

推荐答案

尝试一下:

Sub NoRepetition()

Dim NameList As Variant, NameVal As Variant, NameVal2 As Variant
Dim Iter As Long, OutputList As Range, NotYet As Boolean

NameList = Range("A1:A5").Value

Iter = 1
For Each NameVal In NameList
    For Each NameVal2 In NameList
        LRow = Range("C" & Rows.Count).End(xlUp).Row
        Set OutputList = Range("C1:C" & LRow)
        NotYet = (Application.CountIf(OutputList, NameVal2) = 0)
        If NameVal2 <> NameVal And NotYet Then
            Range("C" & Iter).Value = NameVal
            Range("D" & Iter).Value = NameVal2
            Iter = Iter + 1
        End If
    Next NameVal2
Next NameVal

End Sub

附加概念很简单:只需添加检查以查看名称是否已存在于第一列中.如果是这样，请跳过该组合.如果没有，则将其放入.

The additional concept is simple: just add a check to see if the name already exists in the first column. If it does, skip that combination. If not, put it in.

截屏:

让我们知道这是否有帮助.

Let us know if this helps.

这篇关于从excel中的名称列表中产生所有组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！