So filtern Sie eine Excel-Spalte nach Einträgen, die einer Master-Liste entsprechen
14306
Chris
Die allgemeine Frage beinhaltet das Sortieren einer großen Excel 2007-Liste, um Einträge zu finden, die mit einer kleineren Teilmengenliste übereinstimmen.
Ich habe ein paar Ideen, wie man das Problem angehen kann, aber mir fehlt die technische Raffinesse, um diese Ideen umzusetzen. Ich werde meine speziellen Anwendungsfälle beschreiben, um die Frage klarer zu machen.
Spezifisches Beispiel:
Ich habe eine Hauptliste mit Firmennamen, die ich für mein Vertriebsgebiet verwalte (ca. 1000 Kundenkonten). Jede Woche veröffentlicht mein Unternehmen eine Liste aller getätigten Geschäfte in allen Vertriebsgebieten der USA (meines und meines Gebiets). Dies ist alles Das Transaktionsprotokoll umfasst mehr als 10.000 Zeilen, daher ist das Scannen mit dem Auge, um die mit meinen Konten verknüpften Transaktionen zu finden, nahezu unmöglich.
Meine derzeitige unangemessene Lösung ist, meine Kontoliste gelb zu markieren, die markierte Liste zu kopieren, diese hervorgehobene Liste am Ende des wöchentlichen Transaktionsprotokolls einzufügen, anschließend AZ zu sortieren und dann manuell zu den hervorgehobenen Elementen zu blättern. Wenn das Transaktionsprotokoll eines meiner Konten enthält, befindet sich der Transaktionsprotokolleintrag direkt über oder unter dem markierten Eintrag, den ich eingefügt habe. Diese Methode ist effektiv, aber extrem zeitaufwändig.
Ich weiß, wie Duplikate in Excel entfernt werden können. Gibt es eine Möglichkeit, alles zu entfernen, aber Duplikate? Dies würde das visuelle Scannen der Liste erleichtern.
Ein weiteres Problem bleibt, da die Inkonsistenz von Daten die Verwendung einfacher Makros, Filter oder die Schaltfläche "Duplikate suchen" eingeschränkt hat. Transaktionsprotokollnamen werden oft etwas anders geschrieben als in meiner Master-Liste.
Beispiel: Acme Widget Company, Inc .; Acme Widget Inc; Acme Widget; ZB: United States Hand-Ball-Organisation; US Handball Org; US Handball; USHO
Ich weiß, dass es einige Apps von Drittanbietern gibt, die Fuzzy-Logik verwenden können, um nicht genaue Einträge abzugleichen. Ich kann jedoch keine Plug-Ins auf meinem Unternehmenscomputer ausführen. (Es sei denn, es gibt einen sehr zwingenden Fall ...)
Gibt es ein Makro, das das Transaktionsprotokoll "normalisieren" kann, indem Leerzeichen und Satzzeichen entfernt werden? Gibt es ein Makro, das mit der ersten X-Anzahl von Zeichen übereinstimmen kann (mehr Zeichen = höhere Genauigkeit, aber größere Wahrscheinlichkeit, dass ein fast doppelter Eintrag fehlt)? Gibt es ein Makro, das die resultierende Übereinstimmungsliste ausgeben oder filtern kann?
Wenn diese Aufgaben zu kompliziert sind, habe ich eine viel einfachere Idee. Nach dem Zusammenführen meiner hervorgehobenen Kontenliste in das Transaktionsprotokoll wäre es schön, wenn Sie alle anderen Transaktionsprotokollzeilen ausblenden könnten, die weniger als 5 Zeilen über oder unter meinen hervorgehobenen Elementen liegen. Dies würde eine gewisse Flexibilität für nicht standardisierte Schreibweisen ermöglichen, die visuelle Überprüfung durch die Liste jedoch erheblich vereinfachen.
Jeder Beitrag zur Umsetzung dieser Ideen - oder ganz andere Ansätze - wäre sehr dankbar. Ich denke, die generelle Antwort auf diese Frage wird für andere als den von mir beschriebenen engen Anwendungsfall wertvoll sein.
Vielen Dank!
@ Chris, dieser Beitrag hat zu viele Fragen. In der Zukunft sollten Sie jeden einzeln fragen.
hyperslug vor 15 Jahren
2
In Beispiel 2 (US-Handball) würde das Entfernen von Whitespace + Sonderzeichen und übereinstimmenden ersten X-Zeichen geringe Übereinstimmungen ergeben: "US" steht nicht in der Nähe von "United". Ein anständiges Makro kann jedoch einige Übereinstimmungen herausziehen. Sie sind sich nicht sicher, ob dies Ihre Arbeitsbelastung verringern oder immer noch zu viele manuelle Prozesse verursachen würde. Wieder sollten Sie dies aufteilen oder angeben, in welchem Bereich eine Lösung Sie am meisten Zeit sparen würde: Nicht-Duplikate entfernen? Fuzzy-Matching? Linien außerhalb von + - 5 ausblenden?
hyperslug vor 15 Jahren
0
Es gibt definitiv zu viele Fragen, die hier beantwortet werden müssen (als hyperslug Kommentare). Ich habe eine sehr ähnliche Situation und habe herausgefunden, dass ich es nur manuell tun musste, um Dupes zu finden, da es zu viele Codierungen gab.
Alle Makros, die Sie vorschlagen, können geschrieben werden. Wenn Sie entscheiden, welches am effektivsten ist, fragen Sie dies als separate Frage, und wir tun, was wir können. Die letzte ist einfach zu implementieren und spart Zeit beim Scrollen. Ich würde dieses Makro erstellen, und nachdem die Dupes ausgeblendet wurden, klicken Sie einfach auf den Standardeintrag und ziehen ihn über die anderen.
1
J.T. Grimes
Ich würde die MATCH-Funktion von Excel verwenden, um die benötigten Daten zu erhalten, anstatt zu kopieren und zu sortieren.
Angenommen, Ihre Master-Liste befindet sich in einem benannten Bereich mit dem Namen Master, und der Firmenname im Transaktionsprotokoll befindet sich in Spalte D. Geben Sie in der Zeile der Transaktion die folgende Formel ein: =IF(ISNA(MATCH(D1,Master,0)),0,1)und kopieren Sie sie in alle Zeilen der Transaktionstabelle. Diese Formel ergibt 1, wenn der Firmenname übereinstimmt, und sonst 0.
Dies wird nur mit exakten Namen übereinstimmen. Sie müssen dem Master-Bereich alternative Namen hinzufügen (stellen Sie sicher, dass Sie ihn nach dem Hinzufügen von Namen sortieren), um alle möglichen Versionen zu erhalten.
0
AdamV
Ich stimme dem Ansatz zu, alternative Schreibweisen zu Ihrer Master-Liste hinzuzufügen (möglicherweise haben Sie eine zweite Spalte, in der Sie angeben können, welches das bevorzugte Format für das Mailing usw. ist und welches nur zu den Unternehmensdaten passen soll). Möglicherweise haben Sie einige Erfolge, wenn Sie aufeinanderfolgende SUBSTITUTE-Funktionen verwenden, um eine alternative Version der Namen zu generieren. z.B
Jede Ersetzung ersetzt also jede Instanz des ausgewählten Textes durch die Ersetzung - in unserem Fall hier nichts. Aufgrund meiner Erfahrung mit einem ähnlichen Fuzzy-Matching zwischen Namen aus unterschiedlichen Systemen müssen Sie möglicherweise Dinge wie inc, corp, plc usw. ablegen, um Übereinstimmungen zu erhalten. Während Sie SUBSTITUTE dafür verwenden können, könnten Sie einige merkwürdige Ergebnisse erhalten, wenn "Income Corporation" zu "omeorporation" wird. Daher ist es möglicherweise sicherer, diese Art von Dingen zu verwenden:
WENN (RECHTS (unten (A1), 4) = "corp", links (unten (A1), len (A1) -4)), niedriger (A1)).
Ersetzen Sie zuletzt die Leerzeichen.
Sie können MATCH oder COUNTIF mit ähnlichen Ergebnissen verwenden, um eine Spalte mit den Transaktionen anzuzeigen, die Ihrer Liste entsprechen.
Eine Alternative wäre die Verwendung Ihrer Stammliste als Kriterium für einen erweiterten Filter, mit dem Sie sehr leicht eine Kopie der Transaktionslisteneinträge erstellen können, die Ihren Kundennamen entsprechen, und diese gefilterte Kopie an einer anderen Stelle (z eine Seite oder auf einem anderen Blatt). Genau wie bei den oben genannten müssen Sie immer noch Varianten hinzufügen, deren Varianten zu weit von Ihrem ursprünglichen Namen entfernt sind.
0
RocketGoal
Ich habe mich nur gefragt, ob Sie versucht haben, einen Pivot-Tisch zu verwenden. Ich verwende eine Menge Daten mit Hilfe von PT, und sie helfen mir, Probleme auf verschiedene Weise sehr schnell und mit vollständiger Datenintegrität zu betrachten.
Markieren Sie alle Ihre Daten und wählen Sie Insert Pivot Table. Sie können nun Ihre Daten auf viele interaktive Arten überprüfen, um lästige Doppeleinträge, falsche Schreibweisen usw. einzuschränken. Sie können dann mit benutzerdefinierten Sortierungen usw. sowie mit AZ sortieren.