Verbinden zweier Spalten zum Erstellen einer Datenliste

Question

Ich habe derzeit zwei Spalten, die verglichen werden müssen. Spalte A, Blatt 1 und Spalte A, Blatt 2.

Blatt 1 enthält:

A      B       C
5000   Apples  WI
6182   Oranges NY
7271   Grapes  MN
2293   Peanuts FL

Blatt 2 enthält:

A
4032
5233
7271
2293

Sollte Ergebnisse wie ... erhalten.

7271   Grapes  MN
2293   Peanuts FL

Ich muss nur die Ergebnisse anzeigen, die dieselbe Zahl wie Blatt 2 enthalten. Gibt es eine bessere Möglichkeit, als Blatt 2 in ein Array zu laden und es mit jeder Zelle in Blatt 1 zu vergleichen?

    'For i = 1 to Sheet1LastRow

    Sheet2Row = Range("A" & Rows.Count).End(xlUp).Row
    Sheet2Array = Range("A2:A" & Sheet2LastRow).Value
            For i = LBound(Sheet2Array, 1) To UBound(Sheet2Array, 1)

    'if cell = Sheet2Array(i, 1)
     '....
    'End if

    Next i

   'Next Cell sheet 1

Answer 1

Dies ist die Kernfunktion von =VLOOKUP().

Die Syntax lautet:

=VLOOKUP(
         compare this cell, 
         to the cells in the leftmost column of this range,
         returning the corresponding value from this column index,
         true/false for range lookup (just leave this false if you aren't sure)
 )

Auf Blatt 2 würden Sie in Zelle B1 =VLOOKUP(A1,Sheet1!A:B,2,False)und in Zelle C1 Folgendes platzieren:=VLOOKUP(A1,Sheet1!A:C,3,False)

Diese Funktion funktioniert zwischen Arbeitsblättern genauso gut wie zwischen Arbeitsmappen (Sie müssen jedoch Verknüpfungen aktivieren und auf Änderungen an beiden Arbeitsmappen achten). Es ist kein VBA erforderlich, dies ist eine einfache Inline-Funktion.

Wenn Sie dies weiter ausführen, werden Sie wahrscheinlich #N/AFehler für Werte erhalten, die in Ihrer Quelltabelle nicht vorhanden sind. Umschließen Sie die Formel, =IFERROR( your vlookup() function , "" )um alle Fehler durch eine leere Zelle zu ersetzen.

Answer 2

Aus Ihrer Frage und den Beispieldaten geht hervor, dass Sie als Ausgabe eine Spalte mit übereinstimmenden Daten in einem zusammenhängenden Zellenblock ohne Leerzeichen oder Fehler dazwischen suchen.

Wenn Sie einen Formelansatz verwenden möchten, können Sie dies mithilfe der etwas komplexeren INDEX- und MATCH-Funktionen in einer Array-Formel erreichen. Sobald Sie die übereinstimmenden Daten in zusammenhängenden Zellen einer Spalte haben, wenden Sie einfach SVERWEIS an, um den Rest der beiden Spalten aus Ihrer Haupttabelle in Blatt1 abzurufen.

So geht's. Unten sind zwei Screenshots von Sheet1 und Sheet2.

Blatt1 enthält Ihre Mastertabelle in den Spalten A, B und C, Blatt2 enthält Ihre Liste, die mit Spalte A aus Blatt1 abgeglichen werden soll

Geben Sie nun in Sheet2 in Zelle C1 die folgende Array-Formel ein

{=INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISERROR(MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),"",MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),ROW(1:1)))}

Geben Sie diese Formel ohne Klammern ein und drücken Sie in der Formelleiste STRG+UMSCHALT+EINGABE, um eine Matrixformel zu erstellen. Ziehen Sie sie nach unten, bis Sie einen Fehler in den Zellen oder eine Länge Ihrer Liste erhalten, die abgeglichen werden soll. Jetzt haben alle Zeilen über den Fehlerzellen die übereinstimmenden Zellen aus Spalte A von Blatt1.

Löschen Sie einfach die Fehlerzellen und Sie erhalten die Liste in einem zusammenhängenden Zellenblock.

Geben Sie nun in D1 eine relativ einfache SVERWEIS-Formel ein, um die nächste passende Spalte aus Sheet1 abzurufen

=VLOOKUP(C1,Sheet1!$A$1:$C$4,2,FALSE)

Und in E1 setzen Sie die Formel

=VLOOKUP(C1,Sheet1!$A$1:$C$4,3,FALSE)

und ziehen Sie beide nach unten. Sie haben Ihre Liste wie gewünscht.

Sie können die in Excel integrierte Option „Formel auswerten“ verwenden, um die INDEX MATCH-Kombination auszuwerten und ein Gefühl dafür zu bekommen, wie dies funktioniert. Es erstellt ein Array übereinstimmender Zeilennummern und wählt die erste, dann die zweit- und dann die drittkleinste Zahl aus, während Sie die Formel nach unten ziehen.

Zwei Einschränkungen bestehen hier darin, dass die Daten in der Reihenfolge zurückgegeben werden sollen, in der sie in der Mastertabelle vorhanden sind und nicht in der Reihenfolge, in der sie in der Tabelle stehen und „abgeglichen“ werden sollen. Außerdem soll, falls Sie mehr als eine übereinstimmende Zelle haben, die erste zurückgegeben werden.