Como posso obter os valores únicos presentes em uma coluna para cada conjunto de valores idênticos em outra coluna?

Question

Eu tenho as duas colunas a seguir:

Con.By  Prod
   A     1
   A     1
   A     2
   A     2
   B     1
   B     1
   B     2
   B     2

Posso obter facilmente os valores exclusivos em column Con.Byou column Prod. No entanto, meu requisito é obter os valores exclusivos em Column Prodpara cada um dos valores exclusivos em column Con.By. Portanto, minha saída pretendida para as colunas de dados acima seria:

Con.By  Prod
   A     1
         2
   B     1
         2

Eu nem tenho certeza por onde começar. Tentei escrever um código VBA para filtrar a coluna Con.Bypara cada valor individualmente e, em seguida, encontrar valores exclusivos na Prodcoluna. No entanto, este método não funciona porque minha Con.Bycoluna contém muitas entradas e algumas das quais podem mudar com o tempo.

Qual é a melhor maneira de obter o resultado que preciso? Existem fórmulas do Excel ou requerem codificação VBA?

Answer 1

Você pode tentar este método. Faz uso de uma classe definida pelo usuário para auxiliar na coleta de itens exclusivos na segunda coluna.

O código, tanto no Módulo Regular quanto no Módulo de Classe, aproveita o fato de que ao tentar adicionar um membro à coleção que possui a mesma Chave de um membro existente, 457será gerado um erro.

Você pode ver no código onde fazer alterações para levar em conta as diferenças em sua planilha e intervalos de Origem (Src) e Resultados (Res).

Você deve RENOMEAR o Módulo de Classe cConBy. Depois de você Insert Class Module,F4abre a janela de propriedades. Altere o Nameparâmetro lá.

Módulo de aula

---

Option Explicit
Private pConBy As String
Private pProd As String
Private pProds As Collection

Private Sub Class_Initialize()
    Set pProds = New Collection
End Sub

Public Property Get ConBy() As String
    ConBy = pConBy
End Property
Public Property Let ConBy(Value As String)
    pConBy = Value
End Property

Public Property Get Prod() As String
    Prod = pProd
End Property
Public Property Let Prod(Value As String)
    pProd = Value
End Property

Public Function AddProd(Value As String)
    On Error Resume Next
    pProds.Add Value, CStr(Value)
    On Error GoTo 0
End Function

Public Property Get Prods() As Collection
    Set Prods = pProds
End Property

---

Módulo Regular

---

Option Explicit
Sub UniqueConBy()
    Dim cCB As cConBy, colCB As Collection
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim vSrc As Variant, vRes() As Variant
    Dim I As Long, J As Long, K As Long
    Dim lRowCount As Long

'Source and results location
Set wsSrc = Worksheets("Sheet1")
Set wsRes = Worksheets("Sheet1")
    Set rRes = wsRes.Cells(1, 5)
With wsSrc
    vSrc = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(columnsize:=2)
End With

'Collect and consolidate the data
Set colCB = New Collection
On Error Resume Next
For I = 2 To UBound(vSrc, 1)
    Set cCB = New cConBy
    With cCB
        .ConBy = vSrc(I, 1)
        .Prod = vSrc(I, 2)
        .AddProd .Prod
        lRowCount = lRowCount + 1
        colCB.Add cCB, CStr(.ConBy)
        Select Case Err.Number
            Case 457
                With colCB(CStr(.ConBy))
                    lRowCount = lRowCount - .Prods.Count - 1
                    .AddProd cCB.Prod
                    lRowCount = lRowCount + .Prods.Count
                End With
                Err.Clear
            Case Is <> 0
                MsgBox "Error: " & Err.Number & vbTab & Err.Description
                Stop
        End Select
    End With
Next I
On Error GoTo 0

'Create results array
ReDim vRes(0 To lRowCount, 1 To 2)

'column labels
For I = 1 To UBound(vRes, 2)
    vRes(0, I) = vSrc(1, I)
Next I

'populate the array
For I = 1 To colCB.Count
    With colCB(I)
        K = K + 1
        vRes(K, 1) = .ConBy
        vRes(K, 2) = .Prods(1)
        For J = 2 To .Prods.Count
            K = K + 1
            vRes(K, 2) = .Prods(J)
        Next J
    End With
Next I

Set rRes = rRes.Resize(UBound(vRes, 1) + 1, UBound(vRes, 2))
With rRes
    .EntireColumn.Clear
    .Value = vRes
    With .Rows(1)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    .EntireColumn.AutoFit
End With

End Sub

---

EDITAR:

Um método alternativo, que se aproxima do que você deseja, mas fornece uma saída um pouco diferente, seria simplesmente usar a opção Remover Duplicados na Faixa de Opções de Dados/Guia Ferramentas de Dados. Você selecionaria as colunas A e B.

Certifique-se de que seus dados estejam classificados antes de aplicar este método (a classificação não seria necessária usando o método VBA).

Com seus dados postados, os resultados seriam assim:

Você pode usar a formatação condicional para eliminar as entradas duplicadas na Coluna A. Por exemplo: use uma fórmula =$A2=$A1 e formate a cor do texto para que seja a mesma cor do plano de fundo. O valor Con.By ainda estaria lá, mas não seria visível.

Answer 2

Experimente a receita a seguir, que considero mais fácil de entender, mas talvez não tão fácil de automação quanto a resposta de Ron.

1. Supondo que Con.Byesteja na coluna A e Prodna coluna B, em outra coluna (digamos C), concatene as duas colunas com um separador, por exemplo, "_":

   =A2&"_"&B2que é o mesmo que=CONCATENATE(A2,"_",B2)

2. Usando seu exemplo, a saída será como A_1etc. Copie a coluna C e Paste Valuessomente na coluna D.

3. Destaque a Coluna D e usando o menu da faixa de opções selecione Data -> Remove Duplicates. A coluna D ficará assim: A_1 A_2

4. Para dividir os dados novamente em duas colunas separadas, use o menu da faixa de opções e selecione Data -> Text to Columns. Escolha `Delimitado

Answer 3

Experimente a receita a seguir, que considero mais fácil de entender, mas talvez não tão fácil de automação quanto a resposta de Ron.

1. Supondo que Con.Byesteja na coluna A e Prodna coluna B, em outra coluna (digamos C), concatene as duas colunas com um separador, por exemplo, "_":

   =A2&"_"&B2que é o mesmo que=CONCATENATE(A2,"_",B2)

2. Usando seu exemplo, a saída será como A_1etc. Copie a coluna C e Paste Valuessomente na coluna D.

3. Destaque a Coluna D e usando o menu da faixa de opções selecione Data -> Remove Duplicates. A coluna D ficará assim: A_1 A_2

4. Para dividir os dados novamente em duas colunas separadas, use o menu da faixa de opções e selecione Data -> Text to Columns. Escolha Delimitedcomo primeira opção e Othercomo segunda. Seu delimitador está _neste caso.

Isso lhe dará resultados próximos ao que você deseja.