PDA

Vollständige Version anzeigen : -- VBA Problem mit Datevergleich und -analyse


CCC01
14.03.2009, 16:45
Hallo,

ich stehe vor folgendem Problem, welches für mich als absoluten VBA-newbee schier unlösbar scheint und auf das ich hoffe von erfahrenen Usern etwas Input zu bekommen:


Ausgangssituation:
Ich habe zwei Tabellen im folgenden Tabelle 1 und Tabelle 2 genannt. Tabelle 1 enthält einen Block mit Daten, wobei jeweils eine Zeile einen Datensatz darstellt für den 9 Spaltenfelder der jeweiligen Zeile benötigt werden. Allerdings sind eigentlich nur die Daten dreier aneinanderliegender Spalten von Interesse für mich und zwar die der Spalten G, H und I. Es handelt sich hierbei um numerische Werte.
Desweiteren befinden sich in Tabelle 2 Daten gleicher Formatierung (sprich wieder 9 Spalten pro Zeile), diese werden dann zunächst durch Multiplikation zu zehn Spalten pro Datensatz erweitert (dies wird allerdings manuell geschehen und ist somit nicht weiter von interesse). Aus diesen Daten sind die Spalten J und F von interesse, die ebenso numerische Werte enthalten.
Aufgabe:
Nun soll folgendes passieren, zuerst soll immer verglichen werden, ob die Daten aus Tab 2 Spalte F und J derselben Zeile irgendwo in der selben Kombination in einer Zeile der Tab 1 auftauchen. Wobei der Wert aus Tab 2 Spalte J sowohl mit den Werten aus Tab 1 Spalten H und I verglichen werden soll und der Wert aus Tab 2 Spalte F mit den Werten aus Tab 1 Spalte G. Sofern ein Match vorliegt soll jeweils die Schrift der Ausgangszeile in Tab 2 grün formatiert werden und auch die der jeweiligen Zeile aus Tab 1 und zusätzlich in der Zeile aus Tab 2 welche als Suchbasis genutzt wurde in Spalte K der Zeile eine Ausgabe der Zeilennummer des Fundorts in Tab 1 erfolgen.
In einem weiteren Lauf sollen die Zeilen aus Tab 2 für die kein Matching festgestellt werden konnte erneut gesucht werden ob der Wert aus Tab 2 Spalte J irgendwo in Spalten H und I der Tab 1 aufzufinden ist und wenn ja selbiges Prozedere wie oben beschrieben ablaufen.
Ich fürchte keine all zu einfache Aufgabe, hoffe jedoch eine schaffbare. Vielen vielen Dank schonmal.

P.S.: Die Daten auf beiden Tabellen beginnen jeweils in der dritten Zeile.

Uwe (:o)
14.03.2009, 17:43
Hi,
das hört sich nicht besonders schwierig an.
Allerdings habe ich (und auch wohl viele andere) keine Lust Deine Tabellen nachzubauen und dann anhand Deiner (wenn auch sehr guten) Beschreibung erstmal zu sehen, was erreicht werden soll.
Wenn Du eine kleine Beispieldatei hochlädst, in der Du direkt zeigst (Textfelder/Pfeile) was wo gesucht werden soll und wie es hinterher aussehen soll, wird Dir bestimmt jemand helfen.

CCC01
14.03.2009, 18:18
Vielen Dank für die rasche Antwort, hab mal ein Minimalbeispiel erstellt und dieses an den relevanten Zellen mit Kommentar versehen. Vielen Dank schonmal

Uwe (:o)
14.03.2009, 19:40
Hi,
ok, ich habe mir das mal kurz angeschaut und denke ich werde damit klar kommen, wenn ich mich noch ein bisschen darin vertiefe. Dazu werde ich aber wohl erst morgen kommen. Bis dahin noch zwei Fragen:
Muss die Lösung unbedingt über VBA sein. Evtl.!!! wäre auch eine Formellösung möglich.
Falls Formellösung: Wäre eine ausgeblendete Hilfsspalte ein Problem?

CCC01
14.03.2009, 19:46
Super, ist ja spitze, wenn du dir die Zeit dafür extra nimmst! :top: Also generell wäre mir eine VBA Lösung lieber, allerdings wäre fürs erste mir schon sehr mit irgendeiner Lösung geholfen und ausgeblendete Hilfsspalten würden dabei nicht wirklich ein Problem darstellen. Nur eins noch, würde die nicht-VBA-Lösung damit ein Problem haben, fall die Liste an irgendeiner Stelle durch eine Leerzeile unterbrochen ist und dann weitergeht bzw in den Augen von Excel ein neuer Block beginnt (was IMHO VBA ja ziemlich egal sein dürfte wenn man ihm sagt es soll die komplette Range von Anfang bis zum Ende der Tabelle scannen, oder?)

Uwe (:o)
14.03.2009, 19:53
Hi,
Leerzeilen wäre auch für die Formellösung (falls es die überhaupt gibt) kein Problem. VBA wäre wahrscheinlich einfacher.
Ich schau mir das Morgen mal an. Ist mein Hobby und hält mich (geistig) fit.
Aber vielleicht bekommst Du ja sogar von anderer Seit noch eine Lösung. Lass Dich überraschen.
Bis morgen.

CCC01
15.03.2009, 11:05
Bzgl. einer möglichen VBA Lösung hab ich noch drei Sachen gefunden, die man mit etwas besserer Sachkenntnis als ich sie habe vielleicht als Ausgangspunkt nehmen könnte und durch Modifikation zu einer Lösung meines Problems machen könnte.

Nummer 1: Vergleich zweier Spalten und bei nicht vorhandensein Wert kopieren

Sub vergleich()
Dim i As Long
Dim j As Long
Dim k As String
Dim l As String
j = 1
For i = 1 To Sheets(1).Cells(65536, 1).End(xlUp).Row
k = Sheets(1).Cells(i, 1) & Sheets(1).Cells(i, 2) & Sheets(1).Cells(i, 3)
l = Sheets(2).Cells(i, 1) & Sheets(2).Cells(i, 2) & Sheets(2).Cells(i, 3)
If k = l Then
Sheets(3).Cells(j, 1) = Sheets(1).Cells(i, 1)
Sheets(3).Cells(j, 2) = Sheets(1).Cells(i, 2)
Sheets(3).Cells(j, 3) = Sheets(1).Cells(i, 3)
Sheets(3).Cells(j, 4) = Sheets(1).Cells(i, 4)
j = j + 1
End If
Next i
End Sub

Nummer 2: Vergleich der Daten zweier Spalten und Löschen doppelt vorhandener Datensätze

StandardModule: basMain

Sub DoppelteRaus()
Dim wksA As Worksheet, wksB As Worksheet
Dim rng As Range
Dim iRow As Integer
Set wksA = Worksheets("Tabelle1")
Set wksB = Worksheets("Tabelle2")
iRow = 1
Do Until IsEmpty(wksA.Cells(iRow, 1))
Set rng = wksB.Columns(1).Find(wksA.Cells(iRow, 1))
If Not rng Is Nothing Then
wksA.Rows(iRow).Delete
wksB.Rows(rng.Row).Delete
End If
iRow = iRow + 1
Loop
End Sub


Nummer 3: Es soll geprüft werden, ob die Werte aus Spalte E in Spalte A vorhanden sind. Wenn ja, soll in Spalte F eine SVERWEIS-Formel eingesetzt werden

StandardModule: Modul1

Sub SetVLookup()
Dim vRow As Variant
Dim iRow As Integer
iRow = 2
Do Until IsEmpty(Cells(iRow, 1))
vRow = Application.Match(Cells(iRow, 5).Value, Columns(1), 0)
If Not IsError(vRow) Then
Um die Formel einzutragen
Cells(iRow, 6).Formula = "=VLOOKUP(" & Cells(iRow, 5).Address & ",A:A,0)"
Um Werte einzutragen:
'Cells(iRow, 6).Value = _
WorksheetFunction.VLookup(Cells(iRow, 5).Value, Columns(1), 0)
End If
iRow = iRow + 1
Loop
End Sub

Uwe (:o)
15.03.2009, 13:32
Hi,
ich habe jetzt mal was in VBA zusammengebastel. Ich bin mir nicht sicher, ob ich Dich 100%ig richtig verstanden habe. Schau's Dir mal an und teste es mit ein paar mehr Daten (Der Knopf "Abgleich" startet das Makro). Der Text in Spalte K kann natürlich noch angepasst werden. Schreib mir einfach, was noch nicht so richtig hinhaut, dann seh ich mal was ich machen kann.

CCC01
15.03.2009, 15:54
Hey vielen vielen Dank, denke schon, dass du mich richtig verstanden hast soweit ich es sehe, aber irgendwo hackt es noch, hab mal paar daten hinzugefügt und gereupt und dir die fehlerhaften Zeilen rot makiert. Und ein paar Kommentare hinzugefügt. Aber trotzdem ist schon nah dran. Vielen Dank!

Uwe (:o)
15.03.2009, 16:48
Hi,
formatiere mal die Zelle mit der 10,11 auf 4 Dezimalstellen, dann siehst Du, das da 10,1099 steht. In Tabelle 1 steht genau 10,11!
Ändere mal die Formel in der Zelle auf:
=RUNDEN(H10*0,19+I10*0,19;2)
dann klappt es auch mit dem Makro (;-)

CCC01
15.03.2009, 17:01
Ganz großes Kino!!! Echt spitze :sun: Jetzt hätte ich nur noch ein paar Fragen


Hat es mit den farbigen Feldern in Tabelle 3 eine besondere Bedeutung und werden diese immer benötigt?
Könnte man das Makro noch dahingehend abändern, dass der Benutzer zu Beginn aufgefordert wird durch Markieren zweier Spalten die Targetwerte dem Programm mitzuteilen und danach noch durch Markieren der entsprechenden Spalten welche durchsucht werden sollen. Oder wahrscheinlich besser zuerst Auswahl der Targetspalte der USt und dann die Spalten die Soll und haben Beträge enthalten. Der grund für diesen Wunsch wäre es unabhängig davon zu machen in welcher Spalte nun explizit die jeweiligen Informationen stehen... EDITED: Alternativ wäre es eigentlich auch schon ausreichend wenn der Benutzer aufgefordert wird die Tabelle (durch Eingabe deren Namen) in der sich die jeweiligen Daten befinden anzuzeigen und in folgenden Schritten aufgefordert wird die Nummer der Spalten für die jeweiligen Positionen anzugeben.

Uwe (:o)
15.03.2009, 17:18
Hi,
schon mal zu 1.:
Das hatte ich nur vergessen zu löschen. Ich hatte mir da die Farben für den Colorindex anzeigen lassen (Colorindex=Zeilennummer), um rauszubekommen welchen Index Deine Farbe grün hat (10). Kannst Du "wegschmeißen"
Den Punkt 2 lasse ich mir nochmal durch den Kopft gehen. Die Spalten einzugeben wäre nicht weiter schwierig, aber das mit dem markieren weiß ich noch nicht.

Uwe (:o)
15.03.2009, 17:29
Hi,
ich habe mir Punkt 2 nochmal durch den Kopf gehen lassen. Weiß nicht ob ich das hinbekomme, habe aber auf jeden Fall heute keine Zeit mehr dafür.
Ich würde vorschlagen, Du machts dafür einen neuen Thread auf, ruhig mit dem Beispiel (Du kannst übrigens das zweite Makro (Abgleich1) löschen, das war zum rumprobieren).
Vielleicht kann Dir ein Anderer weiter helfen, sonst schaue ich morgen oder so nochmal 'rein.

CCC01
16.03.2009, 08:14
Hallo Uwe ich hab noch einen kleinen Bug entdeckt und zwar folgendes: Wenn in Tabelle 1 in mehreren aufeinanderfolgenden Zeilen derselbe Betrag steht und in Tabelle 2 ebenso, lediglich mit unterschiedlichen Belegfeldern so makiert er ab der ersten Wiederholung des Betrags in Tab 1 die Zeilen nicht mehr grün (wo wir grad dabei sind, wäre es arg viel Mehraufwand wenn man statt die ganze Zeile grün zu färben lediglich die Felder die Geldbeträge sprich Soll und Haben Umsätze grün färbt und wenn identisch das Belegfeld?) Vergessen wir solang mal die Sache mit der Spaltenangabe von oben, denn es wäre erstmal wichtiger, dass er mit identischen aufeinanderfolgenden Beträgen richtig umgeht.

Danke schonmal im voraus

EDITED:

Vielleicht sollten wir in Betracht ziehen das Datum als Vergleichskriterium miteinzubauen, denn sonst wird er bei identischen Beträgen jeweils beim ersten den er in Tabelle 1 findet uns in Tabelle 2 sagen, dass dieser in der Zeile steht in der er den ersten korrespondierenden Betrag gefunden hat ...

CCC01
16.03.2009, 18:07
Im Anhang unsere Tabelle so erweitert, dass mein beschriebenes Problem deutlich wird. Anmerkung: Die rote gefärbten Zeilen beinhalten den beschriebenen Fehler und wurden nachträglich zur Hervorhebung markiert

Edited:

Rein vom methodischen her würde mit meiner bescheidenen Kenntnis der Möglichkeiten evtl. folgender Lösungsweg zum Ziel führen:

Man lässt die Prozedur vom Grundablauf wie sie ist ändert aber zuerst ab, dass immer wenn ein Treffer erzielt werden konnte in der jeweiligen Zeile in Tabelle 1 in einer Hilfsspalte ausgegeben wird. Im folgenden müsste dann nur noch der Range der zu überprüfenden Datensätze unter die Bedingung gestellt werden, dass eine leere Hilfszelle in Tabelle 1 vorliegen muss.

Aber bekanntlich führen ja viele Wege nach Rom.

Uwe (:o)
16.03.2009, 18:36
Hi,
da bin ich wohl von einer falschen Voraussetzung ausgegangen, denn mit Application.Match findet VBA nur den ersten übereinstimmenden Eintrag.
Wenn mehrere Übereinstimmungen möglich sind, bekomme ich das nur mit zwei verschachtelten Schleifen hin. Dadurch wird das Ganze zwar ziemlich langsam, aber ich wüsste keine Alternative.
Ich habe Dich jetzt auch so verstanden, dass als erstes Kriterium das Datum übereinstimmen muss. Dann wird geprüft, ob die Werte übereinstimmen und die Belegnummer. Und das Ganze immer bis zum Ende der Liste in Tabelle1.Das müsste so klappen:
Sub Abgleich()
Dim Zelle As Range
Dim ZelleTab1 As Range
Dim BereichTab1_I As Range
Dim BereichTab1_H As Range
Dim strBelegfeld


With Sheets("Tabelle1")
.Range(.Cells(3, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 9)).Font.ColorIndex = xlAutomatic
Set BereichTab1_H = .Range(.Cells(3, 8), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 8))
Set BereichTab1_I = .Range(.Cells(3, 9), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 9))
End With

With ActiveSheet
.Range(.Cells(3, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 11)).Font.ColorIndex = xlAutomatic
.Range(.Cells(3, 11), .Cells(Rows.Count, 11).End(xlUp)) = ""

For Each Zelle In .Range(.Cells(3, 10), .Cells(Rows.Count, 10).End(xlUp))

For Each ZelleTab1 In BereichTab1_H
Debug.Print ZelleTab1.Offset(0, -6).Address
If Zelle.Offset(0, -8).Value = ZelleTab1.Offset(0, -6).Value Then
If Zelle.Value = ZelleTab1.Value Then
If Zelle.Offset(0, -3).Value <> ZelleTab1.Offset(0, -1).Value Then _
strBelegfeld = "nicht "
Zelle.Offset(0, 1).Value = "USt-Anteil:Tabelle1/ Haben, Zeile: " & ZelleTab1.Row & _
": Belegnummer stimmt " & strBelegfeld & "überein"
Zelle.EntireRow.Font.ColorIndex = 10
ZelleTab1.EntireRow.Font.ColorIndex = 10
strBelegfeld = ""
End If
End If
Next ZelleTab1

For Each ZelleTab1 In BereichTab1_I
Debug.Print ZelleTab1.Offset(0, -7).Address
If Zelle.Offset(0, -8).Value = ZelleTab1.Offset(0, -7).Value Then
If Zelle.Value = ZelleTab1.Value Then
If Zelle.Offset(0, -3).Value <> ZelleTab1.Offset(0, -2).Value Then _
strBelegfeld = "nicht "
Zelle.Offset(0, 1).Value = "USt-Anteil:Tabelle1/ Haben, Zeile: " & ZelleTab1.Row & _
": Belegnummer stimmt " & strBelegfeld & "überein"
Zelle.EntireRow.Font.ColorIndex = 10
ZelleTab1.EntireRow.Font.ColorIndex = 10
strBelegfeld = ""
End If
End If
Next ZelleTab1

Next Zelle
End With
End Sub
Das mit dem nur teilweise färben ist auch möglich und nicht allzu schwierig, aber etwas Fummelei. Darum werde ich mich morgen kümmern.

chris-kaiser
16.03.2009, 19:19
hiho

hier mal ein Versuch
Option Explicit

Sub Abgleich()
'Hauptkriterium die Belegnummer oder?
'ich gehe mal davon aus das diese nicht 2mal vorkommen darf
Dim sh1 As Worksheet, Ilng As Long, objGef As Object, checkdate As Boolean, checkbuTxt As Boolean, checkUst As Boolean
Dim sh2 As Worksheet, checkAnz As Boolean
Set sh1 = Tabelle1 'den Codenamen der Tab
Set sh2 = Tabelle2
'Inhalte löschen aus Tab2 Spalte K
sh2.Range("K3:K" & sh2.Cells(Rows.Count, "K").End(xlUp).Row).ClearContents
'erste Schleife die die Belegenummern einzeln durchgeht
For Ilng = 3 To sh1.Cells(Rows.Count, "G").End(xlUp).Row
'durchsuche die Spalte G in Tabelle2 ob die Belegnummer gefunden wird
Set objGef = sh2.Columns(7).Find(sh1.Cells(Ilng, "g").Value, lookat:=xlWhole)
'wenn die Belegnummer gefunden wird
If Not objGef Is Nothing Then
'Anzahl der belege checken ob Doppeleinträge sind
checkAnz = WorksheetFunction.CountIf(sh2.Columns(7), objGef) = 1
'überprüfe ich ob das Datum das gleiche ist
checkdate = (objGef(1, -4).Value = sh1.Cells(Ilng, "B").Value)
'und überprüfe ob der Buchungstext übereinstimmt
checkbuTxt = (objGef(1, -1).Value = sh1.Cells(Ilng, "E").Value)
'und überprüfe den Ust Anteil
checkUst = (objGef(1, 4).Value = sh1.Cells(Ilng, "i").Value)
'usw wenn Du willst
'Ausgabe in Spalte K
objGef(1, 5).Value = "Beleg in Zeile" & Ilng & " gefunden " & _
IIf(checkdate, "", "Datum stimmt nicht überein ") & _
IIf(checkbuTxt, "", "Belegtext nicht ident!") & _
IIf(checkUst, "", "Ust_stimmt nicht überein! ") & _
IIf(checkAnz, "", "Achtung Belegnummer gibt es Mehrfach!! ")
'usw
End If
Set objGef = Nothing
Next
End Sub

p.s wirklich viel getestet ist es mal nicht

CCC01
17.03.2009, 00:01
Ups entschuldigt meine späte Antwort, habe ganz versäumt, dass der Thread schon auf Seite 2 weitergeht. Oo

1) Uwe, finde es äußerst klasse, dass du dir morgen nochmal Zeit dafür nimmst, denke dann sind wir der präsentationsreifen Lösung schon sehr sehr nahe ... :top:

Nun nochmal wie ich mir das prioritätsmäßig wünschen würde:


Wichtigster Anhaltspunkt ist der Umsatzsteueranteil us Tabelle 2 verglichen mit den Beträgen in der Soll und Haben Spalte aus Tabelle 1
Falls dieser gefunden wurde, soll im nächstebn Schritt geprüft werden ob rein zufällig auch die Belegnummern der beiden Datensätze falls vorhanden übereinstimmen
Und zu guter letzt sollte dann bei allen folgenden Werten eben auf welche Art und Weise auch immer, sofern diese schon einmal gefunden wurden noch zusätzlich das Datum mit als Auswahlkriterium genutzt werden


Und wie gesagt, top wäre es wenn man es farblich auf die Art und Weise wie bereits geschildert hinbekommen könnte und eben die Angaben ob der Betrag gefunden wurde und wenn ja in welcher Zeile aus Tab 1 in ein Feld in ein weiteres Feld die Angaben zur Übereinstimmung des Belegfelds und in ein drittes angrenzendes Feld die Angaben über das datum...

Ihr seid echt spitze!

Und @chris-kaiser:

Danke das du dich der Suche auch anschließt, jedoch ist mir bei deiner Lösung aufgefallen, dass Beträge die zwar übereinstimmen jedoch mit unterschiedlichen Belegfeldern nicht erkannt werden, was schonmal ein Manko darstellt.
Ich seh grad das es in deiner Arbeitsmappe mit den 2,85 kein Problem gibt, aber guck dir mal in der von mir im folgenden Post hochgeladenen Arbeitsmappe die Arbeitsweise deines Makros an. Oo

Aber auch dir vielen lieben Dank

CCC01
17.03.2009, 00:04
Ach und @Uwe, hast du eine Erklärung dafür wieso er die rot markierten Zeilen nicht erkennt?

Edit:
Nur um es nochmal klarzustellen es geht als aller erstes um die Beträge. Denn in diesem Problem geht es in der Praxis darum bei Abweichungen der Umsatzsteuerverprobung die Ursachen zu finden und da diese Abweichungen zumeist bei Insolvenzfälle auftauchen ist es fast schon gängig, dass gar keine Belegnummern exisitieren ...

CCC01
17.03.2009, 19:47
'Hauptkriterium die Belegnummer oder?
'ich gehe mal davon aus das diese nicht 2mal vorkommen darf
Hauptkriterium: einzig der Betrag, alles andere kann optional auch doppelt oder dreifach vorkommen.

BTWich finde es sehr begrüßenswert, dass dein Code kommentiert ist, da es so deutlich schneller geht herauszubekommen wie welche Elemente davon funktionieren und welchen Sinn sie haben!

chris-kaiser
18.03.2009, 04:26
hiho

der Code müsste um einiges umgebaut werden, da ich von einer Eindeutigkeit ausgegangen bin.
das find prüft nur ab ob es einen Eintrag gibt und nicht wieviele es sind, da müsste eine Schleife rundumgebaut werden die alle gefundenen Objekte vergleicht.(wenn Du bei find in der VBE hinklickst und F1 drückst gibt es dafür ein Bsp.)
und ich habe Tabelle1 mit Tab2 verglichen, ich vermute das gehört umgedreht.

Vielleicht komme ich dazu den Code umzuschreiben, nur versprechen kann ich es nicht, da Freizeit für mich im Moment Luxus ist:( .

CCC01
18.03.2009, 12:53
Hallo Chris,

danke für den Hinweis, ich werde es mir auf alle Fälle auch mal ansehen, aber bezweifle doch, dass ich es allein hinbekomm und wäre dir für deine Hilfe sehr dankbar, obgleich ich das Problem mit der nicht vorhandenen Freizeit zu gut nachvollziehen kann - aber vielleicht kann ja aufgrund des gesagten auch irgend ein anderer etwas fähigerer Mensch als ich mir damit weiterhelfen! Und ja, Tabelle 2 dient als Ausgangspunkt. Danke jedenfalls für deinen Input.

Uwe (:o)
18.03.2009, 17:55
Hi,
wie ich beschrieben hatte, wird in meiner letzten Version zuerst nach dem Datum gesucht. Wenn das nicht übereinstimmt, dann wird nicht weiter verglichen. Du hattes geschrieben, das Datum sei zu berücksichtigen, und ich hatte das so verstanden.
Inzwischen blicke ich ehrlich gesagt nicht mehr ganz durch. Ich hoffe Chris, mit dem verglichene ich ein Anfänger bin, kann Dir helfen.
Ich werde das weiter verfolgen.
Vielleicht kannst Du sonst nochmal "in eigenen Worten beschreiben" wie die Schleifen laufen sollen. Also:" kucke wo der Betrag vorkommt, wenn ja dann machs grün, dann such weiter, kommt er nochmal vor schau aufs Datum (aber wie? gleich, später,früher) oder nach der Belegnummer... oder...)".
Leider ist bei mir im Moment die Zeit auch etwas knapper, aber wenns am Wochenende regnet.... (;-)

CCC01
18.03.2009, 18:14
So dann versuche ich nochmal etwas klarer zu beschreiben wie ich mir das vorstellen würde, vorab allerdings ein paar Konventionen um es übersichtlicher zu schreiben


Spalten aus Tabelle 2 werden im folgenden mit deren Buchstaben und einer angehängten 2 bezeichnet
Spalten aus Tabelle 1 folglich mit Buchstabe + 1
Und noch ein Hinweis: Farben sollen nun mal außen vor gelassen werden, da diese reinen Luxus darstellen und zuerst das Programm funktionieren muss


Es soll in J2 damit gestartet werden, dass der Inhalt aus Zeile 3 J2 zuerst mit H1 und danach mit I1 verglichen wird.
Bei nur einem positiven Treffer soll nichts weiter berücksichtigt werden, falls jedoch 2 oder mehr Treffer auftauchen, so soll zuerst der Ausgangswert aus B2 mit denen der Treffer in B1 verglichen werden.
Zusätzlich sollen die Werte der Treffer aus G2 mit denen der Werte in G1 verglichen werden und jeweils Meldung darüber ausgegeben werden, ob für die Werte aus B1+2 und G1+2 übereinstimmung oder nicht vorliegt.
Im folgenden soll dann die gleiche Prozedur noch für die Werte aus I1 mit J2 als Ausgangsbasis in selbiger Art und Weise durchgeführt werden.
Wobei es vielleicht sinnvoll aus Gründen der Performance wäre ihn zunächst testen zu lassen, ob die jeweilige Zelle aus H1 respektive I1 leer ist oder nicht und nur im Falle von nicht leer die weiteren Tests erfolgen.

Ich hoffe es wurde etwas deutlicher

Uwe (:o)
21.03.2009, 10:16
Hi,
ein paar Nachfragen:
falls jedoch 2 oder mehr Treffer auftauchen, so soll zuerst der Ausgangswert aus B2 mit denen der Treffer in B1 verglichen werden.
Was soll dann damit passierern (soll das nächstliegende Datum berücksichtigt werden oder was sonst?)
Zusätzlich sollen die Werte der Treffer aus G2 mit denen der Werte in G1 verglichen werden und jeweils Meldung darüber ausgegeben werden, ob für die Werte aus B1+2 und G1+2 übereinstimmung oder nicht vorliegt.
Soll das in Tabelle1 geschehen? In Tabelle2 steht der Wert ja nur in EINER Zeile?
dass der Inhalt aus Zeile 3 J2 zuerst mit H1 und danach mit I1 verglichen wird.
Im folgenden soll dann die gleiche Prozedur noch für die Werte aus I1 mit J2 als Ausgangsbasis in selbiger Art und Weise durchgeführt werden.
Die beiden zusammen verstehe ich nicht. Ist das nicht "doppelt gemoppelt"?

CCC01
22.03.2009, 06:52
Was soll dann damit passierern (soll das nächstliegende Datum berücksichtigt werden oder was sonst?)Es soll einfach verglichen werden, ob es zu diesem Betrag die Datumsangabe aus Tabelle 2 mit der des Fundeintrags aus Tabelle 1 übereinstimmt.

Und ggf. eine positive Meldung mit Angabe der Zeilennummer der Übereinstimmung von Datum und Betrag des Datensatzes aus Tab1 gemacht werden, oder evben ausgegeben werden, dass es hierzu keine Übereinstimmung gab,.

Zitat:

Zusätzlich sollen die Werte der Treffer aus G2 mit denen der Werte in G1 verglichen werden und jeweils Meldung darüber ausgegeben werden, ob für die Werte aus B1+2 und G1+2 übereinstimmung oder nicht vorliegt.

Soll das in Tabelle1 geschehen? In Tabelle2 steht der Wert ja nur in EINER Zeile?
Pardon, hier hab ich mich wohl etwas ungl+ücklich ausgedrückt. Es war eigentlich so gemeint, dass einfach unabhängig von den Übereuinstimmungen beim Datuzm noch geschaut werden soll, ob bei den Fundeinträgen bzgl des Betrags aus Tabelle 1 noch die Belegnummern auf übereinstimmung mit der des Datensatzes aus Tabelle 2 geprüft werden sollen und wiederum eine Ausgabe des Ergebnisses des Vergleichs erfolgen soll. Und ja das soll in Tabelle 1 unter den Einträgen die vom Betrag jher übereinstimmen passieren.

Zitat:

dass der Inhalt aus Zeile 3 J2 zuerst mit H1 und danach mit I1 verglichen wird.

Zitat:

Im folgenden soll dann die gleiche Prozedur noch für die Werte aus I1 mit J2 als Ausgangsbasis in selbiger Art und Weise durchgeführt werden.

Die beiden zusammen verstehe ich nicht. Ist das nicht "doppelt gemoppelt"?Vergiss die einfach, bin mir selbst grad etwas unschlüssig was ich damit genau sagen wollte Oo. Sorry, bin auch etwas überarbeitet...

Und nochmals vielen Dank für deine Hilfe

ebs17
22.03.2009, 10:51
Hallo CCC01,

kannst Du mal die Datei mit Excel 2000 lesbar zur Verfügung stellen und dabei die Tabellen so darstellen, dass die Datenzeilen mindestens ab Zeile 2 beginnen (max. eine Titelzeile). Die Zeilen sollten zusätzlich (falls nicht vorhanden) jeweils eine eindeutige ID (Long) haben zwecks Darstellung einer Meldung, Kennzeichnung einer Zeile zwecks Färbung usw.

Ansatzpunkt: Performant (viele) Daten zu vergleichen ist eine typische Aufgabe für SQL. Wenn dieses anwendbar ist, ist es um einiges effektiver als die Verwendung von VBA-Schleifen. (Ich bin gerade auf dem Trip, einige Möglichkeiten außerhalb von Datenbanken auszuloten.)

CCC01
22.03.2009, 12:35
Hallo Ebs17,

freut mich wirklich sehr, dass du dich auch in die Diskussion eingeklingt hast und versuchst eine Lösung für mein Problem zu finden. Ich hab mal die Arbeitsmappe geändert und angehängt, hoffe ich habe das mit der eindeutingen ID (long) richtig verstanden.

EDITED: Habe die Datensätze zu Testzwecken noch ein bisschen vergrößert!

Uwe (:o)
22.03.2009, 14:07
Hi,
teste das mal aus:
Sub Abgleich()
Dim Zelle As Range
Dim ZelleTab1 As Range
Dim BereichTab1_I As Range
Dim BereichTab1_H As Range
Dim strBelegfeld
Dim varGefunden

With Sheets("Tabelle1")
'Farben zurücksetzen:
.Range(.Cells(3, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 9)).Font.ColorIndex = xlAutomatic
'Such-Bereiche festlegen
Set BereichTab1_H = .Range(.Cells(3, 8), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 8))
Set BereichTab1_I = .Range(.Cells(3, 9), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 9))
End With
With ActiveSheet
'Farben zurücksetzen und Spalte K leeren
.Range(.Cells(3, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 11)).Font.ColorIndex = xlAutomatic
.Range(.Cells(3, 11), .Cells(Rows.Count, 11).End(xlUp)) = ""

For Each Zelle In .Range(.Cells(3, 10), .Cells(Rows.Count, 10).End(xlUp)) 'Schleife Tab1 Spalte I
'Kommt der Wert in Tab1 H/I vor?:
Select Case WorksheetFunction.CountIf(Sheets("Tabelle1").Columns(8), Zelle) + _
WorksheetFunction.CountIf(Sheets("Tabelle1").Columns(9), Zelle)
Case 0
Case 1
If WorksheetFunction.CountIf(Sheets("Tabelle1").Columns(8), Zelle) = 1 Then _
varGefunden = Application.Match(Zelle.Value, Sheets("Tabelle1").Columns(8), 0)
If WorksheetFunction.CountIf(Sheets("Tabelle1").Columns(9), Zelle) = 1 Then _
varGefunden = Application.Match(Zelle.Value, Sheets("Tabelle1").Columns(9), 0)
If IsError(varGefunden) Then MsgBox "Fehler"
If Sheets("Tabelle1").Cells(varGefunden, 7) = Zelle.Offset(0, -3).Value Then _
strBelegfeld = " / Belegfelder stimmen überein"
Zelle.Offset(0, 1) = "Gefunden in Zeile " & varGefunden & strBelegfeld
strBelegfeld = ""
Case Else
For Each ZelleTab1 In BereichTab1_H
If Zelle.Value = ZelleTab1.Value Then
strBelegfeld = ""
If Zelle.Offset(0, -3).Value = ZelleTab1.Offset(0, -1).Value And _
Zelle.Offset(0, -8).Value = ZelleTab1.Offset(0, -6).Value Then
strBelegfeld = " / Belegfelder stimmen überein"
Zelle.Offset(0, 1) = "Gefunden in Zeile " & ZelleTab1.Row & " / Gleiches Datum!" & strBelegfeld
Exit For
ElseIf Zelle.Offset(0, -8).Value <> ZelleTab1.Offset(0, -6).Value Then
Zelle.Offset(0, 1) = "Gefunden in Zeile " & ZelleTab1.Row & " / Anderes Datum!" & strBelegfeld
End If
End If
Next ZelleTab1

For Each ZelleTab1 In BereichTab1_I
If Zelle.Value = ZelleTab1.Value Then
strBelegfeld = ""
If Zelle.Offset(0, -3).Value = ZelleTab1.Offset(0, -2).Value Then _
strBelegfeld = " / Belegfelder stimmen überein"
If Zelle.Offset(0, -8).Value = ZelleTab1.Offset(0, -7).Value Then
Zelle.Offset(0, 1) = "Gefunden in Zeile " & ZelleTab1.Row & " / Gleiches Datum!" & strBelegfeld
Exit For
Else
Zelle.Offset(0, 1) = "Gefunden in Zeile " & ZelleTab1.Row & " / Anderes Datum!" & strBelegfeld
End If
End If
Next ZelleTab1
End Select
Next Zelle
End With
End Sub
Ist wahrscheinlich noch nicht 100%ig was Du willst (und kann bestimmt auch noch optimiert werden), aber vielleicht erst mal eine Grundlage.
Bastele am besten mal ein Beispiel zusammenm, in dem ALLE Möglichkeiten vorkommen, am besten mehrfach, lasse dann diesen Code drüberlaufen, und schreibe in Spalte L, ob das Ergebinis richtig ist, bzw. wie es richtig hätte sein sollen.
Falls Eberhard eine SQL unterstützte Lösung findet, ist die aber auf jeden Fall vorzuziehen.
(Auch Chris' Code wird, wenn er ihn noch anpasst wahrscheinlich besser sein.)

CCC01
22.03.2009, 14:24
Danke super von dir! Glaubst gar nicht wie erfreut ich darüber bin, dass sich doch soviele Leute rege an meinem Problem beteiligen! Eins ist allerdings noch etwas seltsam, lass das Makro dazu mal über meine eben hochgeladen Datei mit Endung xlms laufen und schau dir an was bei dem Wert aus Zeile 4 Tab 2 passiert, er findet ihn, allerdings gibt er an ihn nur einmal gefunden zu haben zwar in Zeile 23 obwohl er das erste mal schon in Zeile 4 Tab 1 auftaucht. Gibts dafür eine plausible Erklärung? Und allgemein, fiel mir auf, dass bei mehrfachem Vorkommen eines Wertes in Tabelle 1 immer nur 1 Fundwert in Tabelle 2 ausgegeben wurde und nicht alle Übereinstimmungen, dieses Verhalten wäre aber lediglich bei mehrfachem Vorkommen des gesuchten Betrags in Tabbele 1 wünschenswert

ebs17
22.03.2009, 15:08
Kurze Wasserstandsmeldung:
Ich habe in beide Tabellen eine Spalte ID eingefügt (beginnend mit jeweils 1, Inhalt ID also gleich aktuelle Zeile - 1).
Darauf habe ich eine Abfrage gelegt mit dem Kriterium:
(Soll1 = USt-Anteil2 ODER Haben1 = USt-Anteil2) UND USt-Satz1 = USt-Satz2

Das beigefügte Bildchen zeigt das Ergebnis mit allen dazu möglichen Varianten. Ist das so richtig (Logikprüfung)?

Wie soll jetzt das Datum einbezogen werden?

Wie soll das Ergebnis ausgewertet werden?
- Mit Hilfe der ID kann man betreffende Zeilen färben, in zusätzlichen Spalten Einträge veranlassen u.ä.
- Das gezeigte Ergebnis oder ähnliches kann man in einem dritten Arbeitsblatt anzeigen und irgendwie auswerten.

CCC01
22.03.2009, 17:29
Hey wow, das ist wirklich spitze! Nur eine Frage hab ich, wie verhält sich deine Anfrage, wenn der Betrag aus der Soll bzw Haben-Seite aus Tabelle 2 mehrmals in Tabelle 1 auftaucht, ist es dann möglich alle IDs der Datensätze aus Tabelle1 in irgendeiner Form ausgeben zu lassen und dann eben unter diesen Beträgen zu vergleichen, ob für einen oder mehrere ggf. auch das Datum übereinstimmt und das ergebnis dieser Abfrage widerrum auszugeben. Und ganz zum Schluss wäre es cool, wenn alle Datensätze aus Tabelle 1, die keinem Datensatz aus Tabelle 2 in irgend einer Form zugeordnet werden konnten rot gefärbt werden würden.

:top:

ebs17
22.03.2009, 17:45
ist es dann möglich alle IDs der Datensätze aus Tabelle1 in irgendeiner Form ausgeben zu lassen
Ja. Ohne ein Kriterium ergäbe sich ein Kreuzprodukt ((13 DS -1) x (13 DS -1) = 144 DS), also alle möglichen Kombinationen. Mit passenden Kriterien kann man dann das Ganze sinnvoll einschränken.

Die Abfragen werden nicht nacheinander, sondern es wird eine Abfrage mit allen notwendigen Kriterien (Datum1=Datum2 kommt also hinzu) ausgeführt. Die ist dann ein wenig komplexer.

Der Rest ist lösbar.

CCC01
22.03.2009, 17:49
Wirklich spitze. Ich dank dir jetzt schon mal!! Mein Held des Tages!

Uwe (:o)
22.03.2009, 18:03
Hi,
es wird der Wert in Zeile 23 angegeben, weil mein Code im Moment so läuft, dass er, wenn ein Wert mehrmals vorkommt, DIE Zeile ausgibt, in der das DATUM übereinstimmt. Wenn es in keinem FundFall übereinstimmt (wie in diesem Fall), wird die letzte Zeile ausgegeben in der der Wert gefunden wurde. Wie soll es dann RICHTIG aussehen?
Deinen letzen Satz verstehe ich nich wirklich:
..dass bei mehrfachem Vorkommen eines Wertes in Tabelle 1 immer nur 1 Fundwert in Tabelle 2 ausgegeben wurde und nicht alle Übereinstimmungen, dieses Verhalten wäre aber lediglich bei mehrfachem Vorkommen des gesuchten Betrags in Tabbele 1 wünschenswert
Ich gebe zu dass ich langsam nicht mehr ganz durchblicke.
Ich hoffe Eberhard, im Gegensatz zu mir ein Profi, kann Dir helfen.

ebs17
22.03.2009, 20:22
Anbei ein Vorschlag - den Test auf Richtigkeit musst Du selber vornehmen.
Anmerkung: Der Code läuft auf Excel 2000. Bei Excel 2007 benötigst Du vermutlich einen anderen Connectionstring (einen Versuch dazu habe ich beigefügt).
Option Explicit

Public Sub XX_Abgleich()

' Verweis auf Microsoft ActiveX Data Objects 2.1 Library setzen

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sPath As String
Dim sSQL As String
Dim sh As Worksheet
Dim i As Long
' Pfad der aktiven Mappe
sPath = "E:\Forum\Excel_SQL\Excel_SQL.xls"

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & sPath & _
";Extended Properties=Excel 8.0;"

' ' für O2007 muss der Connectionstring wahrscheinlich so aussehen
' ' evtl. auch Verweis ändern
' .Provider = "Microsoft.ACE.OLEDB.12.0"
' .ConnectionString = "Data Source=" & sPath & _
' ";Extended Properties=Excel 12.0 Xml;"

.CursorLocation = adUseClient
.Open
End With

sSQL = "SELECT DISTINCT T.[ID1] FROM [Tabelle1$] T LEFT JOIN" & _
" (SELECT T1.[ID1] FROM [Tabelle1$] T1, [Tabelle2$] T2" & _
" WHERE (T1.[Soll1]=T2.[USt-Anteil2] OR T1.[Haben1]=T2.[USt-Anteil2])" & _
" AND T1.[USt-Satz1]=T2.[USt-Satz2] AND T1.[Datum1]=T2.[Datum2]) Q" & _
" ON T.[ID1] = Q.[ID1] WHERE Q.[ID1] Is Null AND T.[ID1] Is Not Null"

rs.Open sSQL, cn, adOpenStatic

Set sh = ActiveWorkbook.Worksheets("Tabelle1")
i = 1
Do While Not rs.EOF
If rs(0) > 1 Then
' Testausgabe der ID´s
Debug.Print i & ": " & rs(0)
' Colorieren
sh.Rows(rs(0) & ":" & rs(0)).Font.ColorIndex = 3
End If
i = i + 1
rs.MoveNext
Loop

rs.Close
cn.Close
Set sh = Nothing
Set rs = Nothing

End Sub
Die Namen der Arbeitsblätter habe ich zwecks einfacher Anpassungsmöglichkeit farbig hervorgehoben.
Für ein erstes Verstehen der SQL-Anweisung: SQL-Tutorial (http://www.sql-und-xml.de/sql-tutorial/index.html)

Anmerkung: Dies ist mein erst zweiter Versuch mit einem ADODB-Recordset. Verbesserungen sind also nicht ausgeschlossen.

CCC01
22.03.2009, 20:39
Hallo Uwe ich hab dir mal ein Beispiel manuell teilweise beschriftet wie ich mir die Ausgabe wünschen würde, vielleicht kannst du daran ja mein 'System' erkennen. Ich weiß dass es schwer für mich zu erklären ist ohne mich selbst schon konfus damit zu machen ... Irgend ein Makro scheint die jeweiligen Spalten K,L,M aus Tab 2 in Tab 1 zu kopieren, aber lass dich davon mal einfach nicht irritieren und vergleiche anhand des rotfarbeigen Textes in Tab 2 wie ich vorgegangen bin bzgl Tab 1.


@ebs17: Schon mal rießen Dank, ich werds gleich mal ausprobieren und mich dann nochmal melden ...

:top:

CCC01
22.03.2009, 20:46
Also momentan bringt er mir noch nen Laufzeitfehler ebs17. :mad:

Aber google grad mal bisschen und hoffe noch was zu finden...

EDITED: Screenshot des Fehlers:

ebs17
22.03.2009, 20:54
sPath = "E:\Forum\Excel_SQL\Excel_SQL.xls"
Der Pfad und der Name Deiner Datei lauten vermutlich anders.

CCC01
22.03.2009, 20:59
In VBA geht es am besten mit ADO, aber auch DAO soll gut sein, ist mir aber etwas kompliziert.

Innerhalb einer Excel-Tabelle klappt es mit dem Befehl

sql.request("DSN=Treibername";;;"Select * from artikel";wahr)

Ansonsten kannst Du auch über 'Extras|Externe Daten' mit MS Query auf diese Daten zugreifen.Das hab ich grad gefunden, falls dir das mehr hilft als mir momentan ... :confused:

ebs17
23.03.2009, 01:08
Siehe #39, da braucht es noch kein Googlen.

CCC01
23.03.2009, 05:15
Auch auf die Gefahr hin etwas doof auszusehen, um was für eine Datei handelt es sich bei Excel_SQL.xls? Und wie kann ich herausfinden welchen Pfad ich dort angeben muss? :redface:


EDITED: Hat sich erledigt....

OMG

CCC01
23.03.2009, 05:32
Allerdings macht er leider gar nichts außer mir einige Zeilen in Tabelle 1 rot einzufärben :( Kannst du mir mal nen Screenshot posten wie die Sache bei dir nach einem Lauf aussieht?

Er gibt mir aber jetzt zumindestens schon mal keine Fehler mehr aus :)

ebs17
23.03.2009, 09:17
... und dann eben unter diesen Beträgen zu vergleichen, ob für einen oder mehrere ggf. auch das Datum übereinstimmt und das ergebnis dieser Abfrage widerrum auszugeben. Und ganz zum Schluss wäre es cool, wenn alle Datensätze aus Tabelle 1, die keinem Datensatz aus Tabelle 2 in irgend einer Form zugeordnet werden konnten rot gefärbt werden würden.
Mein Ergebnis zeigt (hoffentlich) das. Falls Du irgendwelche Zwischenergebnisse angezeigt haben willst, musst Du präzise sagen, was und wo (vergleiche Ausführungen zur Verständlichkeit der Aufgabenstellung).

Das Ergebnis der Abfrage sind derzeit die ID´s der Tabelle1, die keine Beziehung zu Tabelle2 haben. In der beigefügten Datei kannst Du sehen, dass ich die ID´s mit den Zeilennummern gleichgeschaltet habe, um mir Zuordnungsberechnungen zu ersparen.

Das mit der Datei ist klar? Die hieß bei mir anders, und beim Zurückumbenennen hatte ich die Zeile nicht berücksichtigt, wobei der Pfad trotzdem ein anderer als der von Dir verwendete sein dürfte.
Dieser "Fehler" zeigt einerseits, dass man auch auf eine externe Excelmappe referenzieren kann (das funktioniert, wenn die Datei da ist).

Andererseits fehlt mir noch die Anweisung, um Pfad und Name der aktuellen Mappe zu ermitteln, quasi die Excel-Entsprechung von CurrentProject.Path für Access.

[EDIT]: Gefunden:
sPath = ThisWorkbook.Path & "\" & ActiveWorkbook.Name

ebs17
23.03.2009, 13:32
Eine kleine Variante zur Darstellung des Zwischenergebnisses a la #31:
...
sSQL = "SELECT T1.[ID1], T2.[ID2], T1.[Soll1], T1.[Haben1], T2.[USt-Anteil2]," & _
" T1.[USt-Satz1] FROM [Tabelle1$] T1, [Tabelle2$] T2" & _
" WHERE (T1.[Soll1]=T2.[USt-Anteil2] OR T1.[Haben1]=T2.[USt-Anteil2])" & _
" AND T1.[USt-Satz1]=T2.[USt-Satz2] AND T1.[Datum1]=T2.[Datum2]"
rs.Open sSQL, cn, adOpenStatic

' Darstellung Ergebnis in Tabelle3
Set sh = ActiveWorkbook.Worksheets("Tabelle3")
sh.Cells(2,1).CopyFromRecordset rs

' Bereinigen
rs.Close

Set sh = ActiveWorkbook.Worksheets("Tabelle1")

sSQL = "SELECT DISTINCT T.[ID1] FROM [Tabelle1$] T LEFT JOIN" & _
" (SELECT T1.[ID1] FROM [Tabelle1$] T1, [Tabelle2$] T2" & _
" WHERE (T1.[Soll1]=T2.[USt-Anteil2] OR T1.[Haben1]=T2.[USt-Anteil2])" & _
" AND T1.[USt-Satz1]=T2.[USt-Satz2] AND T1.[Datum1]=T2.[Datum2]) Q" & _
" ON T.[ID1] = Q.[ID1] WHERE Q.[ID1] Is Null AND T.[ID1] Is Not Null"
...

CCC01
23.03.2009, 13:48
[...] wie verhält sich deine Anfrage, wenn der Betrag aus der Soll bzw Haben-Seite aus Tabelle 2 mehrmals in Tabelle 1 auftaucht, ist es dann möglich alle IDs der Datensätze aus Tabelle1 in irgendeiner Form ausgeben zu lassen und dann eben unter diesen Beträgen zu vergleichen, ob für einen oder mehrere ggf. auch das Datum übereinstimmt und das ergebnis dieser Abfrage widerrum auszugeben. Und ganz zum Schluss wäre es cool, wenn alle Datensätze aus Tabelle 1, die keinem Datensatz aus Tabelle 2 in irgend einer Form zugeordnet werden konnten rot gefärbt werden würden. Hi, vielen Dank für deine Hilfe, ich denke wir sind nun schon sehr weit, aber ich habe versucht durch meine Hervorhebungen im obigen Zitat deutlicher zu machen, was mir noch fehlt: nähmlich eine Ausgabe der gefundenen Datensätzen in Form derer IDs. Diese Ausgabe sollte wenn möglich in der selben Zeile des gesuchten Objekts erfolgen und darüber Auskunft geben, welche Kriterien denn für die jeweiligen Treffer tatsächlich übereinstimmen.

Mir viel dann noch in deinem Code folgendes auf:

" AND T1.[USt-Satz1]=T2.[USt-Satz2] AND T1.[Datum1]=T2.[Datum2]) Q" & _Und mich verunsicherte das Vorkommen des USt-Satzes als Vergleichskriterium und gleichzeitig vermisste ich das ursprünglich gewollte Belegfeld, aber ich denke es ist unproblematisch den Code dann einfach wie folgt zu schreiben

" AND T1.[Belegfeld1]=T2.[Belegfeld2] AND T1.[Datum1]=T2.[Datum2]) Q" & _ Oder?



Und dann noch folgende Frage bzgl folgender Unterscheidung:

' .Provider = "Microsoft.Jet.OLEDB.4.0"
' .ConnectionString = "Data Source=" & sPath & _
' ";Extended Properties=Excel 8.0;"

' ' für O2007 muss der Connectionstring wahrscheinlich so aussehen
' ' evtl. auch Verweis ändern
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & sPath & _
";Extended Properties=Excel 12.0 Xml;"
Welcher der beiden Alternativen ist Excel 2003 zuzuordnen?

Und wird in deinen Suchvorgaben nicht eigentlich vorgegeben, dass alle Felder kumulativ erfüllt sein müssen, dadurch dass du den Operator <<AND>> nutzt? Denn das wäre dann schon wieder abweichend vom gewünschten Verhalten, dass generell auch bei alleiniger Übereinstimmung eines Spaltenwerts aus USt-Anteil2 mit einem aus Spalten Soll1 & Haben1 eine Ausgabe in Textform über die entsprechende(n) Fundstelle(n) in Tabelle2 erfolgen soll.

:sos:

EDITED:
Dieser Beitrag berücksichtigt deinen vorangehenden noch nicht, da er entstand während dieser gepostet wurde

ebs17
23.03.2009, 14:30
Wenn mein Vorschlag von Deinen Vorstellungen abweicht, kann es auch daran liegen, dass Deine Beschreibung
- verwirrend war,
- unkonkret war, so dass ich eigene Annahmen getroffen habe.
Dass ich mich in Neuland bewege und das Ganze mache, um selber etwas zu lernen und dahingehend auch fehlerbehaftet bin, ist ein zusätzlicher Aspekt.

(1) Das mit dem Tausch der Feldnamen ist problemlos möglich, dafür werden ja eindeutige Namen verwendet (in SQL gibt es keine Spalten und Zeilen, sondern Felder und Datensätze).

(2) Office 2000 - 2003 ist ziemlich ähnlich und meist gleichwertig zu verwenden. Größere Unterschiede gibt es bei Word und Outlook. Also: Excel 2003 ist sehr nahe zu Excel 2000. Excel-Version 8 entspricht übrigens Excel 97.

(3) In der SQL-Anweisung werden alle angegebenen Kriterien auf einmal geprüft. Eine Angabe, wo was fehlt, ist nur möglich, wenn man einzelne Anweisungen mit genau den gesuchten Kriterien ausführt. Ebenso ist dann eine Zuordnung in der gleichen Tabelle nicht bzw. nur mit VBA möglich (aber umständlich). Üblich wäre das Anzeigen des Abfrageergebnis in einer zusätzlichen Tabelle (wie in #45 angedeutet).

(4) Ausgabe: Konnte ohne konkrete Angabe schwanken zwischen 10 + 11 Feldern und einem Feld (ID, siehe Debug.Print-Ausgabe und Zeilenfärbung).

dass du den Operator <> nutzt
Wo findest Du das?

CCC01
23.03.2009, 15:08
Wenn mein Vorschlag von Deinen Vorstellungen abweicht, kann es auch daran liegen, dass Deine Beschreibung
- verwirrend war,
- unkonkret war, so dass ich eigene Annahmen getroffen habe.
Dass ich mich in Neuland bewege und das Ganze mache, um selber etwas zu lernen und dahingehend auch fehlerbehaftet bin, ist ein zusätzlicher Aspekt.Ich habe meine Äußerungen absolut nicht als Kritik verstanden. Ich bin super dankbar, dass du mir überhaupt hilfst und bin mir auch bewusst, dass einiges im laufe dieses Threads weit entfernt von K L A R gewesen ist. Entschuldige dies bitte - es war nicht meine Absicht und der Vorgang m.E. nicht ganz unproblematisch zu schildern via Internet.

(3) In der SQL-Anweisung werden alle angegebenen Kriterien auf einmal geprüft. Eine Angabe, wo was fehlt, ist nur möglich, wenn man einzelne Anweisungen mit genau den gesuchten Kriterien ausführt. Ebenso ist dann eine Zuordnung in der gleichen Tabelle nicht bzw. nur mit VBA möglich (aber umständlich). Üblich wäre das Anzeigen des Abfrageergebnis in einer zusätzlichen Tabelle (wie in #45 angedeutet).Alles klar, das ist aber auch nicht weiter tragisch

(4) Ausgabe: Konnte ohne konkrete Angabe schwanken zwischen 10 + 11 Feldern und einem Feld (ID, siehe Debug.Print-Ausgabe und Zeilenfärbung).Ich bin mir nicht ganz sicher wie ich den blau gefärbten Teil deiner Äußerung richtig verstehen darf. Entschuldige. Und zur "Ausgabe" noch eine Frage, kann man dann in der Ausgabe wie in deinem Beispiel in Tabelle3 für jeden Treffer bzgl des Vergleichwerts USt-Anteil2 die ID der Fundstelle irgendwie mit in die Ausgabetabelle in Tabelle3 einbeziehen? Das wär dann schon fast die Lösung!

Danke nochmals und entschuldige die problematische Darstellungsweise meinerseits.

P.S. Statt <> schrieb ich AND wobei ich mit <> nur deutlich machen wollte, dass ich mich auf Code beziehe.

ebs17
23.03.2009, 15:54
Zitat 1: Das ist primär eine Feststellung. Kritiken soll sich der herausziehen, der will. (Wenn ich mich ärgere, klingt das anders.)

Ausgabe: Es sind von allen 21 Feldern eben alle (macht mehr Schreibarbeit und verschlechtert die Übersicht) oder auch nur einige sinnvolle ausgewählte möglich. Konkret wäre die Angabe: Ich brauche in der folgenden Reihenfolge ...

Zur Erweiterung lt. #45:
In der Tabelle 3 erscheinen beide ID´s. Die erste Zeile hatte ich freigelassen für die Feldnamen. Die kannst Du per Hand eintragen, man könnte sie aber auch per Code aus dem Recordset dahin eintragen (das hatte ich der Übersicht wegen weggelassen).
Die erste SQL-Anweisung enthält die Trefferliste (mit den Kriterien, wie ich sie zum Zeitpunkt verstanden hatte).
Die zweite SQL-Anweisung enthält aufbauend auf der ersten diejenigen ID´s aus Tabelle 1, die nicht durch die Trefferliste erfasst wurden.

gewünschten Verhalten, dass generell auch bei alleiniger Übereinstimmung eines Spaltenwerts aus USt-Anteil2 mit einem aus Spalten Soll1 & Haben1 eine Ausgabe ...
Dann müsste man dafür eine separate SQL-Anweisung mit Ausgabe anlegen, wobei es einfacher ist, den gewünschten Text in dieser Auswertung zu ergänzen, als eine Zuordnung zu Tabelle 1 oder 2 zu machen (in SQL gibt es keine Zeilenabhängigkeiten).

Dinge wie Arbeitsblatt anlegen bzw. Prüfen auf Vorhandensein, Leeren eines Arbeitsblattes u.ä. hatte ich auch weggelassen. Das ist dann für eine runde Lösung angebracht.

ebs17
23.03.2009, 16:01
Kleiner Nachtrag:
...
' Feldnamen aus Recordset in erste Zeile übernehmen
For i=1 To rs.Fields.Count
sh.Cells(1, i) = rs.Fields(i - 1).Name
Next

sh.Cells(2,1).CopyFromRecordset rs
...

jinx
23.03.2009, 16:34
Moin, Eberhard,

statt
sPath = ThisWorkbook.Path & "\" & ActiveWorkbook.Name
sPath = ThisWorkbook.FullName
Und mit dem "\" wäre ich auch vorsichtig, die Überraschung beim MAC ist Dir gewiss - dort würde ich stattdessen Application.PathSeparator einsetzen...

ebs17
23.03.2009, 16:53
@jinx: Vorschläge, die besser und sicherer sind und zudem weniger Arbeit machen, übernehme ich besonders gern. Danke.

CCC01
23.03.2009, 17:59
Wäre es nicht u.U. viell. nicht ganz unvernünftig folgendermaßen vorzugehen:

Man querryed zuerst ob USt-Anteil2 = Soll1 OR Haben1 und lässt sich alle Datensätze für die die Bdg. TRUE ist ausgeben.

In einem nächsten Schritt könnte man die Daten aus Tabelle2 mit der obigen Ausgabe dahingehend das man nun prüft, ob USt-Anteil2 = Soll1 OR Haben1 AND Datum2 = Datum1 und sich dies wiederrum ausgeben lassen.

Und dann könnte man das ganze noch für die Belegfelder in analoger Art tun (wenn überhaupt nötig). Denn wenn die Ausgabe eines jeden dieser zwei respektive drei Test in einem eigenen Arbeitsblatt erfolgt würde dem Betrachter wahrscheinlich übersichtlicherweise dargestellt wonach er sucht. Denn es besteht dann die Möglichkeit zuerst einmal das wichtigste zu überprüfen, nämlich ob der Wert des Feldes USt-Anteil2 überhaupt in einer der Spalten Soll/Haben1 vorhanden ist.
Wenn er das nicht ist, dann stimmt sowieso schon mal etwas nicht (aus Sicht des Anwenders und dessen Interesses). Im nächsten Schritt könnte er dann die "guten" (Datumsfelder stimmen zusätzlich überein) von den "dubiosen" (Datumsfelder stimmen nicht mehr überein) aussieben und soweiter.

Allerdings suche ich gerade nach einer konzeptionell vernünftigen Darstellungsweise des eben geschilderten unter der Bedingung, dass immer noch ersichtlich sein soll, welche WAHREN IDs1 zur korrespondierenden Such ID2 eben TRUE sind.

Any ideas?

P.S.: Und könnte man das nicht vielleicht auch noch irgendwie unter Zuhilfenahme von IN GROUP lösen?

:eek:

ebs17
23.03.2009, 20:17
Wenn man nicht beschreiben kann, wie man von einer definierten Ausgangssituation zu einer definierten Zielsituation kommen kann, ist eine Einzellösung praktisch nicht möglich. Dann kann man sich nur von Zwischenschritt zu Zwischenschritt durchhangeln.
Wenn man Regeln und Bearbeitungsabläufe nicht beschreiben kann, lässt sich keine vernünftige Programmroutine erstellen.

Du kannst wie beschrieben verfahren, aber der Sinn eines Programms, einem Bediener Routinearbeit abzunehmen, wird unbefriedigend erfüllt. Die Vernunftfrage dessen muss jeder für sich beantworten.

CCC01
23.03.2009, 20:31
Du kannst wie beschrieben verfahren, aber der Sinn eines Programms, einem Bediener Routinearbeit abzunehmen, wird unbefriedigend erfüllt. Die Vernunftfrage dessen muss jeder für sich beantworten.Ähm, dazu müsstest du den wirklichen Hintergrund dieses Problems kennen, allerdings ist dieser hier in Schriftform wirklich nur sehr langwierig zu erklären. Die von mir oben angedachte Umsetzung würde schon eine erhebliche Arbeitserleichterung verglichen mit dem bisherigen manuellen Vorgehen erbringen. Doch letztlich denke ich nicht, dass es überhaupt möglich wäre für das Problem eine vollautomatische Lösung zu entwickeln, da das Problem aus menschlichen Fehlern während der Buchhaltung resultiert und diese dem Menschen gleich so unlogisch sein können, dass es schwer fallen dürfte einem Programm geeignete Vorgaben zu machen um diese erkennen und entsprechend deuten zu können. Ich werd mich jedenfalls mal etwas mit SQL auseinandersetzen damit ich etwas mehr von der Grundlage verstehe, falls aber noch jemand weiteren Input haben sollte wäre ich sehr dankbar dafür, andernfalls werde ich nach etwas Studium der Materie versuchen meine eigenen Ideen einzubringen und dabei sicherlich auf Probleme stoßen, die ich dann hier zur Diskussion stellen würde...

ebs17
24.03.2009, 09:48
Ich halte es für keinen guten Stil, erst im Beitrag #55 anzudeuten, worum es eigentlich geht: Es geht augenscheinlich nicht um einen planmäßigen Ablauf, sondern um das Ausbügeln von "Mist", und das relativ planlos bzw. intuitiv.

Hier nützen feste Auswertungen vermutlich gar nichts. Besser wäre eine Zusammenstellung der Kriterien in der WHERE-Klausel der SQL-Anweisung nach individueller Wahl, das ist aber dann eher eine Datenbankaufgabe (Bsp.-DB Suchen in DBWiki (http://www.dbwiki.de/wiki.php?title=Access_Beispieldatenbanken))

Da ich Aufgabe und Hintergrund aber nicht verstehen kann (wie mir attestiert wird), setze ich mich auch nicht weiter damit auseinander.

CCC01
24.03.2009, 10:47
Und ich verstehe gerade nicht worin der Grund der nun angespannten Stimmung liegt??!!??

Ich versuch einfach nochmal zu erläutern wie mein letzter Beitrag zu verstehen ist.

Ähm, dazu müsstest du den wirklichen Hintergrund dieses Problems kennen, allerdings ist dieser hier in Schriftform wirklich nur sehr langwierig zu erklären.Dann werde ich doch versuchen es darzustellen, allerdings in etwas gekürzter Form. Unternehmen/Personen deren geschäftliche Tätigkeiten der Umsatzsteuerpflicht unterliegen müssen dem Finanzamt vierteljärlich Meldung über die Höhe ihrer umsatzsteuerpflichtigen Erlöse machen und dann eine Zahlung in dieser Höhe abführen. Am Ende des Jahres wird dann abgerechnet indem, die gezogene Vorsteuer mit der USt-Schuld von Amts wegen abgerechnet wird und ggf. eine Erstattung oder Nachzahlung fällig wird.
Soweit so gut, nun aber zu dem Fall einer Betriebsprüfung, bei der dann eben u.a. geprüft wird, ob im Rahmen der USt alles ordnungsgemäß gemeldet wurde, da sich hier ja enormes Potential für Betrug befindet. Dies wird dann auf folgendem Weg geprüft:
Man nehme die diversen (diverse Aufgrund unterschiedlicher USt-Sätze, besdonderheiten bei Auslandstransaktionen, etc) USt-Erlöskonten (sprich die Erlöse mit denen gleichzeitig USt vereinnahmt wurde) und errechnet daraus, die jeweils vereinnahmte USt bildet die Summe, die sich dann auf dem korrespondierenden USt-Konto befinden muss. Da ja bei Einnahme eines Ust-pflichtigen Erlöses folgendermaßen gebucht wird:
Bank 100€ an Umsatzerlöse 100€
Umsatzerlöse 15,97€ an USt-Schuld 19% 15,97€
Im Normalfall kann man diese beiden Buchungssätze auch zu einem einzigen zusammenfassen aber zwecks besserer Verständlichkeit halte ich diese Darstellungsweise für geeigneter. Das Ergebnis kann dann mit den Voranmeldungen beim FA verglichen werden und sollte tunlichst mit diesen übereinstimmen. Dies ist aber gerade bei der Abwicklung von toten Gesellschaften (Insolvenzen) häufig nicht der Fall! Man stelle sich nun vor, dass man 3-4 Erlöskonten habe auf denen jeweils 500-1000 Geschäftvorfälle gebucht wurden, die sich eigentlich dann auch auf dem korrespondierenden USt Konto wiederfinden sollten. Doch wenn hier aus diversen Gründen die Summen nicht aufgehen, dann bleibt einem letztlich nichts anderes übrig, als Geschäftsvorfall für Geschäftsvorfall dahingehend zu prüfen, ob dieser richtig erfasst wurde. Und eben hier sollte diese Excellösung ansetzen. Aber die Fehlerquellen sind eben äußerst vielfältiger Natur, da es sein kann, dass

Ein Ust-pflichtiger Erlös nicht berücksichtigt wurde, sprich sich die in ihm enthaltene USt nicht verbucht wurde
Fehler beim Auswahl des richtigen Steuersatzes gemacht wurden
Erlöse fälschlicherweise doppelt berücksichtigt wurden
Tipfehler bei manueller Buchung der USt den Betrag nicht korrekt auf dem USt-Konto wiederspiegeln
Bei der Eingabe von Belegfeld und/oder datum schlampig gearbeitet wurde und fehlerhafte Einträge vorliegen
und noch einige ähnliche aber weniger wichtige Sachverhalte

Dann hat man das gleiche Spiel nocheinmal spiegelverkehrt bei der vereinnahmten Vorsteuer. Und nun beachte man nochmals die Anzahl der zu überprüfenden Datensätze um festzustellen, dass das in der Praxis in vielen Kanzleien immer noch häufig verwendete Verfahren sich die entrsprechenden Kontoblätter auszudrucken und dann Buchung für Buchung durchzugehen, durchzustreichen bis dann die unklaren/unkorrekten Buchung übrig bleiben ziemlich ressourcenverschwendend ist.

So ich hoffe ich konnte die Situation halbwegs schildern und du verstehst mich nicht falsch und bist wieder im Boot

ebs17
26.03.2009, 12:33
Wenn Du auf eine Fortführung des Themas meinerseits bzw. des von mir vorgeschlagenen Weges (andere sind nicht undenkbar) wartest:

Die grundlegende Technik hatte ich genannt: Wenn Du mittels einer bestimmten SQL-Anweisung eine bestimmte Ansicht in einem Arbeitsblatt erzeugen kannst, so kannst Du das beliebig in Varianten wiederholen. Das Logik- und Ablaufmodell musst Du schon selber entwickeln.

Bei einem iterativen Herangehen würde ich die Kriterien zur Auswahl aber nicht nicht "fest" im Code verwenden, sondern diese Kriterien dynamisch aus einer Auswahl heraus, die über ein Formular bereitgestellt wird, zusammenstellen und verarbeiten. Das ist in der bereits dargestellten DB dargestellt. Das Genannte ist eine in einer DB durchaus typische Aufgabenstellung, du kannst es aber sicher auch in Excel entsprechend nachstellen.

Zusatz: SQL-Anweisungen sind nicht nur auf das Anzeigen beschränkt, man kann auch zusammenfassen, summieren, gruppieren, filtern, aktualisieren ... (... wenn man einen Plan hat).