Paare in zwei Excelspalten finden und löschen

Computer: Software & Programmierung Office Software
#1

Hallo,

ich habe mal wieder ein Excelproblem.

Aus der Buchhaltung erhalte ich eine Aufstellung mit Soll- und Habenbuchungen.
Wir haben jeweils 2 Spalten. Es steht immer entweder in der A Spalte ein Betrag und B ist leer, oder es ist A leer und in B steht der Betrag.
Nun sollen A und B durchsucht werden und Paare gefunden werden. Diese sollen dann gelöscht werden.

Beispiel
(Spalte A1 - A9 / Spalte B1-B9 leer)
10
10
12
16
12
20
15
10
16

(Spalte B10-B18 / Spalte A10-A18 leer)
12
16
17
15
10
10
10
10
16

Es müsste also gelöscht werden:
A1 und B14
A2 und B15
A3 und B10
A4 und B11
A7 und B13
A8 und B16
A9 und B18

Die restlichen Zahlen würden stehen bleiben.
Ist das möglich und wenn ja, habt Ihr eine Lösung?

Danke und viele Grüße

Christian

#2

Ich würde den Buchhalter entlassen, wenn er mir so einen Mist liefert.

#3

sehr hilfreich. danke. sonst nichts zu tun?

#4

Doch, ich habe jede Menge Schriftsätze für das Arbeitsgericht zu schreiben, weil ich die halbe Buchhaltung rausgeworfen habe.

2 Like
#5

Hallo,

ohne gleich den Buchhalter entlassen zu wollen, würde ich gerne erst einmal eruieren, ob es sich wirklich um ein Excelthema handelt. Geht es eventuell darum, durchlaufende Posten zu eliminieren? Dann kann ich von so einer automatisierten Vorgehensweise nur abraten, weil es da durchaus zu folgenschweren Fehlern kommen kann, z.B. wenn bei verschiedenen Buchungen gleiche Beträge vorkommen und das möglicherweise nur auf einer Seite der einen Buchung (zusammengesetzter Buchungssatz).

Gruß
C.

#6

Wozu sollte das denn gut sein?

Abgesehen davon, dass dann zwei Buchungen mit gleichem Betrag gelöscht werden könnten - warum in aller Welt sollte man denn Buchungen löschen? Was ist mit den entsprechenden Rechnungsnummern? Wo ist die Buchungsnummer überhaupt in der Tabelle? Was genau hindert einen daran, positive und negative Beträge zu buchen und für eine Übersicht einfach die Summe zu bilden?

#7

Danke, dass ihr euch solche Sorgen um die Buchhaltung macht.
Nehmt es doch einfach als Excelproblem das gelöst werden soll.

Übrigens sind Kontenexporte aus Buchhaltungen immer so aufgebaut, dass natürlich nur in einer Spalte Zahlen stehen.
Und ja, es soll die Menge an Zeilen von Forderungsbuchungen reduziert werden, um eine Tabelle mit 220.000 Zeilen auf die wesentlichen zu reduzieren um durch eine Plausibilitätsprüfung das Saldo einzugrenzen. Und nein, es gibt keine Möglichkeit 220.000 Buchungen miteinander auszugleichen und genau zuzuordnen, da die Ausgangsdaten dafür nicht ausreichen.

#8

Das wird so nichts, so bekommst du doch nie eine vernünftige Debitorenaufstellung hin. Das muss die Buchhaltung bereinigen, also schön Debitor für Debitor ausziffern, was sich nicht mehr zuordnen läßt, weil zu alt oder zu schlampig, das bucht man dann weg. Damit schafft man einen Ausgangsstand, und von da an wird ab sofort sauber gearbeitet. Offene Posten werden nicht mehr über Jahre mitgeschleppt, sondern es wird regelmäßig (monatlich, mindest aber quartalsweise) komplett ausgeziffert. Alles andere ist Murks.

Übrigens sagen Buchhalter dazu immer dasselbe, der eine Satz lautet „Das schaffe ich nicht in der vorgegebenen Zeit!“, der andere Satz lautet „Aber wenn mir der Mandant/ die Abteilung doch immer zu spät oder gar nicht die Daten liefert!“. Da muss man in den Konflikt gehen, bei Satz 1 mit dem Buchhalter, bei Satz 2 mit dem Mandanten bzw. der Abteilung.

1 Like
#9

Wow. Lest Ihr gerne Eure eigenen Beiträge? Und habt Langeweile? Ihr bringt Fragen auf die keiner wissen will und die keinen interessieren, aber ignoriert die gestellte Frage im Beitrag, weil Ihr davon keine Ahnung habt. Einfach mal nichts schreiben wäre klasse.
Früher wurden hier Probleme gelöst. Das Forum scheint dies wohl nicht mehr zu leisten. Schade.

#10

Hi,

mit welchem Buchhaltungsprogramm arbeitet ihr denn? DATEV? Da gibt es die Option Ausziffern, die das hilfreich lösen könnte.

Viele Grüße
Karin

#11

Hallo,
muß die Lösung zwingend in Excel sein? Mit R und data.table wäre es kein Problem (fsetdiff).

Man kann das Vorgehen ungefähr so beschreiben: Bilde die Multimengendifferenz zwischen A und B, vereinige mit der Multimengendifferenz zwischen B und A.

Oder algorithmisch:

  • A <- Sortiere(A), B <- Sortiere(B)
  • i <- 0, j <- 0
  • Falls A[i] = B[j], inkrementiere i, inkrementiere j
  • Ansonsten: Schreibe min(A[i], B[j]) raus, inkrementiere i, falls A[i] < B[j], anderenfalls inkrementiere j
#12

Hallo,
mit welcher Info, die Du uns noch sagst,
findet Excel, dass
A2 und B15 gilt
und nicht
A2 und B14
A1 und B15
A2 und B15, B16, oder B17

#13

Ist doch egal in welcher Reihenfolge gelöscht wird. Er will eine Mengendifferenz, sprich die Elemente, die nicht im Schnit liegen.

#14

Hallo Christian,

wenn ich das machen müsste, würde ich es wohl so realisieren:

Neue Spalte C, in der 1+b addiert wird
Nach Spalte C sortieren
Die Differenz (also Kontostand) an einer Zelle ausgeben und dies in einer extra Hilfsspalte in jede Zeile eingeben. Wenn sich dieser Wert ändert, Warnung ausgeben (bedingte Formatierung), weil dann der Kontostand verändert wurde.

Dann manuell löschen.

Gruß
Karin

#15

Hallo Karin,

Danke. Aber deine Lösung verstehe ich leider nicht.
Auch haben wir hier 220.000 Zeilen. Da geht nichts mit manuell löschen.

Und ja, es ist ein Datevexport.
Ausziffern geht leider nicht, da die Daten das nicht hergeben.
Und nein. Ihr müsst Euch keine Gedanken machen, dass wir nicht wissen, was wir mit den Buchhaltungen machen. Ich bin Steuerberater und seit Jahren auf Onlinehändler spezialisiert. Dieser hier hat aber leider nun so viele Buchungen und so wirre Daten, dass wir uns nur über Plaubilitätsprüfungen und nicht über direkte Ausgleiche helfen können. Alles andere will und kann er nicht bezahlen.
Übrigens braucht Datev da alleine für das öffnen des Datenblatts 20 Sekunden und für den Export 5min. Da geht über die SQL Datenbank nichts.

@ hroptatyr
danke für den Ansatz. Wir werden mal schauen, ob wir das mit data.table hinbekommen.

Danke

Christian

#16

Hier das vollständige R Skript mit Deinem Beispiel (würde man vermutlich sonst via read_excel or fread einlesen)

library(data.table)
A <- data.table(c(10,10,12,16,12,20,15,10,16))
B <- data.table(c(12,16,17,15,10,10,10,10,16))
## Überbleibsel in A: 12, 20
fsetdiff(A, B, all=T)
## Überbleibsel in B: 17, 10
fsetdiff(B, A, all=T)
#17
  1. Oben leere Zeile einfügen - die Daten sind in Spalte A und B, und fangen in A2 an.
  2. In C2: =ZÄHLENWENN(A:A;A2)-ZÄHLENWENN(B:B;A2)
  3. In D2: =ZÄHLENWENN(B:B;B2)-ZÄHLENWENN(A:A;B2)
  4. In E2: =WENN(C2>0;ZÄHLENWENN(A:A;A2)-ZÄHLENWENN(A$1:A1;A2);0)
  5. In F2: =WENN(D2>0;ZÄHLENWENN(B:B;B2)-ZÄHLENWENN(B$1:B1;B2);0) (Oder von E2 kopieren)
  6. In G2: =WENN(E2+F2=0;0;WENN(E2>C2;0;WENN(F2>D2;0;1)))
  7. C2 bis G2 nach unten kopieren
  8. Nach Spalte G sortieren
  9. Alle Zeilen mit Wert=0 in Spalte G löschen

Vielleicht nicht die eleganteste Lösung, aber sollte funktionieren.

#18

Hallo,

C1 (Differenzmenge A/B):

=WENN(ZÄHLENWENN(A$1:A1;A1)>ZÄHLENWENN(B:B;A1);A1;"")

D1 (Differenzmenge B/A):

=WENN(ZÄHLENWENN(B$1:B1;B1)>ZÄHLENWENN(A:A;B1);B1;"")

E1 (saubere Zusammenfassung Spalte C und D):

=WENN(ZEILE()>ANZAHL(C:D);"";KKLEINSTE(C:D;ZEILE()))

Alle 3 Formeln einfach nach unten erweitern.

Sollte die Formel aus E1 nicht in der ersten Zeile sein, muss entsprechend bei ZEILE() die Anzahl der darüberligenden Zeilen abgezogen werden. Steht die Formel z.B. in Zeile 2, wäre das ZEILE()-1 , bei Zeile 10 wäre das dann ZEILE()-9

Gruß
Tobias

#19

Hallo,

mir ist gerade aufgefallen dass es natürlich Blödsinn ist Spalte C und D zusammenzufassen. Also einfach in zwei Formeln aufteilen, einmal mit C:C für Spalte C und einmal mit D:D für Spalte D.

Gruß
Tobias

#20

Wie findest du damit denn gleiche Paare?