Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1156to1160
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Abstimm. zweier Tabellen mit spor. versetzten Zell

Abstimm. zweier Tabellen mit spor. versetzten Zell
Martin
Hallo!
Ich habe folgendes Problem:
Zwei in einem Blatt zusammenkopierte Tabellen müssen abgestimmt werden, d.h. die Werte in Spalte D und E müssen in der jeweiligen Zeile die gleichen sein. Hier kommt es aber sporadisch zu einem Versatz von +-1 Spalte und dass sollte mittels Formel erkannt werden. Die Tabelle hat ca. 68.000 Zeilen und muss jede Woche neu abgestimmt werden.
1. Versuch war: =WENN(D1=E1;"OK";"FALSCH") nach dem ersten Versatz gaben aber alle folgenden logischer Weise einen Fehler aus.
Ich glaub ich habe da irgendwie ein Brett vorm Kopp. :o(
Beispiel 1: Siehe Spalte D und E, Zeile 4:
A B C D E F G
1 8885730100 tzt 32,84 119085 119085 -32,84 richtig
2 8880204000 tzt 32,84 119460 119460 -32,84 richtig
3 8884695000 tzt 32,84 119890 119890 -32,84 richtig
4 8884695000 tzt 32,84 129495 128605 -32,84 FALSCH
5 8884695000 tzt 32,84 129541 129495 -32,84 FALSCH
6 8884695000 tzt 32,84 129728 129541 -32,84 FALSCH
Ein weiteres Problem ist, die Erkennung der Differenz, also auf welcher Seite lag der Versatz in Spalte D oder E?
Es geht nämlich auch so herum:
Beispiel 2: Siehe Spalte D und E, Zeile 4:
A B C D E F G
1 8885730100 tzt 32,84 119085 119085 -32,84 richtig
2 8880204000 tzt 32,84 119460 119460 -32,84 richtig
3 8884695000 tzt 32,84 119890 119890 -32,84 richtig
4 8884695000 tzt 32,84 128605 129495 -32,84 FALSCH
5 8884695000 tzt 32,84 129495 129541 -32,84 FALSCH
6 8884695000 tzt 32,84 129541 129728 -32,84 FALSCH
Kann mir irgendjemand auf die Sprünge helfen?
Vielen, vielen Dank im voraus!!!!
MfG
Martin
AW: Abstimm. zweier Tabellen mit spor. versetzten Zell
27.05.2010 12:57:41
Oberschlumpf
Hi Martin
Mit Hilfe einer Bsp(xls)-Datei, anstelle der nur hier dargestellten Werte könnte dein Problem vllt besser verstanden werden.
Ciao
Thorsten
AW: Abstimm. zweier Tabellen mit spor. versetzten Zell
27.05.2010 14:47:06
Martin
Hallo,
natürlich ich musste mich erst ein wenig mit diesem Forum beschäftigen. Nun gibt es aber auch eine Bsp. Datei:
https://www.herber.de/bbs/user/69766.xlsx
ich weiss nich weiter...
27.05.2010 14:56:29
Oberschlumpf
...habe nach ner xls-Datei gefragt, erhalte aber eine xlsx-Datei...hmmmm?
Hi Martin
Ich hab kein XL2007, kann mit deiner Datei nix anfangen, und nun hab ich eh keine Zeit mehr.
Ciao
Thorsten
Anzeige
AW: Abstimm. zweier Tabellen mit spor. versetzten Zell
27.05.2010 13:09:50
Rudi
Hallo,
einfach mit ODER?
1. Datensatz in Zeile2!!!
=Wenn(Oder(E2=D1;E2=D2;E2=D3);"OK";"Falsch")
Gruß
Rudi
AW: Abstimm. zweier Tabellen mit spor. versetzten Zell
27.05.2010 14:50:12
Martin
Vielen Dank! Sieht schon fast richtig aus. Ich habe deine Formel gleich mal eingebaut, nun taucht ein weiteres Problem auf, es können auch +-2Zeilen unterschied sein so wie ich gerade gesehen habe:
https://www.herber.de/bbs/user/69766.xlsx
Ich versuche einer Freundin damit zu helfen, ich bearbeite diese Datei normalerweise nicht selber, deswegen war mir der 2.Versatz vorher nicht bekannt. Sorry!!
Was ist denn das Ziel?
27.05.2010 15:19:25
Rudi
Hallo,
willst du nur wissen, ob ein Wert in beiden Spalten vorkommt?
=wenn(zählenwenn(d:d;e2)>0;"OK";"Falsch") und runter kopieren.
Gruß
Rudi
Anzeige
AW: Was ist denn das Ziel?
27.05.2010 15:49:20
Martin
Nein, leider nicht. Es ist etwas "defiziler"...
Es geht wirklich um eine Abstimmung, also zwei Parteien liefern Ihre Daten zu einem Gemeinschaftsprojekt, und diese Daten müssen 1:1 stimmen. Leider gibt es entw. auf der einen oder anderen Seite Fehler, also fehlende oder falsche Posten (ID1/ID2).
Dadurch entsteht dieser Versatz zwischen den ID´s also mal rutscht eine falsche ID in Spalte D und mal in E. Eigentlich sind die Arbeitsschritte (bislang manuell):
1. Erkennen des Versatzes, also des Fehlers. (Das macht deine Formel schon ganz gut :-))
2. Den Versatz ausgleichen, d.h. wenn fehlerhafte ID in E5 dann wird in D5 eine Zelle hinzugefügt (Zellen nach unten verschieben) damit dieser Versatz ausgeglichen wird und dass für jeden einzelnen Versatz in den 68.000 Zeilen. :-(
3. Wenn neben der falschen ID keine Beträge stehen (Spalten C und F) dann kann diese Zeile gelöscht werden.
Das wars im großen und ganzen...
Etwas kompliziert leider. Vermutlich komme ich deswegen auch nicht mehr weiter.
Anzeige
AW: Was ist denn das Ziel?
27.05.2010 16:40:13
Martin
Bin zumindest bei der Prüfung voran gekommen:
Noch ein etwas anderer Ansatz:
G11=UND(WENNFEHLER(SVERWEIS(E11;$D:$D;1;FALSCH);"Nicht vorhanden ID1");WENNFEHLER(SVERWEIS(D11;$E:$E;1;FALSCH);"Nicht vorhanden ID2"))
Es kommt aber kein "Nicht vorhanden"-Text sondern nur WAHR oder FALSCH als Ergebnis! Kann man das noch auf die vorgegebenen Antworttexte ändern?
AW: Was ist denn das Ziel?
27.05.2010 16:41:16
Martin
Bin zumindest bei der Prüfung voran gekommen:
Noch ein etwas anderer Ansatz:
G11=UND(WENNFEHLER(SVERWEIS(E11;$D:$D;1;FALSCH);"Nicht vorhanden ID1");WENNFEHLER(SVERWEIS(D11;$E:$E;1;FALSCH);"Nicht vorhanden ID2"))
Es kommt aber kein "Nicht vorhanden"-Text sondern nur WAHR oder #WERT als Ergebnis! Kann man das noch auf die vorgegebenen Antworttexte ändern?
Anzeige
AW: Abstimm. zweier Tabellen mit spor. versetzten Zell
27.05.2010 19:39:11
Uduuh
Hallo,
check das mal:
Sub Prüfung()
Dim oDict As Object, arrDaten(), vntTmp, i As Long, rngC As Range
Set oDict = CreateObject("Scripting.Dictionary")
For Each rngC In Range("D:E").SpecialCells(xlCellTypeConstants)
If rngC.Row  1 Then
If Not oDict.exists(rngC.Value) Then oDict.Add rngC.Value, ""
End If
Next
arrDaten = Application.Transpose(oDict.keys)
ReDim Preserve arrDaten(1 To oDict.Count, 1 To 3)
For i = 1 To oDict.Count
vntTmp = Application.Match(arrDaten(i, 1), Columns(4), 0)
If Not IsError(vntTmp) Then arrDaten(i, 2) = Cells(vntTmp, 3)
vntTmp = Application.Match(arrDaten(i, 1), Columns(5), 0)
If Not IsError(vntTmp) Then arrDaten(i, 3) = Cells(vntTmp, 3)
Next
With Worksheets.Add
.Cells(1, 1) = "ID"
.Cells(1, 2) = "Betrag1"
.Cells(1, 3) = "Betrag2"
.Cells(2, 1).Resize(oDict.Count, 3) = arrDaten
End With
End Sub

Gruß aus’m Pott
Udo

Anzeige
AW: Abstimm. zweier Tabellen mit spor. versetzten Zell
28.05.2010 08:36:52
Martin
Hi Udo!
Absolut Klasse Idee!
Nur eine Verständnisfrage:
Er gibt die Beträge 1 und 2 zwar gesondert aus, aber wenn ich in der "Quelltabelle" in der Betrag1 Spalte überall z.B. 55 eintrage und in der Spalte Betrag2 alles auf -32,84 lasse, dann steht in dem neuen Blatt in beiden Betragsspalten 55, sollte da nicht der jeweilige Betrag stehen?
Viele Grüße und bereits jetzt VIELEN DANK!!!! !
Martin
Festwerte
28.05.2010 10:36:21
Rudi
Hallo,
wie ich das sehe, werden Festwerte in die neue Tabelle eingetragen. Du musst das Makro also nochmal von der Quelltabelle aus starten.
Gruß
Rudi
AW: Festwerte
28.05.2010 11:54:16
Martin
Hallo Rudi,
ich habe die absolute Quelldatei (also die mit den ID1/ID2 in Spalte D/E) nochmal mit dem Code gefüttert und den Makro ausgeführt. Leider sind beide Betragsspalten immernoch mit den selben Inhalten gefüllt, ausser siehe B14 dort ist der fehlende Wert von Betrag2 (Quelldatei) in Betrag1 (Prüfung) "gewandert" das ist eigentlich falsch so.
Hier die neue Beispielsdatei (Excel 2007):
https://www.herber.de/bbs/user/69784.xlsm
Vielen Dank und viele Grüße!!!
Martin
Anzeige
AW: Festwerte
28.05.2010 12:06:41
Martin
Alles Klar! Ich hatte etwas übersehen, es scheint doch korrekt zu sein. Habe nochmal einen neuen Testlauf gemacht, mir war nur die Verwendung der Spalten Betrag1 und Betrag2 nicht klar, der Wert der zur jeweiligen ID gehört ist Betrag1 der andere Wert ist dann immer Betrag2. Oder?
AW: Festwerte
28.05.2010 12:26:13
Martin
Hmm... hab nochmal mit meiner Freundin gesprochen:
Die Funktionsweise ist schon echt klasse, aber das erkannte Problem mit den Beträgen bleibt bestehen.
Also Quelldatei Spalte C-Betrag1 muss auch dem Betrag1 (am besten mit Vorzeichen -) in der Prüftabelle entsprechen, bei Betrag2 natürlich genauso. Es muss zur Abstimmung nachher aus den beiden Betragsspalten die "Differenzzeilen" ausgewiesen werden. :o(
@Udo: Magst du evtl. nochmal schauen? :o)
Danke!!!
Anzeige
AW: Festwerte
28.05.2010 13:37:13
Rudi
Hallo,
das funktioniert so:
Es wird eine Liste aller IDs aus D:E erstellt, in der diese einmalig vorkommen.
Anschließend wird die Liste nochmal durchgegangen. Existiert die jeweilige ID in D, wird der Betrag aus C nach Betrag1 geschrieben. Gibt es sie in E, wird der Betrag aus F in Betrag2 geschrieben. Gibt es sie in beiden Spalten, sind Betrag1 und Betrag2 gefüllt. Ist also Betrag1 leer, existiert die ID nicht in D. Analog Betrag2.
Voraussetzung ist, dass die IDs in D und E nur 1x vorkommen.
Gruß
Rudi
AW: Festwerte
28.05.2010 13:59:22
Martin
Hallo Rudi,
erstmal vielen Dank dass du dich damit so ausführlich beschäftigst. Finde ich wirklich Klasse!!!
Deiner Ausführung kann ich soweit folgen und die Ergebnisse sind was die "Positiven" Ergebnisse angeht auch korrekt, aber wenn mal eine ID nur einmal vorhanden ist (wie in meinem aktuellem Beispiel in E13, ID2 128605) Betrag2=0 dann zieht er bei Betrag2 in der Prüftabelle den Wert aus C (32,84) und das darf nicht. Wenn eine ID alleine steht in D/E dann darf auch nur deren "eigener" Betrag gezogen werden.
Ich würde wenn das funktionieren würde, einen Abgleich in der Prüftabelle laufen lassen, der mir auch noch ausgibt auf wessen Seite (ID1 oder ID2) die fehlerhafte bzw. alleinstehende ID stand. Es ist für die Differenzenklärung wichtig zu wissen auf wessen Seite der Fehler war.
Oh mann, Ihr habt mir echt schon SUPER geholfen, vielleicht bekommen wir das kleine "Projekt" ja noch richtig zum laufen.
Gruß
Martin aus Oldenburg
Anzeige
Dein Beispiel
28.05.2010 14:32:00
Rudi
Hallo,
mit XL2007 kann ich nichts anfangen.
Gruß
Rudi
überarbeitet
28.05.2010 16:21:38
Uduuh
Hallo,
das müsste passen
Sub Prüfung()
Dim oDict As Object, arrDaten(), i As Long, rngC As Range, arrItems, arrKeys, arrTmp
Set oDict = CreateObject("Scripting.Dictionary")
Dim Item1, Item2
For Each rngC In Range("D:E").SpecialCells(xlCellTypeConstants)
If rngC.Row  1 Then
If Not oDict.exists(rngC.Value) Then
If Application.CountIf(Columns(4), rngC) = 0 Then
Item1 = ""
Else
Item1 = Application.SumIf(Columns(4), rngC.Value, Columns(3))
End If
If Application.CountIf(Columns(5), rngC) = 0 Then
Item2 = ""
Else
Item2 = Application.SumIf(Columns(5), rngC.Value, Columns(6))
End If
oDict.Add rngC.Value, Item1 & "|" & Item2
End If
End If
Next
ReDim arrDaten(1 To oDict.Count, 1 To 3)
arrItems = Application.Transpose(oDict.items)
arrKeys = Application.Transpose(oDict.keys)
ReDim Preserve arrDaten(1 To oDict.Count, 1 To 3)
For i = 1 To oDict.Count
arrTmp = Split(arrItems(i, 1), "|")
arrDaten(i, 1) = arrTmp(0)
arrDaten(i, 2) = arrKeys(i, 1)
arrDaten(i, 3) = arrTmp(1)
Next
With Worksheets.Add
.Cells(1, 1) = "Betrag C"
.Cells(1, 2) = "ID"
.Cells(1, 3) = "Betrag F"
.Cells(2, 1).Resize(oDict.Count, 3) = arrDaten
.Cells(1, 1).Sort key1:=.Cells(2, 2), header:=xlYes
End With
End Sub
Gruß
Rudi
Anzeige
AW: überarbeitet
28.05.2010 16:50:41
Martin
Hammer!! Das sieht wirklich sehr gut aus, :o)) werde es Montag ausführlich testen!
Vielen, vielen Dank und erstmal ein schönes Wochenende!!!
klappt's? owT
01.06.2010 09:34:05
Rudi

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige