Doppelte Zeilen löschen und Beträge zusammenfassen

Forumthread: Doppelte Zeilen löschen und Beträge zusammenfassen

27.02.2025 14:42:17

Christian

Hallo !

Ich habe eine Exceldatei mit ca. 4000 - 6000 Zeile bei denen Polizzennummern und Versicherungsprämien unteranderem vorhanden sind.

Spalte A = Polizzennummern
Spalte B = Versicherungsprämien

Nun kommen in dieser gesamten Datei viele Polizzennummern doppelt vor, weil die Versicherung jeweils zu einer Polizzennummern, zweimal eine unterschiedliche Prämie verrechnen musste.

Meine Aufgabe ist es nun die doppelten Polizzennummern, welche ich mittels bedingter Formatierung und auch einer Sortierung untereinander sortiert habe,
die Versicherungsprämien aus Spalte B zusammenzuzählen. Dieses Ergebnis wird in einer der beiden doppelten Zeilen (egal welche) dann in Spalte B bei der Versicherungsprämie dieser Pol.Nr eingefügt und die zweite doppelte Zeile dann gelöscht.

Händisch ist das kein Problem, weil ich da die Versicherungsprämien der beiden doppelten Zeilen markiere, mir rechts untern in der Leiste die Summe anzeigen lasse, dies dann in einer der beiden Zeile händisch eintrage und dann die andere doppelte Zeile lösche.

Aber bei insgesamt ca. 4000 - 6000 Zeilen ist das händisch eine katastrophale Arbeit :(

Kann man das irgendwie mit Formeln oder Funktionen schneller bewältigen?
Anbei eine Beispieldatei.
https://www.herber.de/bbs/user/176028.xlsx

LG

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Schon eine Pivot-Tabelle versucht? owT

27.02.2025 14:48:22

Fennek

AW: Doppelte Zeilen löschen und Beträge zusammenfassen

27.02.2025 15:02:29

UweD

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	Polnr	Prämien			Polnr	Prämien
2	1084574659	-189,97			1084574659	13,28
3	1084574659	203,25			1084574864	13,27
4	1084574864	-189,98			1084580120	13,27
5	1084574864	203,25			1084580240	-276,53
6	1084580120	-189,98			1084580325	13,28
7	1084580120	203,25			1084580433	13,27
8	1084580240	-189,98			1084580671	13,28
9	1084580240	203,25			1084580780	13,28
10	1084580240	-203,25			1084580800	13,28
11	1084580240	189,98			1084580984	13,28
12	1084580240	-276,53			1084581202	-272,3

Zelle	Formel
E2	=EINDEUTIG(FILTER(A:A;(A:A>"")*(ZEILE(A:A)>1)))
F2	=SUMMEWENNS(B:B;A:A;E2#)

Formeln sind selbsterweiternd

LG UweD

AW: Doppelte Zeilen löschen und Beträge zusammenfassen

27.02.2025 19:10:58

Daniel

Hi
bei großen Datenmengen für eine einmalige Umwandlung der Tabelle am besten so:

1. Liste nach Pol.Nummer sortieren, so das gleiche Pol.Nummern direkt untereinander stehen
2. in der ersten freien Spalte (Spalte C im Beispiel) folgende Formel in die erste Datenzeile einfügen und nach unten zum Datenende ziehen:
=B2+Wenn(A2=A3;C3;0)
diese Formel summiert die Prämien in Spalte B für jede Zeile nach oben auf, so dass immer in der obersten Zeile eine Pol.Nummerngruppe die Gesamtsumme steht.
3. Spalte C kopieren und als Wert einfügen
4. auf die ganze Tabelle die Funktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN mit Spalte A (Pol.Nummern) als Kriterium anwenden
5. ggf die Spalte B löschen

das Duplikate Entfernen lässt von von mehrfach vorkommenden Werten immer die erste Zeile stehen. In der ersten Spalte hat deine Formel aus 2. die gesamtsumme erstellt.

ohne Umsortieren geht es so:
1. in einer freien Spalte in die erste Datenzeile die Formel: =SummeWenns(B:B;A:A;A2) und nach unten ziehen
2. Spalte kopieren und als Wert einfügen
3. Duplikate Entfernen ausführen mit Spalte A als Kriterium.

bei 6000 Zeilen sollte die zweite Methode noch ausreichend schnell sein.
sollten es mal 60.000 oder 600.000 Zeilen sein, dann die erste Methode anwenden.

Gruß Daniel

AW: Doppelte Zeilen löschen und Beträge zusammenfassen

27.02.2025 15:43:54

Christian

Hallo @UweD

Herzlichen Dank für die zwei Formeln !
Genau das hab ich benötigt.

Nun habe ich ein zweites Zusatzproblem, an das ich nicht gedacht habe.

Ich habe ja grundsätzlich zu jeder Polizzennummer die ja eigentlich ursprünglich doppelt sind und zwei verschiedene Versicherungsprämien in zwei verschiedenen Zeilen,
auch in der selben Zeile zugeordnete Kennzeichen, Fahrzeugart, Marke, Typ, Fahrgestellnummer, Zeitraum für Versicherungsprämie und um welche Firma es sich handelt.

Mit deiner Formel wird zwar genau das gemacht grundsätzlich was ich möchte, aber nach diesem "zusammenstauchen" wird ja quasi nur in zwei separaten neuen Spalten die Polizzennummer und die Versicherungsprämie zusammengestaucht/zusammengefasst.
Die Zeilen im Exceltabellenblatt werden dadurch nicht gelöscht.

Somit bleiben die ursprünglichen ca. 4000 Zeilen bestehen nur die Pol.Nr. und Prämien werden in zwei neuen Spalten zu insgesamt ca. 2000 Zeilen zusammengestaucht.
Dadurch verschiebt sich nun auch die Reihenfolge der betroffenen Kennzeichen etc., da die Reihenfolge mit den neuen 2000 Kennzeichen nun nicht mehr zusammenpasst :(

Wie kann man das nun lösen? Ich hab an diesem Zusatzproblem nicht von Anfang an gedacht, da mit der Formel ja keine Zeilen gelöscht werden sondern nur die Ergebnisse in zwei neuen Spalten zusammengefasst werden.

LG,
Christian

AW: Doppelte Zeilen löschen und Beträge zusammenfassen

27.02.2025 15:54:22

UweD

HAllo

Dann ergänze in deiner Tabelle mal solche Infos und lade neu hoch

Sind die Zusatzinfos bei Policengleichheit unterschiedlich?
Sollen die dann in mehreren Spalten hintereinander?
...

AW: Doppelte Zeilen löschen und Beträge zusammenfassen

27.02.2025 16:03:16

Christian

Genau, alle anderen Zusatzinfos in den restlichen Spalten sind bei Polizzengleichheit absolut gleich, bis auf die Versicherungsprämie.

Das Problem ist, dass ich diese Zusatzinformationen in der Tabelle nicht ergänzen möchte aus Datenschutzgründen, weil Fahrgestellnummer des Fahrzeugs, Kennzeichen, Zeitraum und Firmenname leider sensible Firmendaten sind :(

Kannst du bzw. könnt ihr mir weiterhelfen, ohne das ich die Daten ergänze?

AW: Doppelte Zeilen löschen und Beträge zusammenfassen

27.02.2025 16:34:54

UweD

Hallo

dann die eine Formel noch zusätzlich

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Polnr	Prämien	FIN	Kennzeichen	Zeitraum	Halter	usw					Polnr	Prämien	FIN	Kennzeichen	Zeitraum	Halter	usw
2	1084574659	-189,97	A123	B AA 123	2024-2025	Mr X	aaa					1084574659	13,28	A123	B AA 123	2024-2025	Mr X	aaa
3	1084574659	203,25	A123	B AA 123	2024-2025	Mr X	aaa					1084574864	13,27	Z2233	D AB 999	2023-2026	Sie	lll
4	1084574864	-189,98	Z2233	D AB 999	2023-2026	Sie	lll					1084580120	13,27	333	S UU 765	2022-2024	Er	111
5	1084574864	203,25	Z2233	D AB 999	2023-2026	Sie	lll					1084580240	-276,53	K999999	M TW 100	2021-2024	Du	ÄÄÄ
6	1084580120	-189,98	333	S UU 765	2022-2024	Er	111					1084580325	13,28	0	0	0	0	0
7	1084580120	203,25	333	S UU 765	2022-2024	Er	111					1084580433	13,27	0	0	0	0	0
8	1084580240	-189,98	K999999	M TW 100	2021-2024	Du	ÄÄÄ					1084580671	13,28	0	0	0	0	0
9	1084580240	203,25	K999999	M TW 100	2021-2024	Du	ÄÄÄ					1084580780	13,28	0	0	0	0	0

Zelle	Formel
L1	=A1
L2	=EINDEUTIG(FILTER(A:A;(A:A>"")*(ZEILE(A:A)>1)))
M2	=SUMMEWENNS(B:B;A:A;L2#)
N2	=SVERWEIS($L2#;$A:$G;SPALTE(C2);0)

N2 nach rechts kopieren

Danach kannst du
- die kompletten Spalten L:R kopieren,
- dann A markieren
- und Inhate Einfügen, Werte

LG UweD

AW: Doppelte Zeilen löschen und Beträge zusammenfassen

28.02.2025 13:13:39

Christian

Herzlichen Dank, @UweD !

Ich konnte deine Lösung sehr gut umsetzen und mein Problem damit vollständig lösen !
Vielen Dank ! War eine super Hilfe !

LG, Christian

AW: Doppelte Zeilen löschen und Beträge zusammenfassen

27.02.2025 16:03:04

Yal

Hallo Christian,

Formel geben zwar ein Gefühl von Beherrschung über der Daten, aber mit steigender Komplexität hast Du sehr schnell eine unüberschaubare Gewerk.
Frage vom Chef: "Haben Sie alle Ihre Formel geprüft? Kann ich diese Zahl vertrauen?"

- Gehe auf die erste Zelle deiner Liste (die wohl mehr als nur die 2 Spalten deiner Beispieldatei),
- drücke Strg+t (alternativ: Menü "Einfügen", "Tabelle")
- in dem auftretenden Menü "Tabellenentwurf", gehe auf "mit Pivottable zusammenfassen",
- verschiebe den Feld "PolNr" aus der Feldliste oben recht ins Quadrant "Zeilen",
- verschiebe den Feld "Prämien" vom Feldliste ins Quadrant "Werte", es wird zu eine "Summe von ..."
- verschiebe nochmal den Feld "Prämien" in "Werte", klicke aus diese zweite Kennzahl und wähle "Anzahl" als Aggregationsformel.
- usw.

- gehe auf eine Wert auf die Pivottabelle und doppelklick drauf. Was passiert? Du bekommst alle Details über die Eingangszeilen für diese Summe/Anzahl.

Das waren jetzt 3 Minuten. Und es war unabhängig von der Anzahl von Spalten in deine Daten.
Versuch mal dasselbe mit Formeln hinzubekommen... Und denk dabei an den Fragen deines Chefs.

Du wirst sicher in deinem Leben viel mit Formel machen müssen und diese auswendig lernen, aber wer sich davor mit quadratisch-praktisch-guten Werkzeug wie Pivottable auseinander gesetzt hat, vermeidet sehr viel unnötige Murks.

VG
Yal

AW: Schon eine Pivot-Tabelle versucht? owT

27.02.2025 14:50:01

Christian

Ich wüsste nicht wie das mittels Pivot Tabelle geht :(

Würde dazu eine detaillierte Anleitung brauchen :/ Habe nur Basiskenntnisse bzw. mittelmäßige Kenntnisse und Pivot bisher nie benutzt.

Pivot- Anleitung/Tutorial

27.02.2025 15:31:43

Yal

Hallo Christian,

solche Anleitung gibt es als Video wie Sand am Meer.

Aufgrund meiner guten Erfahrung mit Tutorial von Daniel Kogan aka Excelhero: https://excelhero.de/pivot-tabellen/pivot-tabellen-ganz-einfach-erklaert/

VG
Yal

Doppelte Zeilen löschen und Beträge zusammenfassen

Forumthread: Doppelte Zeilen löschen und Beträge zusammenfassen

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Doppelte Zeilen löschen und Beträge zusammenfassen

Forumthread: Doppelte Zeilen löschen und Beträge zusammenfassen

11 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

11
Beiträge zum Forumthread

Beiträge zu diesem Forumthread