Tabellenvergleich in Excel - schnellste Lösung
Susanne Wenzel
es gibt hunderte von Threads zu meinem Subject, ich habe mir diverse davon
zu Gemüte geführt, konnte aber nicht direkt herauslesen, welche Möglichkeit
die schnellste ist.
Situation: ich bekomme beruflich Datensätze aus Datenbanken als
Exceltabelle zur Verfügung gestellt. So weit so gut (oder schlecht, ich
hätte lieber direkten Zugriff auf die Datenbank, habe ihn aber nicht). Das
klitzekleine Problem liegt in der Anzahl der Datensätze, die je nachdem
zwischen 10.000 und 60.000 betragen kann, man hat also mit grauslig
monströsen Exceldateien zu tun...
Und eine Auswertung besteht darin, 2 Tabellen miteinander zu vergleichen
und festzustellen, welche Werte in der einen, welche in der anderen und
welche in beiden Tabellen vorkommen, Kriterium des Vergleichs ist eine
mehrstellige Nummer (der Primärschlüssel datenbanktechnisch).
Aktuell hatte ich eine Tabelle mit ca. 35.000 Datensätzen mit einer zweiten
mit ca. 55.000 Datensätzen auf die oben beschriebene Weise miteinander zu
vergleichen. Ich habe in meiner VBA-Schatzkiste eine Routine, die das macht
(mit Hilfe einer doppelten For-Schleife, wie auch in vielen der vorhandenen
Threads vorgeschlagen). Die habe ich zwar angeworfen, ist aber
performancemäßig grausam, wie man sich ja vorstellen kann. Habe sie deshalb
nach 10 Minuten oder so abgebrochen und stattdessen mit SVerweis
gearbeitet, der meinen Rechner auch so gut wie lahmgelegt hat, immerhin
aber zu einem Ergebnis kam. Den dann auch in der 2. Tabelle angewandt, nach
viel Rechenzeit auch dort ein Ergebnis. Aber natürlich alles nur halber
Kram, auf die Art sehe ich ja immer nur die fehlenden/vorhandenen
Datensätze aus der Sicht der entsprechenden Tabelle, da fehlt der
Gesamtüberblick. Dann gingen mir die (Excel-)Ideen aus, ich habe beide
Tabellen per DTS in eine SQLServer-Datenbank gepackt, ein paar Abfragen
gebastelt, das von mir gewünschte Ergebnis hatte ich dann nach ein paar
Sekunden. Dieser Weg steht mir offen, weil ich da ein bisschen Kenntnisse
habe, meinen Kollegen mangels dieser Kenntnisse aber nicht. Die haben aber
mit den gleichen Monstertabellen zu tun und machen die gleichen
Auswertungen wie ich und verfügen lediglich über Excelgrundkenntnisse.
Lange Vorrede, kurze Frage:
Hat jemand einen Tip für mich, welches Excel-Verfahren/welche
Excel-Vorgehensweise ich meinen Kollegen empfehlen könnte, das/die am
wenigsten Zeit verschlingt?
Vielen Dank fürs Lesen und eventuelle Tips.
Viele Grüße aus dem hohen flachen Norden Deutschlands
Susanne
Eike Bimczok
ich würde empfehlen, dass du im ersten Schritt die Daten komplett in
Arrays einliest und dann vergleichst. Wenn du es bisher so versuchst hast:
if cells(1,1).value = cells(1,2).value
musst du ständig erneut die Daten aus der Tabelle auslesen und dann erst
vergleichen. Dabei ist die Performance sehr schlecht.
Du kannst die Daten wie folgt in ein Array bekommen:
Dim a(), myRange As Range
Set myRange = ActiveSheet.UsedRange
a = myRange
Das gleiche machst du für die andere Tabelle in ein Array b(). Dann
liegen die Daten der Tabellen in den Arrays a und b, also im
Arbeitsspeicher und lassen sich extrem schnell abfragen.
Wie du dann die beiden Arrays a() und b() am schnellsten vergleichst,
hängt vom Aufbau der Daten ab, im ersten Schritt kannst du es ja mal mit
Forschleifen versuchen, eventuell mit geeigneten Abbruchbedingungen.
Gruß
Eike
Susanne Wenzel schrieb:
Michael Tsiaousidis
Die Exceltabellen einfach in Access importieren und die Auswertung mit einer
Vergleichsabfrage durchführen, fertig!
Gruß
Micha
"Susanne Wenzel" <wirdnich...@onlinehome.de> schrieb im Newsbeitrag
news:1lp36801ss96a$.96zkh8kf43h6$.dlg@40tude.net...
Michael Schwimmer
Susanne Wenzel schrieb:
> Und eine Auswertung besteht darin, 2 Tabellen miteinander zu
> vergleichen und festzustellen, welche Werte in der einen, welche in
> der anderen und welche in beiden Tabellen vorkommen, Kriterium des
> Vergleichs ist eine mehrstellige Nummer (der Primärschlüssel
> datenbanktechnisch).
das kann man sehr schön mit Collections machen. Das folgende
Beispiel habe ich bei zwei Blättern mit je 65536 "Primärschlüsseln" in
Spalte 1 getestet, wobei ca. die Hälfte gemeinsam sind. Der Vergleich
dauert ca. 5 Sekunden.
Als Ergebnis hat man eine Collection, die nur die in Tabelle1
vorkommenden Schlüssel samt der Zeile und dem Blattnamen enthält. Eine
weitere Collection enthält die gleichen Daten der nur in Tabelle2
vorkommenden Schlüssel. Die dritte Collection enthält die, die gemeinsam
vorkommen.
Nun könnte man beispielsweise die Daten auf extra Blättern ausgeben, die
gemeinsamen der Originaltabellen einfärben, oder einen Wert in eine
Hilfsspalte einfügen, nach der man filtern kann. Möglich ist vieles.
Private Sub vergleich()
Dim varTabelle1 As Variant
Dim varTabelle2 As Variant
Dim wsTab1 As Worksheet
Dim wsTab2 As Worksheet
Dim varCell As Variant
Dim varDummy As Variant
Dim colTab1 As New Collection
Dim colTab2 As New Collection
Dim colGemeinsam As New Collection
Dim colOnly1 As New Collection
Dim colOnly2 As New Collection
Dim avarDummy(1 To 3) As Variant
Dim i As Long
Set wsTab1 = Worksheets(1)
Set wsTab2 = Worksheets(2)
With wsTab1
varTabelle1 = .Range(.Cells(1, 1), .Cells(65536, 1))
End With
With wsTab2
varTabelle2 = .Range(.Cells(1, 1), .Cells(65536, 1))
End With
On Error Resume Next
For i = 1 To 65536
If varTabelle1(i, 1) <> "" Then
avarDummy(1) = varTabelle1(i, 1)
avarDummy(2) = wsTab1.Name
avarDummy(3) = i
colTab1.Add avarDummy, "X" & varTabelle1(i, 1)
End If
If varTabelle2(i, 1) <> "" Then
avarDummy(1) = varTabelle2(i, 1)
avarDummy(2) = wsTab2.Name
avarDummy(3) = i
colTab2.Add avarDummy, "X" & varTabelle2(i, 1)
End If
Next
For Each varCell In colTab1
Err.Clear
varDummy = colTab2("X" & varCell(1))
If Err.Number <> 0 Then
colOnly1.Add varCell
Else
colGemeinsam.Add varCell, "X" & varCell(1)
End If
Next
For Each varCell In colTab2
Err.Clear
varDummy = colTab1("X" & varCell(1))
If Err.Number <> 0 Then
colOnly2.Add varCell
Else
colGemeinsam.Add varCell, "X" & varCell(1)
End If
Next
'Application.ScreenUpdating = False
'With Worksheets(3)
' i = 1
' .Cells.Clear
' For i = 1 To colGemeinsam.Count
' .Cells(i, 1) = colGemeinsam(i)(1)
' .Cells(i, 2) = colGemeinsam(i)(2)
' .Cells(i, 3) = "Zeile : " & colGemeinsam(i)(3)
' Next
'End With
'Application.ScreenUpdating = True
End Sub
MfG
Michael
--
Michael Schwimmer http://michael-schwimmer.de
Excel VBA ISBN 3-8273-2183-2
Excel Programmierung - Das Handbuch ISBN 3-8606-3548-4
Eric March
> Hallo NG,
>
>
> Hat jemand einen Tip für mich, welches Excel-Verfahren/welche
> Excel-Vorgehensweise ich meinen Kollegen empfehlen könnte, das/die am
> wenigsten Zeit verschlingt?
>
> Vielen Dank fürs Lesen und eventuelle Tips.
>
> Viele Grüße aus dem hohen flachen Norden Deutschlands
> Susanne
Die Lösungen sind alle ganz pfiffig - aber mich würde mal reizen, was du
mit dem Ergebnis des Vergleichs anzustellen wünschst. Nur eine Liste mit
Schlüssen die gleich sind zurückzuliefern kanns doch wohl nicht sein..??
(Michael Tsiaousidis' Idee mit der Datenbank müsste sich biem
Lieferanten der Tabellen ebenfall machen lassen - deswegen denke ich
hast du mehr zu tun als nur eine Liste zu erstellen...)
--
Eric March
Kenne die Vergangenheit. In der Unwissenheit über die Vergangenheit
liegt das Verderben der Zukunft.
Susanne Wenzel
vielen Dank für die Anregungen, die Geschichte mit den Collections klingt
ja richtig interessant, da ist bestimmt was für mein VBA-Schatzkästchen
dabei.
Allerdings...
ich suche zumindest in dieser Anfrage nicht wirklich eine Lösung für
*mich*, denn ich bin glücklich mit der SQLServer-Lösung...
Am Sat, 25 Feb 2006 09:39:26 +0100 schrieb Susanne Wenzel:
[...]
> Dieser Weg steht mir offen, weil ich da ein bisschen Kenntnisse
> habe, meinen Kollegen mangels dieser Kenntnisse aber nicht. Die haben aber
> mit den gleichen Monstertabellen zu tun und machen die gleichen
> Auswertungen wie ich und verfügen lediglich über Excelgrundkenntnisse.
>
...vielleicht kam das hier nicht deutlich heraus, aber mit "lediglich über
Excelgrundkenntnisse" meine ich genau das und nicht mehr. Also keine
Kenntnisse von Datenbankprogrammen (vorgeschlagene Accessvariante) und
keine VBA-Kenntnisse. Meine Kollegen sind reine "Exceloberflächenanwender".
Deshalb hatte ich so formuliert:
> Hat jemand einen Tip für mich, welches Excel-Verfahren/welche
> Excel-Vorgehensweise ich meinen Kollegen empfehlen könnte, das/die am
> wenigsten Zeit verschlingt?
Ich kenne mich im Bereich solch großer Datenmengen nicht sonderlich mit den
hauseigenen Excelmöglichkeiten aus, weil ich immer sehr schnell auf
Datenbanklösungen komme, aber es gibt ja auch in Excel das eine oder andere
Werkzeug. Irgendwas mit Filtern? Pivottabellen? Oder sonstwas?
Mich dünkt, ich suche nach der Quadratur des Kreises und die ist ja
bekanntermaßen schwierig...
Aber noch einmal vielen Dank für den von Euch für mich produzierten
"Gehirnschmalz" und
viele Grüße aus dem hohen flachen Norden Deutschlands
Susanne
Klaus "Perry" Pago
mit Excel sind wir hier im Grenzbereich.
Hab' 'mal 36.000 Adressen mit 20.000 Adressen verglichen
8 Minuten - Ergebnisblatt <-- Verzeichnis1 X Verzeichnis2
4 Minuten - Vergleich auf dem selben Tabellenblatt
40 Sekunden - bei Verwendung von Daten im Zahlenformat (Primärschlüssel)
Die gleichen Zahlen (Primärschlüssel) im Textformat dauern 4 Minuten
Vielleicht kann man mit diesen Überlegungen etwas "Performance" gewinnen
Gruß
Klaus
"Susanne Wenzel" <wirdnich...@onlinehome.de> schrieb im Newsbeitrag
news:14koe2pbr7bj1$.kxvx7ecx2jrf.dlg@40tude.net...
Thomas Ramel
Susanne Wenzel schrieb am 25.02.2006
> ...vielleicht kam das hier nicht deutlich heraus, aber mit "lediglich über
> Excelgrundkenntnisse" meine ich genau das und nicht mehr. Also keine
> Kenntnisse von Datenbankprogrammen (vorgeschlagene Accessvariante) und
> keine VBA-Kenntnisse. Meine Kollegen sind reine "Exceloberflächenanwender".
>
> Deshalb hatte ich so formuliert:
>
>> Hat jemand einen Tip für mich, welches Excel-Verfahren/welche
>> Excel-Vorgehensweise ich meinen Kollegen empfehlen könnte, das/die am
>> wenigsten Zeit verschlingt?
>
> Ich kenne mich im Bereich solch großer Datenmengen nicht sonderlich mit den
> hauseigenen Excelmöglichkeiten aus, weil ich immer sehr schnell auf
> Datenbanklösungen komme, aber es gibt ja auch in Excel das eine oder andere
> Werkzeug. Irgendwas mit Filtern? Pivottabellen? Oder sonstwas?
Der Spezialfilter kombiniert mit ZÄHLENWENN() könnte eine gangbare
Alternative sein; zeit benötigst Du allerdings dennoch.
Ich habe zum testen folgenden Aufbau gewählt:
Tabelle1:
- A1: 'Schlüssel'
- A2:A60001: Die Zahlen von 1:60000 (Bearbeiten/Ausfpüllen/Reihe)
- B2:B60001: =ZUFALLSZAHL()
- sortiert nach Spalte B
- Tabelle kopieren
- in Tab1 die letzen 10000 Datensätze gelöscht - ebenfalls die Hilfsspalte
Tabelle2 (die kopierte)
- - sortiert nach Spalte B
- in Tab die letzen 20 Datensätze gelöscht - ebenfalls die Hilfsspalte
So hatte ich zwei Tabellen mit unikaten 'Primärschlüsseln', die in einer
unbekannten Anzahl und Reihenfolge in den beiden Tabellen enthalten waren.
Die Spaltenüberschriften beider Spalten müssen identisch sein.
Tabelle3 (für die Auswertung):
- Spalte A aus Tab1 nach A1 kopiert
- Spalte A aus Tab2 nach D1 kopiert
Spezialfilter erster Schritt:
- Zellenmarkierung in Spalte A stellen (die Spalte mit mehr Datensätzen)
- Menü: 'Daten' / Filter / Spezialfilter
- Aktion: [x] An eine andere Stelle kopieren
- Listenbereich: (müsste ausgefüllt sein)
- Kriterienbereich: D1 markieren, dann Strg+Umschalt+Pfeil unten
- Kopieren nach: G1 anklicken
- [OK]
Nach der Filterung (das dauert eine Weile) stehen in Spalt eG alle Daten,
die in beiden Spalten enthalten sind. Bei mir waren das noch gut 25'000
Zeilen.
Vorbereitung für zweiten Schritt:
- B1 und E1: 'Wert' als Spaltenüberschrift einfügen
- B2: '=ZÄHLENWENN($G:$G;A2)' eintragen
- Doppelklick auf das Ausfüllkästchen unten rechts an der Zelle
- ...Kaffee trinken... ;-)
- Rechtsklick
- Kopieren
- Rechtsklick
- Inhalte einfügen
- [x] Werte
- [OK]
- D2: '=ZÄHLENWENN($G:$G;D2)' eintragen
- Doppelklick auf das Ausfüllkästchen unten rechts an der Zelle
- ...Kaffee trinken... ;-)
- Rechtsklick
- Kopieren
- Rechtsklick
- Inhalte einfügen
- [x] Werte
- [OK]
- in Zeile 1 drei neue, leere Zeilen einfügen
- Zeile 4 nach Zeile 1 kopieren
- B2 und E2: jeweils '0' eintragen als Filterkriterium
Spezialfilter zweiter Schritt:
- Menü: 'Daten' / Filter / Spezialfilter
- Aktion: [x] An eine andere Stelle kopieren
- Listenbereich: A4 markieren
dann Umschlat+Pfeil rechts und Strg+Umschalt+Pfeil unten
- Kriterienbereich: A1:B2 markieren
- Kopieren nach: I4 anklicken
- [OK]
Spezialfilter dritterer Schritt:
- Menü: 'Daten' / Filter / Spezialfilter
- Aktion: [x] An eine andere Stelle kopieren
- Listenbereich: D4 markieren
dann Umschlat+Pfeil rechts und Strg+Umschalt+Pfeil unten
- Kriterienbereich: A1:B2 markieren (müsste noch drin sein)
- Kopieren nach: K4 anklicken
- [OK]
Nun kannst Du die Spalten J und L löschen und hast folgende Werte in den
Spalten:
G: alle die in beiden Spalten enthalten sind
H: alle die nur in Spalte A enthalten sind
I: alle die nur in Spalte D enthalten sind
Ich denke, dass dies für 'Handarbeit' ohne VBA ein durchaus gangbarere Weg
ist - eine Step-by-Step Anleitung ist ja schnell erstellt ;-)
Mit freundlichen Grüssen (aus dem erneut verschneiten kleine Land im Süden)
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]
Microsoft Excel - Die ExpertenTipps:
(http://tinyurl.com/9ov3l und http://tinyurl.com/cmned)
Susanne Wenzel
Am Sat, 25 Feb 2006 22:05:17 +0100 schrieb Klaus "Perry" Pago:
> mit Excel sind wir hier im Grenzbereich.
> Hab' 'mal 36.000 Adressen mit 20.000 Adressen verglichen
> 8 Minuten - Ergebnisblatt <-- Verzeichnis1 X Verzeichnis2
> 4 Minuten - Vergleich auf dem selben Tabellenblatt
> 40 Sekunden - bei Verwendung von Daten im Zahlenformat (Primärschlüssel)
> Die gleichen Zahlen (Primärschlüssel) im Textformat dauern 4 Minuten
vielen Dank für die Mitteilung über Deine Erfahrungswerte, die finde ich
sehr interessant.
Viele Grüße aus dem hohen flachen Norden Deutschlands
Susanne
Susanne Wenzel
Am Sun, 26 Feb 2006 10:40:42 +0100 schrieb Thomas Ramel:
> Susanne Wenzel schrieb am 25.02.2006
>
[Erklärungen und wunderschöne Anleitung gesnipt]
> Ich denke, dass dies für 'Handarbeit' ohne VBA ein durchaus gangbarere Weg
> ist - eine Step-by-Step Anleitung ist ja schnell erstellt ;-)
vielen Dank, das könnte etwas für meine Kollegen sein und bei Deiner
Variante trinkt man bestimmt weniger Kaffee als bei meiner...:-)
Nebeneffekt ist auch noch, dass ich mich ein bisschen mit den
Filtergeschichten auseinandersetzen muss, was aber bestimmt nicht schaden
kann (immerhin habe ich mich auch ein bisschen mit Pivottabellen anfreunden
können).
> Mit freundlichen Grüssen (aus dem erneut verschneiten kleine Land im Süden)
:-)
Viele Grüße aus dem Bundesland mit der Kieler Woche (...;-))
Susanne
P.S.: Noch eine kleine Info bzgl. Skript NonAnsweredPosts. Thomas hat seine
Arbeit daran erst mal auf Eis gelegt, war im Verhältnis zu aufwendig, weil
zu viele Vorbedingungen erfüllt sein mussten. Vielleicht klappt es mit der
nächsten Betaversion (wann auch immer die ausgeliefert wird) .
Thomas Ramel
Susanne Wenzel schrieb am 26.02.2006
> Hallöle Thomas;-),
Helau! heisst das ja im Moment eher ;-)
> Am Sun, 26 Feb 2006 10:40:42 +0100 schrieb Thomas Ramel:
>> Susanne Wenzel schrieb am 25.02.2006
>
> [Erklärungen und wunderschöne Anleitung gesnipt]
>
>> Ich denke, dass dies für 'Handarbeit' ohne VBA ein durchaus gangbarere Weg
>> ist - eine Step-by-Step Anleitung ist ja schnell erstellt ;-)
>
> vielen Dank, das könnte etwas für meine Kollegen sein und bei Deiner
> Variante trinkt man bestimmt weniger Kaffee als bei meiner...:-)
Keine Ahnung, aber IMO ist ZÄHLENWENN() eine sehr flinke Funktion, auch
wenns bei 50'000 Zeilen halt eine Weile dauert - vielleicht reicht ja die
Tasse dann gleich für beide Schritte :-)
> Nebeneffekt ist auch noch, dass ich mich ein bisschen mit den
> Filtergeschichten auseinandersetzen muss, was aber bestimmt nicht schaden
> kann (immerhin habe ich mich auch ein bisschen mit Pivottabellen anfreunden
> können).
Ja, der Nutzen der NG ist schon sehr hoch - es gibt viele Dinge die ich
heute über Excel weiss, über die ich mir ohne NG kaum Gedanken gemacht
hätte.
>> Mit freundlichen Grüssen (aus dem erneut verschneiten kleine Land im Süden)
>
> :-)
>
> Viele Grüße aus dem Bundesland mit der Kieler Woche (...;-))
Ich habs bis dahin leider nicht geschafft so weit gen Norden zu fahren,
obschon ich im Januar im Raume Aachen war.
Aber irgendwann wird das schon noch werden.
> P.S.: Noch eine kleine Info bzgl. Skript NonAnsweredPosts. Thomas hat seine
> Arbeit daran erst mal auf Eis gelegt, war im Verhältnis zu aufwendig, weil
> zu viele Vorbedingungen erfüllt sein mussten. Vielleicht klappt es mit der
> nächsten Betaversion (wann auch immer die ausgeliefert wird) .
Danke für die Info - inzwischen ist das Aufkommen unbeantworteter Postings
ja auch nicht mehr so hoch; da komme ich mit der ?Hand'-Methode ganz gut
hin.
Aber ja, auf eine neue Beta bin ich auch gespannt.
Mit freundlichen Grüssen (hier ist inzwischen wieder Tauwetter)
frankarendt-theilen
wie wäre es mit der bedingten Formatierung. Die Rechenzeit liegt dann
unter 1 Sekunde um festzustellen welche Datensätze in beiden Tabellen
den gleichen Primärschlüssel haben und welche Datensätze nur in der
einen bzw. in der anderen Tabelle vorhanden sind.
Ich habe die Testtabellen nach der Anleitung von Thomas erstellt und
noch zusätzlich beliebige Datensätze gelöscht.
1. Schritt
Namen vergeben, z.B. Primärschlüsselbereich TabelleA ---> BereichA,
Primärschlüsselbereich TabelleB ---> BereichB und als zusätzlichen
Namen über Einfügen/Namen/Definieren:
Name: Zelle
"Bezieht sich auf": INDIREKT("ZS()";FALSCH)
2. Schritt
Bedingte Formatierung einbringen Für TabelleA, die
Primärschlüsselzellen des TabelleblattesA sind markiert:
Formel ist: =ZÄHLENWENN(BereichB;Zelle)>0
Formatierung auswählen
OK
3. Schritt
Bedingte Formatierung einbringen Für TabelleB, die
Primärschlüsselzellen des TabelleblattesA sind markiert:
Formel ist: =ZÄHLENWENN(BereichA;Zelle)>0
Formatierung auswählen
OK
Hierauf werden alle Zellen farblich markiert, die in beiden
Tabellenblättern den gleichen Primärschüssel haben.
Gleichzeitig kann herausgelesen werden, welche Primärschlüssel nur in
dem jeweiligen Tabellenblatt vorhanden sind.
MfG Frank
_________________________________________________
Frank Arendt-Theilen, Microsoft MVP für Excel, Hameln
Microsoft Excel - Die ExpertenTipps http://tinyurl.com/cmned
Website: http://www.xl-faq.de
# Auftragsprojekte#
Hans
Vergleichsergebnis, dann hättest Du noch einen Versuch frei:
Lade beide Excel-Dateien in den Speicher. Sortiere beide nach dem
Primärschlüssel.
Ändere Deine VBA-Routine so, daß Du von 1 bis zum Ende der größeren
Tabelle durchläufst und vergleiche die Einträge von A und B
paarweise. Wenn A kleiner ist, gibst Du A nach "nur A" aus, wenn B
kleiner ist, gibst Du B nach "nur B" aus, und wenn beide gleich sind
nach "A=B".
Ich weiß allerdings nicht, wie lange das läuft, aber sollte
eigentlioch nicht so dramatisch sein wie 2 verschachtelte Schleifen.
:-)
Hans
Susanne Wenzel
auch Dir ein etwas verspätetes Feedback, nachdem ich vier Tage nicht online
war und meinen Hamster nur mit energischen Mitteln dazu überreden konnte,
ältere Artikel nachzuladen...aaarrrggghh
Am Sun, 26 Feb 2006 18:21:17 +0100 schrieb Frank Arendt-Theilen:
> Hallo Susanne,
> wie wäre es mit der bedingten Formatierung. Die Rechenzeit liegt dann
> unter 1 Sekunde um festzustellen welche Datensätze in beiden Tabellen
> den gleichen Primärschlüssel haben und welche Datensätze nur in der
> einen bzw. in der anderen Tabelle vorhanden sind.
An die bedingte Formatierung hatte ich gar nicht gedacht, auch noch mal ein
Ansatz.
>
> Ich habe die Testtabellen nach der Anleitung von Thomas erstellt und
> noch zusätzlich beliebige Datensätze gelöscht.
>
>
> 1. Schritt
> Namen vergeben, z.B. Primärschlüsselbereich TabelleA ---> BereichA,
> Primärschlüsselbereich TabelleB ---> BereichB und als zusätzlichen
> Namen über Einfügen/Namen/Definieren:
> Name: Zelle
> "Bezieht sich auf": INDIREKT("ZS()";FALSCH)
>
Und nach Thomas mit seinen Filtern bringst Du mich zum Themengebiet Namen
und Indirekt, mit denen ich bisher gar nicht bzw. nur selten gearbeitet
habe...:-)
> 2. Schritt
> Bedingte Formatierung einbringen Für TabelleA, die
> Primärschlüsselzellen des TabelleblattesA sind markiert:
> Formel ist: =ZÄHLENWENN(BereichB;Zelle)>0
> Formatierung auswählen
> OK
>
> 3. Schritt
> Bedingte Formatierung einbringen Für TabelleB, die
> Primärschlüsselzellen des TabelleblattesA sind markiert:
> Formel ist: =ZÄHLENWENN(BereichA;Zelle)>0
> Formatierung auswählen
> OK
>
>
> Hierauf werden alle Zellen farblich markiert, die in beiden
> Tabellenblättern den gleichen Primärschüssel haben.
> Gleichzeitig kann herausgelesen werden, welche Primärschlüssel nur in
> dem jeweiligen Tabellenblatt vorhanden sind.
>
Aber sehr interessant, werde ich bei Gelegenheit ebenfalls ausprobieren.
Vielen Dank für Deine Mühen und
viele Grüße aus dem hohen flachen Norden Deutschlands, wo es zur Zeit
schneit wie verrückt (auch die sonnigen Strände Brasiliens und Kaliforniens
machen da leider keine Ausnahme)
Susanne
Susanne Wenzel
Am 27 Feb 2006 00:14:09 -0800 schrieb Hans:
> Wenn Du dennoch eine separate Tabelle brauchst mit dem
> Vergleichsergebnis, dann hättest Du noch einen Versuch frei:
>
> Lade beide Excel-Dateien in den Speicher. Sortiere beide nach dem
> Primärschlüssel.
>
> Ändere Deine VBA-Routine so, daß Du von 1 bis zum Ende der größeren
> Tabelle durchläufst und vergleiche die Einträge von A und B
> paarweise. Wenn A kleiner ist, gibst Du A nach "nur A" aus, wenn B
> kleiner ist, gibst Du B nach "nur B" aus, und wenn beide gleich sind
> nach "A=B".
noch eine Idee, wunderbar, ich liebe die Excel-NG:-)
Allerdings hätte ich das Problem, dass ich auf diese Art und Weise nicht
die Einträge in B erwische, die es in A gar nicht gibt. Aber vielleicht
lässt sich daran feilen...
Auch an Dich einen lieben Dank und
viele Grüße aus dem hohen flachen verschneiten Norden Deutschlands
Susanne
Thomas Barghahn
> User-Agent: 40tude_Dialog/2.0.15.1de Hamster/2.0.0.0
^^^^^^^^^^^^^^^
Na da schau her! ;-))
Die aktuelle "stabile" Version ist übrigens 2.1.0.0.
*Sorry* für diesen OT-Artikel.
Freundliche Grüße
Thomas Barghahn
--
+++ +++ +++ +++ +++ +++ +++ +++ +++ +++ +++ +++ +++ +++
Mit einem Killfile versaeumt man saemtliche Highlights.
(Jakob Krieger in de.alt.ufo)
Hans
tut mir leid. Ich habe erst jetzt Deine Antwort gelesen.
Mit dem paarweisen Abgleich erwischt Du ganz sicher ALLE Kombinationen.
Hans
Hier einige Beispiele:
A B nur A beide nur B
Spalte B länger
1 2 1 3 2
3 3 5 4
5 4 7 8
7 8 9 10
9 10 12
12 14
14
Spalte A länger
1 2 1 3 2
3 3 5 12 4
5 4 7 8
7 8 9 10
9 10 15 14
12 12 19
15 14 21
19
21
VBA Code
numrowsa = Cells(Rows.Count, "a").End(xlUp).Row ' determine the
number of rows
numrowsb = Cells(Rows.Count, "b").End(xlUp).Row ' determine the
number of rows
cnta = 1
cntb = 1
lna = 1
lnb = 1
lng = 1
testnext:
If cnta <= numrowsa Then
If cntb <= numrowsb Then
If Cells(cnta, 1).Value < Cells(cntb, 2).Value Then
Cells(lna, 4).Value = Cells(cnta, 1).Value
cnta = cnta + 1
lna = lna + 1
Else
If Cells(cnta, 1).Value > Cells(cntb, 2).Value Then
Cells(lnb, 6).Value = Cells(cntb, 2).Value
cntb = cntb + 1
lnb = lnb + 1
Else
Cells(lng, 5).Value = Cells(cnta, 1).Value
cnta = cnta + 1
lng = lng + 1
cntb = cntb + 1
End If
End If
Else
Cells(lna, 4).Value = Cells(cnta, 1).Value
cnta = cnta + 1
lna = lna + 1
End If
Else
If cntb <= numrowsb Then
Cells(lnb, 6).Value = Cells(cntb, 2).Value
cntb = cntb + 1
lnb = lnb + 1
Else
GoTo endproc
End If
End If
GoTo testnext
endproc:
Susanne Wenzel
Am 4 Mar 2006 08:38:34 -0800 schrieb Hans:
> Mit dem paarweisen Abgleich erwischt Du ganz sicher ALLE Kombinationen.
[Darstellung gesnipt]
habe mir Deine Variante noch einmal zu Gemüte geführt, Du hast recht. Ob
das was für meine Excel-"Experten" ist, muss ich mir aber noch mal durch
den Kopf gehen lassen...:-)
Danke Dir
Viele Grüße aus dem hohen flachen Norden Deutschlands
Susanne
Susanne Wenzel
wo treibst Du Dich denn rum?
Hast wohl nix zu tun...:-)
Am Sat, 4 Mar 2006 11:09:03 +0100 schrieb Thomas Barghahn:
>> User-Agent: 40tude_Dialog/2.0.15.1de Hamster/2.0.0.0
> ^^^^^^^^^^^^^^^
> Na da schau her! ;-))
Was genau willst Du mir damit sagen?:-)
(BTW sieht die Unterstreichung schon gleich bei der ersten Zitierebene
ziemlich grauslich aus oder habe ich mal wieder irgend etwas falsch
eingestellt?)
> Die aktuelle "stabile" Version ist übrigens 2.1.0.0.
Ist an mir vorbeigegangen, aber wie sollte man das als Normalsterblicher
auch mitkriegen.
Hmm, was mir gerade auffällt, meinst Du Dialog oder den Hamster?
>
> *Sorry* für diesen OT-Artikel.
>
Keine Ursache, ich habe den Thread jetzt mal als solchen gekennzeichnet...
Viele Grüße aus dem hohen flachen Norden Deutschlands
Susanne
Thomas Barghahn
> Am Sat, 4 Mar 2006 11:09:03 +0100 schrieb Thomas Barghahn:
>>> User-Agent: 40tude_Dialog/2.0.15.1de Hamster/2.0.0.0
>> Na da schau her! ;-))
> Was genau willst Du mir damit sagen?:-)
Nun ja, dass du eben ein neues(?) Haustier hast. ;-)
> (BTW sieht die Unterstreichung schon gleich bei der ersten Zitierebene
> ziemlich grauslich aus oder habe ich mal wieder irgend etwas falsch
> eingestellt?)
Stell' einfach "feste Schriftbreite" als Standard ein. ;-) Dieses hilft
dann auch in vielen anderen Situationen - gerade auch hier in der
Excel-Gruppe. Code-Schnippsel lassen sich mit dieser Einstellung
hervorragend lesen. :-)
>> Die aktuelle "stabile" Version ist übrigens 2.1.0.0.
> Hmm, was mir gerade auffällt, meinst Du Dialog oder den Hamster?
Den Hamster, vor welchem du ja /soo viel/ Angst hattest. ;-)
>> *Sorry* für diesen OT-Artikel.
> Keine Ursache, ich habe den Thread jetzt mal als solchen gekennzeichnet...
Vielen Dank und ...
Freundliche Grüße
Thomas Barghahn
--
Zu CP/M- und DOS-Zeiten waren die Anwendungen
noch recht klein und überschaubar; die Hardware
dagegen war relativ groß und unhandlich.
Heute ist alles irgendwie anders.
Susanne Wenzel
es nützt alles nix, das kann ich so nicht im Usenet stehen lassen:-)
Am Sun, 5 Mar 2006 19:33:37 +0100 schrieb Thomas Barghahn:
>> Am Sat, 4 Mar 2006 11:09:03 +0100 schrieb Thomas Barghahn:
>
>>>> User-Agent: 40tude_Dialog/2.0.15.1de Hamster/2.0.0.0
>>> Na da schau her! ;-))
>> Was genau willst Du mir damit sagen?:-)
Jetzt habe ich das mit fester Schriftbreite ausprobiert und tatsächlich, Du
hattest den Hamster unterstrichen... (und ich war fest davon überzeugt,
dass 40tude gemeint war...)
>
> Nun ja, dass du eben ein neues(?) Haustier hast. ;-)
>
Habe ich nicht, der ist bei mir, seit ich diesen Rechner habe:-)
> Stell' einfach "feste Schriftbreite" als Standard ein. ;-) Dieses hilft
> dann auch in vielen anderen Situationen - gerade auch hier in der
> Excel-Gruppe. Code-Schnippsel lassen sich mit dieser Einstellung
> hervorragend lesen. :-)
Ich habe die feste Schriftbreite aber gleich wieder abgestellt, sieht ja
zum Weglaufen aus, ähnelt dem Kammquoting von OjE *schüttel*.
Aber bestimmt kann man auch da wieder an irgendeinem Schräubchen drehen
(wenn ich doch bloß nicht Stunden bräuchte, um das entsprechende auch zu
finden, seufz).
>>> Die aktuelle "stabile" Version ist übrigens 2.1.0.0.
>> Hmm, was mir gerade auffällt, meinst Du Dialog oder den Hamster?
>
> Den Hamster, vor welchem du ja /soo viel/ Angst hattest. ;-)
;-)))
Nicht hattest, hast, jedenfalls wenn es um eine Neuinstallation samt
Erstkonfiguration geht. Aber dieses Thema hatten wir doch schon mal...
Mein Hamster hier dreht übrigens für mich zufriedenstellend am Rad, da muss
nix geändert werden.:-)
Oh Mann, mir fällt schon wieder was auf, die Zitierebenen in Dialog kommen
durcheinander oder kann ich nicht richtig gucken oder ist das normal?
Da das jetzt völlig Richtung OT abdriftet, wie wäre es mit Weitermachen per
PM?
LG
Susanne