Combinando todas as combinações de dados de três colunas no excel

Question

Tenho algumas colunas de dados que gostaria de combinar e obtenho cada combinação exclusiva.

Concatenar através de uma linha ou set list é bastante fácil, mas eu prefiro não ter que inserir cada permutação possível ... isso seria uma perda de tempo.

Então, como posso obter o excel para listar cada combinação possível de colunas a, bec? (Eu não quero combinar as opções dentro de cada coluna.

Então, por exemplo, se eu começar com "ado", eu gostaria de ado-ar-en, ado-r-en, ado-um-en, ado-n-en ... bem, você entende que é um longo lista.

    

Answer 1

A origem original de Excellll em SO é aqui . Seu código.

A maneira mais fácil seria pegar as listas de forma independente e armazená-las em uma matriz. Em seguida, use as matrizes para produzir todas as combinações possíveis. Este código será gerado em 3 colunas diferentes, mas você pode concatenar se precisar delas em uma única célula

Sub combinations()

Dim c1() As Variant
Dim c2() As Variant
Dim c3() As Variant
Dim out() As Variant
Dim j, k, l, m As Long


Dim col1 As Range
Dim col2 As Range
Dim col3 As Range
Dim out1 As Range


Set col1 = Range("A1", Range("A1").End(xlDown))
Set col2 = Range("B1", Range("B1").End(xlDown))
Set col3 = Range("C1", Range("C1").End(xlDown))

c1 = col1
c2 = col2
c3 = col3

Set out1 = Range("E2", Range("G2").Offset(UBound(c1) * UBound(c2) * UBound(c3)))
out = out1

j = 1
k = 1
l = 1
m = 1


Do While j <= UBound(c1)
    Do While k <= UBound(c2)
        Do While l <= UBound(c3)
            out(m, 1) = c1(j, 1)
            out(m, 2) = c2(k, 1)
            out(m, 3) = c3(l, 1)
            m = m + 1
            l = l + 1
        Loop
        l = 1
        k = k + 1
    Loop
    k = 1
    j = j + 1
Loop


out1.Value = out
End Sub

    

Answer 2

Uma abordagem é criar uma tabela "tridimensional" para capturar todas as combinações. Suas listas têm 11, 11 e 5 entradas, portanto, crie cinco tabelas 11x11. Cada um dos cinco conterá combinações envolvendo uma das entradas na sua terceira coluna. As células em cada tabela refletirão a combinação de um item da primeira coluna e um do segundo.

Vamos supor que os dados que você mostra estão em A1:C11 . Crie a primeira tabela começando em A15 . Vamos rotular as linhas e colunas da tabela com números de índice que serão usados para procurar as células para cada entrada na tabela. Então, a tabela 1 seria configurada assim:

         [A]  [B]  [C]  [D]  [E]  [F]  [G]  [H]  [I]  [J]  [K]  [L]
    [15]       0    1    2    3    4    5    6    7    8    9    10
    [16]  0
    [17]  1
    [18]  2
    [19]  3
    [20]  4
    [21]  5
    [22]  6
    [23]  7
    [24]  8
    [25]  9
    [26] 10

A fórmula em B16 seria:

    =OFFSET($A$1,$A16,0,)&OFFSET($B$1,B$15,0,)&$C$1

Copie isso para preencher todas as células da tabela. A primeira expressão ancora a lista na coluna A e seleciona a entrada correspondente com o rótulo da linha da tabela (a primeira linha possui um deslocamento de zero, portanto, refere-se à primeira entrada na lista). A segunda expressão ancora a lista na coluna B e seleciona a entrada correspondente ao rótulo da coluna. A terceira expressão usa o primeiro valor na lista C da coluna para todas as entradas na primeira tabela. Os cifrões nas expressões ancoram as fórmulas aos rótulos de linha e coluna da tabela para que você possa copiar a fórmula para preencher a tabela e cada célula se referirá aos rótulos corretos. Os e comercial concatenam os três valores. Se você quiser ter um delimitador entre os valores, como o hífen mostrado, use-o no lugar do "e" comercial na fórmula:

    &"-"&

Portanto, B16 mostrará adaren ou ad-ar-en . C16 mostrará adren ou ad-r-en . B17 mostrará adoaren ou ado-ar-en .

Crie as outras tabelas exatamente da mesma maneira ao lado ou abaixo da primeira tabela, onde for conveniente. Quando você inserir a fórmula para a primeira célula, aponte para os rótulos de linha e coluna nessa tabela. Por exemplo, se a segunda tabela começar abaixo da primeira tabela em, digamos, A30 , a primeira célula da tabela será B31 . A primeira expressão nessa fórmula usaria $A31 em vez de $A16 e B$30 em vez de B$15 . A última expressão na segunda tabela será $C$2 em vez de $C$1 . Na terceira tabela, será $C$3 e assim por diante.

As cinco tabelas conterão todas as combinações das entradas da sua lista.

    

Answer 3

Não testado, não tenho o Excel disponível (usando o LibreOffice em casa, isso não funcionará no LibreOffice).

SE FUNCIONA ;-) deve / faz: Assume que "anúncio" está na célula A1 e o restante ao lado dele. Preencha D1 e abaixo com resultados combinados, desde que a célula 'atual' não seja uma célula vazia em cada uma das colunas A, B e C.

sub list_gen

  a=1
  d=1
  while Worksheets(1).Cells(a,1).Text<>""
    astr=Worksheets(1).Cells(a,1).text
    b=1
    while Worksheets(1).Cells(b,2).text<>""
      bstr=Worksheets(1).Cells(b,2).text
      c=1
      while Worksheets(1).Cells(c,3).text<>""
        cstr=Worksheets(1).Cells(c,3).text
        c=c+1
        Worksheets(1).Cells(d,4).text=astr+"-"+bstr+"-"+cstr
        d=d+1
       wend 
       b=b+1
    wend  
    a=a+1
  wend 

end sub

    

Answer 4

Supondo que você tenha as listas como as seguintes ... execute a macro e você terá todas as suas permutações possíveis no formato especificado

List_A  List_B  List_C  
A1      B1      C1      
A2      B2      C2      
A3  

    Sub RandomCombo()

    ListA = Application.WorksheetFunction.Transpose(Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value) 'data starts in 2nd row
    ListB = Application.WorksheetFunction.Transpose(Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value) 'data starts in 2nd row
    ListC = Application.WorksheetFunction.Transpose(Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Value) 'data starts in 2nd row

    counter = 2

    For i = 1 To UBound(ListA)
        For j = 1 To UBound(ListB)
            For k = 1 To UBound(ListC)
                Cells(counter, 4) = ListA(i) & "-" & ListB(j) & "-" & ListC(k)
                counter = counter + 1
            Next k
        Next j
    Next i

    End Sub