☰

Vergleichen zweier Spalten mit Teilwerten und Abrufen von Informationen zu Übereinstimmung und Spaltennummer

tag-icon • tag-icon microsoft-excel worksheet-function
Vergleichen zweier Spalten mit Teilwerten und Abrufen von Informationen zu Übereinstimmung und Spaltennummer

Ich habe SVERWEIS, VERGLEICH, INDEX und sogar das Fuzzy-Add-on verwendet. Ich bin sicher, dass eines davon oder eine Kombination davon funktionieren könnte, mir fehlt nur das Know-how, um es zum Laufen zu bringen, und ich erhalte ständig „#N/A“ …

Problem: Ich habe zwei Spalten mit unterschiedlichen Gesamtzahlen von Elementen, die voller Referenzen sind. Eine hat teilweise dieselben Zahlen wie die andere, mit einigen Änderungen, wie folgt:

Column A typical number (ex): 025983553-1 
Column B typical number (ex): 225983553

Ich habe aber Tausende von Zahlen und möchte jede Zelle von A mit dem gesamten Bereich von B abgleichen. Und wenn es eine Übereinstimmung gibt, sogar in Zelle B6544, möchte ich es wissen und etwas (in Spalte C) wie „True B6544“ erhalten.

Ein (kleines) Beispiel dessen, womit ich mich beschäftige (und nicht die genauen Referenzen, das sind intern sensible Daten):

       B                J       H
  1 025983553-1     225983553   True, B1
  2 025973223-1     222222345   False
  3 025965463-2     233444667   False
  4 025911122-4     211198989   False
  5 025998764-1     212989238   False
  6 025925925-3     224397501   False
  7 025900000-2     225973223   True, B2
  8 025999999-5     223334445   False
  9 025965453-6     211100110   False
 10 025943536-2     225911122   True, B4
 ...

Wie Sie sehen, ist J1 eine teilweise Übereinstimmung mit B1, daher gibt H1 „True, B1“ zurück.

Wie oben erwähnt, habe ich MATCH, VLOOKUP, INDEX und das Fuzzy-Add-on ausprobiert. Ich verstehe, dass ich so etwas machen muss wie:

 =VLOOKUP(B1, $J$1:$J$10, valueThat IwantIThink, FALSE)

Aber nichts scheint zu funktionieren ... jede Hilfe ist willkommen!

Antwort1

Probieren Sie diese kleine benutzerdefinierte Funktion aus:

Public Function PartialMatch(r1 As Range, r2 As Range) As Variant
    Dim boo As Boolean, v As Variant, r As Range
    boo = False
    v = Mid(r2.Text, 2)
    For Each r In r1
        If InStr(1, r.Text, v) > 0 Then
            PartialMatch = "True, " & r.Address(0, 0)
            Exit Function
        End If
    Next r
    PartialMatch = boo
End Function

Bildbeschreibung hier eingeben

Wie die Abbildung zeigt,K1eingeben:

=partialmatch($B$1:$B$10,J1)

und nach unten kopieren.

Die Routine entfernt die führende Ziffer aus der Teilzeichenfolge und versucht, sie innerhalb der Spalte zu finden.

Antwort2

Da Excel keine regulären Ausdrücke unterstützt, glaube ich nicht, dass es eine Formel gibt, die keine Hilfsspalte benötigt. In meinem Beispiel

  • A enthält die Zahlen mit einem "-"
  • B enthält die Zahlen, die übereinstimmen
  • C enthält die Werte von B ohne die erste Ziffer
  • D ist „FALSCH“, wenn der Wert von A mit keinem in Spalte B übereinstimmt, oder „WAHR“ + Verweis auf die übereinstimmende Zelle.

Die Formel in C1 lautet:

=RIGHT(B1,LEN(B1)-1)

Dadurch wird die erste Ziffer von B1 entfernt.

Die Formel in D1 lautet:

=IFERROR("TRUE, "&ADDRESS(MATCH(MID(A1,2,SEARCH("-",A1)-2),C$1:C$10,0),COLUMN(B1)),FALSE())

Beachten Sie, dass dies zwar die gewünschte Ausgabe erzeugen sollte, ich jedoch empfehlen würde, TRUE/FALSE und den Zellbezug in zwei Spalten aufzuteilen. Daher werde ich nur den ADDRESSTeil erklären, der Ihnen den Zellbezug liefert, wenn eine Übereinstimmung gefunden wird, oder andernfalls einen Fehler.

VLOOKUPist hier nicht nützlich, da es einen Wert in derselben Zeile wie der übereinstimmende Wert zurückgibt. MATCHgibt andererseits die Zeile des übereinstimmenden Werts zurück.

  • MID(A1,2,SEARCH("-",A1)-2)Gibt die Teilzeichenfolge von A1 zurück, beginnend mit dem zweiten Zeichen bis zum ersten Vorkommen von "-". Dies ist der gesuchte Wert ( lookup_value).
  • MATCH(lookup_value, C$1:C$10, 0)gibt die Zeile des ersten Vorkommens von zurück lookup_value. Da wir auf den Bereich keine Funktion anwenden können und Excel keine regulären Ausdrücke unterstützt, benötigen wir die Hilfsspalte C. Der letzte Parameter ( 0) wird benötigt, da die Werte nicht sortiert sind. Beachten Sie, dass der zurückgegebene Zeilenwert relativ zum angegebenen Bereich ist. Wenn Ihr Bereich also nicht bei Zeile 1 beginnt, müssen Sie dies berücksichtigen (z. B. indem Sie ROW([first cell])-1zum Ergebnis von hinzufügen MATCH).
  • ADDRESS(matched_row, COLUMN(B1))erzeugt den Zellbezug. Sie können statt COLUMN(B1)dessen auch die absolute Nummer der Zeile verwenden, aber das wäre für Menschen nicht so lesbar.

Welche Referenzen absolut oder relativ sein sollen, müssen Sie selbst entscheiden.

Antwort3

Ich versuche, Ihre Frage teilweise zu beantworten, da wir noch nicht das Gesamtbild haben.

Ich habe zwei Hilfsspalten hinzugefügt, um die Daten zu verarbeiten und zu vergleichen – das wird Ihnen ein besseres Verständnis ermöglichen.

Die C-helperSpalte entfernt die 0am Anfang und am Ende - number.
Die I- helperSpalte entfernt die führende Ziffer (oder die erste Ziffer) der J col.
Dann habe ich eine vlookupDatenanalyse I-helperdurchgeführt C - helperund basierend auf dem vlookup()-Ergebnis herausgefunden, ob die Daten gefunden wurden oder nicht.

Die Formel habe ich in

  • C - helper:==MID(B4,2,LEN(B4)-3)
  • I - helper:=MID(D4,2,LEN(D4)-1)
  • vlookup col: =IF(ISNA(VLOOKUP(E4,$C$4:$C$13,1,FALSE)),"not_found","found")

Der nächste Teil des Problems besteht darin, herauszufinden, wo dieser Eintrag gefunden wurde. Wenn der Eintrag gefunden wird, wird seine Adresse zurückgegeben, andernfalls die Zeichenfolge Not_Available is returned. Die Beispieldaten sehen nun so aus

ABC - Helfer JI - Helfer vlookup col HI X-Helfer
1 025983553-1 25983553 225983553 25983553 gefunden Wahr, B1 $C$4
2 025973223-1 25973223 222222345 22222345 nicht_gefunden FALSE Nicht_verfügbar
3 025965463-2 25965463 233444667 33444667 not_found FALSE Nicht verfügbar
4 025911122-4 25911122 211198989 11198989 nicht_gefunden FALSE Nicht_verfügbar
5 025998764-1 25998764 212989238 12989238 nicht_gefunden FALSCH Nicht_verfügbar
6 025925925-3 25925925 224397501 24397501 nicht_gefunden FALSCH Nicht_verfügbar
7 025900000-2 25900000 225973223 25973223 gefunden Wahr, B2 $C$5
8 025999999-5 25999999 223334445 23334445 nicht_gefunden FALSE Nicht_verfügbar
9 025965453-6 25965453 211100110 11100110 nicht_gefunden FALSE Nicht_verfügbar
10 025943536-2 25943536 225911122 25911122 gefunden Wahr, B4 $C$7

Bearbeiten

Die Formel habe ich in

  • X - helper:=IF(F4="found",CELL("address",INDEX($B$4:$C$13,MATCH(E4,$C$4:$C$13,0),2)),"Not_Available")

verwandte Informationen