Wie kann ich für jeden Satz identischer Werte in einer anderen Spalte die eindeutigen Werte in einer Spalte abrufen?

Question

Ich habe folgende zwei Spalten:

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

Ich kann die eindeutigen Werte problemlos entweder in Spalte Con.Byoder Spalte abrufen. Meine Anforderung besteht jedoch darin, die eindeutigen Werte in Spalte für jeden der eindeutigen Werte in Spalte Prodabzurufen . Daher wäre meine beabsichtigte Ausgabe für die obigen Datenspalten:ProdCon.By

Con.By  Prod
   A     1
         2
   B     1
         2

Ich bin mir nicht einmal sicher, wo ich anfangen soll. Ich habe versucht, einen VBA-Code zu schreiben, um die Spalte Con.Byfür jeden Wert einzeln zu filtern und dann eindeutige Werte in der ProdSpalte zu finden. Diese Methode funktioniert jedoch nicht, da meine Con.BySpalte viel zu viele Einträge enthält und sich einige davon mit der Zeit ändern können.

Wie erhalte ich am besten die gewünschte Ausgabe? Gibt es Excel-Formeln oder ist VBA-Codierung erforderlich?

Answer 1

Sie können diese Methode ausprobieren. Sie verwendet eine benutzerdefinierte Klasse, um bei der Sammlung eindeutiger Elemente in der zweiten Spalte zu helfen.

Der Code sowohl im regulären als auch im Klassenmodul nutzt die Tatsache aus, dass ein 457Fehler generiert wird, wenn Sie versuchen, der Sammlung ein Mitglied hinzuzufügen, das denselben Schlüssel wie ein vorhandenes Mitglied hat.

Sie können im Code sehen, wo Sie Änderungen vornehmen müssen, um Unterschiede in Ihrem Arbeitsblatt und den Bereichen für die Quelle (Src) und die Ergebnisse (Res) zu berücksichtigen.

Sie müssen das Klassenmodul umbenennen cConBy. Nachdem Insert Class ModuleSieF4öffnet das Eigenschaftenfenster. Ändern Sie Namedort die Parameter.

Klassenmodul

---

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

---

Reguläres Modul

---

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

---

BEARBEITEN:

Eine alternative Methode, die Ihrem Wunsch nahe kommt, aber ein etwas anderes Ergebnis liefert, wäre, einfach die Option „Duplikate entfernen“ auf der Registerkarte „Daten“ bzw. „Datentools“ zu verwenden. Sie würden die beiden Spalten A und B auswählen.

Stellen Sie sicher, dass Ihre Daten sortiert sind, bevor Sie diese Methode anwenden (bei Verwendung der VBA-Methode wäre eine Sortierung nicht erforderlich).

Mit Ihren veröffentlichten Daten würden die Ergebnisse folgendermaßen aussehen:

Sie können bedingte Formatierung verwenden, um die doppelten Einträge in Spalte A zu eliminieren. Beispiel: Verwenden Sie die Formel =$A2=$A1 und formatieren Sie die Textfarbe so, dass sie mit der Farbe des Hintergrunds übereinstimmt. Der Con.By-Wert wäre noch vorhanden, aber nicht sichtbar.

Answer 2

Versuchen Sie das folgende Rezept, das meiner Meinung nach leichter zu verstehen, aber vielleicht nicht so automatisierungsfreundlich ist wie Rons Antwort.

1. Angenommen, Con.Byes steht in Spalte A und Prodsteht in Spalte B, dann in einer anderen Spalte (sagen wir C), dann verketten Sie die beiden Spalten mit einem Trennzeichen, z. B. "_":

   =A2&"_"&B2Das ist dasselbe wie=CONCATENATE(A2,"_",B2)

2. Anhand Ihres Beispiels sieht die Ausgabe A_1etwa so aus: Kopieren Sie Spalte C und Paste Valuesnur diese in Spalte D.

3. Markieren Sie Spalte D und wählen Sie im Menüband die Option Data -> Remove Duplicates. Spalte D sieht folgendermaßen aus: A_1 A_2

4. Um die Daten wieder in zwei separate Spalten aufzuteilen, verwenden Sie das Menüband und wählen Sie Data -> Text to Columns. Wählen Sie `Getrennt

Answer 3

Versuchen Sie das folgende Rezept, das meiner Meinung nach leichter zu verstehen, aber vielleicht nicht so automatisierungsfreundlich ist wie Rons Antwort.

1. Angenommen, Con.Byes steht in Spalte A und Prodsteht in Spalte B, dann in einer anderen Spalte (sagen wir C), dann verketten Sie die beiden Spalten mit einem Trennzeichen, z. B. "_":

   =A2&"_"&B2Das ist dasselbe wie=CONCATENATE(A2,"_",B2)

2. Anhand deines Beispiels sieht die Ausgabe A_1etwa so aus: Kopieren Sie Spalte C und Paste Valueszwar nur in Spalte D.

3. Markieren Sie Spalte D und wählen Sie im Menüband die Option Data -> Remove Duplicates. Spalte D sieht folgendermaßen aus: A_1 A_2

4. Um die Daten wieder in zwei separate Spalten aufzuteilen, verwenden Sie das Menüband und wählen Sie Data -> Text to Columns. Wählen Sie Delimitedals erste Option und Otherals zweite. Ihr Trennzeichen ist _in diesem Fall.

Dadurch erhalten Sie Ergebnisse, die Ihren Wünschen nahe kommen.