Duplikate entfernen und Anzahlen addieren

[VodeAn]

Bevor sich jetzt jemand aufregt: Ich weiß, dass diese Frage schon an die 1000 Mal in verschiedensten Foren gestellt wurde, aber ich habe auch nach langer Suche immer noch keine Antwort finden können, die mir weitergeholfen hätte, deshalb stelle ich sie jetzt noch mal.

Folgendes Problem:

Es liegt eine lange Liste von gelieferten Produkten vor, mitsamt der Lieferadressen, Datum und Anzahl der jeweils gelieferten Produkte. Diese Liste entstand aus einer Ansammlung verschiedener Rechnungsdokumente. Leider gibt es aber Tag, an denen mehrere Lieferungen an dieselbe Adresse gingen, und in diesen Lieferungen waren teilweise die gleichen Produkte enthalten, nur mit unterschiedlicher Stückzahl. Und diese Rechnungen bilden jetzt natürlich auch je einen eigenen Eintrag in meiner Liste.

Nun soll am Ende in der Liste nur noch die Information existieren, wie viele Exemplare welches Produkts an jedem Tag zu welcher Adresse geliefert wurden. Es sollen also alle Zeilen, in denen Adresse (steht in 2 Spalten: Einmal Name des Empfängers und einmal Straße + Hausnr.), Lieferdatum und Artikelnummer gleich sind, in einer Zeile zusammengefasst werden, und die Anzahl soll der Summe der Zahlen entsprechen, die vorher in den jetzt zusammengefassten Zeilen standen. Nochmal ganz deutlich: Ich will NICHT wissen, wie viele Zeilen zusammengefasst wurden, sondern ich will wirklich eine Summe aus den Werten bilden, die vorher in diesen Zeilen standen.

Ich hänge als Beispiel mal eine Datei an. Auf der linken Seite sieht man, wie die Tabelle aussieht (zumindest im Groben und Ganzen), auf der rechten Seite das gewünschte Ergebnis.

Auf meiner Suche nach einer Lösung kamen mir immer wieder Sätze wie "Das kannst du mit ein paar Klicks über eine Pivot-Tabelle lösen" unter die Augen. Solche tollen Ratschläge helfen einem aber nicht weiter, wenn man sich (wie ich) nicht mit Pivot-Tabellen auskennt. Und meine Versuche, das Problem auf diese Weise zu lösen, haben leider nicht zum gewünschten Ergebnis geführt. Wenn einer von euch also einen Weg kennt, das Ganze mit Pivot-Tabellen zu lösen, dann würde ich darum bitten, genau zu beschreiben, wie diese Lösung aussieht.

Vielen Dank im Voraus,

VodeAn

[RPP63]

Hallo!
Das kannst du mit ein paar Klicks über eine Pivot-Tabelle lösen!
Und im Ernst:
Dies liest sich bisher (ohne die Datei gesehen zu haben) so, als wenn SUMMEWENNS() das Richtige für Dich wäre.
Ich schaue mir aber jetzt die Mappe an.

Gruß Ralf

[RPP63]

Hi!
Mal per "Hand".
1. kopiere die Spalten A:D in in neues Blatt
2. dort Daten, Duplikate entfernen (auf alle vier Spalten anwenden)
3. dann folgende Formel (hier der Einfachheit halber im selben Tabellenblatt):

	A	B	C	D	E	F	G	H	I	J	K
1	Name	Adresse	Lieferdatum	Art.-Nr.	Anzahl		Name	Adresse	Lieferdatum	Art.-Nr.	Anzahl
2	Meier	Beispielstr. 1	01.01.2016	1.1.1	5		Meier	Beispielstr. 1	42370	1.1.1	8
3	Meier	Beispielstr. 1	01.01.2016	1.1.2	5		Meier	Beispielstr. 1	42370	1.1.2	5
4	Meier	Beispielstr. 1	01.01.2016	1.1.3	4		Meier	Beispielstr. 1	42370	1.1.3	19
5	Meier	Beispielstr. 1	01.01.2016	1.1.1	3		Schulze	Beispielstr. 2	42401	1.1.1	4
6	Meier	Beispielstr. 1	01.01.2016	1.1.3	15		Schulze	Beispielstr. 2	42401	1.1.2	75
7	Schulze	Beispielstr. 2	01.02.2016	1.1.1	4		Schulze	Beispielstr. 2	42401	1.1.3	5
8	Schulze	Beispielstr. 2	01.02.2016	1.1.2	75		Schulze	Beispielstr. 2	42415	1.1.1	7
9	Schulze	Beispielstr. 2	01.02.2016	1.1.3	5		Schulze	Beispielstr. 2	42415	1.1.4	8
10	Schulze	Beispielstr. 2	15.02.2016	1.1.1	7		Müller	Beispielstr. 3	42430	1.1.1	4
11	Schulze	Beispielstr. 2	15.02.2016	1.1.4	8		Müller	Beispielstr. 3	42430	1.1.2	5
12	Müller	Beispielstr. 3	01.03.2016	1.1.1	4		Müller	Beispielstr. 3	42430	1.1.3	61
13	Müller	Beispielstr. 3	01.03.2016	1.1.2	2
14	Müller	Beispielstr. 3	01.03.2016	1.1.3	45
15	Müller	Beispielstr. 3	01.03.2016	1.1.2	3
16	Müller	Beispielstr. 3	01.03.2016	1.1.3	16

Formeln der Tabelle

Zelle Formel K2 =SUMMEWENNS(E:E;A:A;G2;B:B;H2;C:C;I2;D:D;J2)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Ralf

[VodeAn]

Ich hab einen Augenblick gebraucht, um das zu begreifen, aber ich glaube, ich hab es verstanden. Werde jetzt mal versuchen, das in meiner Produktivdatei umzusetzen.

Vielen Dank erst mal!

Gruß VodeAn

EDIT: Hat funktioniert, DANKE!

[Rabe]

Hi,

die andere Lösung funktioniert ja, aber da Du gefragt hast:

Wenn einer von euch also einen Weg kennt, das Ganze mit Pivot-Tabellen zu lösen, dann würde ich darum bitten, genau zu beschreiben, wie diese Lösung aussieht.

nun hier die Lösung mit Pivot:
anklicken der Datentabelle

1. Menü "Einfügen" - Pivot-Tabelle
   
2. ziehen von "Name", "Adresse", "Lieferdatum", "Art.-Nr." in den Zeilenbereich
   
3. ziehen von "Anzahl" in den Werte-Bereich
   
4. Rechtsklick in die Pivot-Tabelle, Auswahl von PivotTable-Optionen, Register  "Anzeige", Haken setzen bei "Klassisches PivotTable-Layout"
   
5. dann Register "Summen & Filter", 2x Haken entfernen bei  "Gesamtsummen ..."
   
6. OK drücken
   
7. Rechtsklick in Pivot-Spalte "Name", abklicken von "Teilergebnis ..."
   
8. Rechtsklick in Pivot-Spalte "Adresse", abklicken von "Teilergebnis ..."
   
9. Rechtsklick in Pivot-Spalte "Lieferdatum", abklicken von "Teilergebnis ..."
   
   

Dann sieht das Ergebnis so aus:

Tabelle1

	P	Q	R	S	T
15	Summe von Anzahl
16	Name	Adresse	Lieferdatum	Art.-Nr.	Ergebnis
17	Meier	Beispielstr. 1	01.01.2016	'1.1.1	8
18				'1.1.2	5
19				'1.1.3	19
20	Müller	Beispielstr. 3	01.03.2016	'1.1.1	4
21				'1.1.2	5
22				'1.1.3	61
23	Schulze	Beispielstr. 2	01.02.2016	'1.1.1	4
24				'1.1.2	75
25				'1.1.3	5
26			15.02.2016	'1.1.1	7
27				'1.1.4	8

Schriftart wird in dieser Tabelle nicht dargestellt

Schriftformate
Zelle	Rot	Grün	Blau	Color	Stil	Unterstreichung	Effekte	Durchgestrichen	Schriftart
P15: P27,             Q15:Q27,                          R15:R27,                                       S15:S27,                                                    T15:T27	0	0	0	0					Calibri

Zellen mit Schriftformatierung automatisch werden nicht dargestellt

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 21.07 einschl. 64 Bit

Wenn Du jetzt noch aus dem Datenbereich eine intelligente Tabelle machst (STRG-T), kann die Pivot einfach durch aktualisieren bei weiteren Lieferungen erweitert werden.
Hier Deine Datei umgearbeitet:

  Beispiel Lieferzahlen.xlsx (Größe: 15,45 KB / Downloads: 7)

[pefeu]

Hallo VodeAn,

mit VBA und nicht als Pivot so:

PHP-Code:

Option Explicit

Public Sub Zusammenfassen()

Dim MyDict   As Object  ' As Dictionary
Dim vTemp    As Variant ' ein temporärer Array
Dim lTemp    As Long    ' der For/Next Schleifen Index zum Array
Dim sText    As String  ' Zusammenfassung der ggf. noch doppelten
Dim lLetzte  As Long    ' die letzte belegte Zeile in Spalte E
Dim lZeile   As Long    ' der For/Next Schleifen-Index - hier die Zeile


   Set MyDict = CreateObject("Scripting.Dictionary")
   
'     die Eingabe-Daten aus Performance-Gründen in ein Array kopieren
   With ThisWorkbook.Worksheets("Tabelle1")  ' den Tabellenblattnamen ggf. anpassen!
      vTemp = .Range("A4:E" & .Cells(.Rows.Count, 1).End(xlUp).Row) ' ggf. anpassen!
   End With
   
'     zusammenfassen der Begriffe und addieren der Werte
   For lTemp = 1 To UBound(vTemp)
      sText = vTemp(lTemp, 1) & "##" & vTemp(lTemp, 2) & "##" & vTemp(lTemp, 3) & "##" & vTemp(lTemp, 4)
      MyDict(sText) = MyDict(sText) + Val(vTemp(lTemp, 5))
   Next lTemp
   
   Application.ScreenUpdating = False ' kein Bildschirm-Update mehr zulassen
   
'     Ausgeben. Die Zielzellen müssen ggf. angepasst werden
   With ThisWorkbook.Worksheets("Tabelle1")
      lLetzte = 4 + .Cells(.Rows.Count, 1).End(xlUp).Row
'         die im Dictionary gesammelten und addierten Werte ausgeben
      .Range("I4").Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.keys)
      .Range("M4").Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.Items)
      
      For lZeile = 4 To 4 + MyDict.Count - 1
         vTemp = Split(.Range("I" & lZeile).Value, "##")
         .Range("I" & lZeile).ClearContents
         .Range("I" & lZeile).Value = vTemp(0)
         .Range("J" & lZeile).Value = vTemp(1)
         With .Range("K" & lZeile)
            .NumberFormat = "DD.MM.YYYY"
            .Value = vTemp(2)
         End With
         .Range("L" & lZeile).Value = vTemp(3)
      Next lZeile
      
'        ab Zeile 2 bis zur letzten belegten Zeile alles einschl. D-G aufsteigend sortieren
      .Range("I4:M" & lLetzte).Sort _
         Key1:=.Range("I4"), Order1:=xlAscending, _
         Key2:=.Range("J4"), Order2:=xlAscending, _
         Key3:=.Range("K4"), Order3:=xlAscending, _
         Header:=xlNo, OrderCustom:=1, _
         MatchCase:=True, Orientation:=xlTopToBottom
         
      .Columns("I:M").EntireColumn.AutoFit
   End With
   
   Application.ScreenUpdating = True ' das Bildschirm-Update wieder zulassen

End Sub

Gruß Peter 


[pefeu]

Hallo VodeAn,

mit VBA und nicht als Pivot so:

PHP-Code:

Option Explicit

Public Sub Zusammenfassen()

Dim MyDict   As Object  ' As Dictionary
Dim vTemp    As Variant ' ein temporärer Array
Dim lTemp    As Long    ' der For/Next Schleifen Index zum Array
Dim sText    As String  ' Zusammenfassung der ggf. noch doppelten
Dim lLetzte  As Long    ' die letzte belegte Zeile in Spalte E
Dim lZeile   As Long    ' der For/Next Schleifen-Index - hier die Zeile


   Set MyDict = CreateObject("Scripting.Dictionary")
   
'     die Eingabe-Daten aus Performance-Gründen in ein Array kopieren
   With ThisWorkbook.Worksheets("Tabelle1")  ' den Tabellenblattnamen ggf. anpassen!
      vTemp = .Range("A4:E" & .Cells(.Rows.Count, 1).End(xlUp).Row) ' ggf. anpassen!
   End With
   
'     zusammenfassen der Begriffe und addieren der Werte
   For lTemp = 1 To UBound(vTemp)
      sText = vTemp(lTemp, 1) & "##" & vTemp(lTemp, 2) & "##" & vTemp(lTemp, 3) & "##" & vTemp(lTemp, 4)
      MyDict(sText) = MyDict(sText) + Val(vTemp(lTemp, 5))
   Next lTemp
   
   Application.ScreenUpdating = False ' kein Bildschirm-Update mehr zulassen
   
'     Ausgeben. Die Zielzellen müssen ggf. angepasst werden
   With ThisWorkbook.Worksheets("Tabelle1")
      lLetzte = 4 + .Cells(.Rows.Count, 1).End(xlUp).Row
'         die im Dictionary gesammelten und addierten Werte ausgeben
      .Range("I4").Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.keys)
      .Range("M4").Resize(MyDict.Count) = WorksheetFunction.Transpose(MyDict.Items)
      
      For lZeile = 4 To 4 + MyDict.Count - 1
         vTemp = Split(.Range("I" & lZeile).Value, "##")
         .Range("I" & lZeile).ClearContents
         .Range("I" & lZeile).Value = vTemp(0)
         .Range("J" & lZeile).Value = vTemp(1)
         With .Range("K" & lZeile)
            .NumberFormat = "DD.MM.YYYY"
            .Value = vTemp(2)
         End With
         .Range("L" & lZeile).Value = vTemp(3)
      Next lZeile
      
'        ab Zeile 2 bis zur letzten belegten Zeile alles einschl. D-G aufsteigend sortieren
      .Range("I4:M" & lLetzte).Sort _
         Key1:=.Range("I4"), Order1:=xlAscending, _
         Key2:=.Range("J4"), Order2:=xlAscending, _
         Key3:=.Range("K4"), Order3:=xlAscending, _
         Header:=xlNo, OrderCustom:=1, _
         MatchCase:=True, Orientation:=xlTopToBottom
         
      .Columns("I:M").EntireColumn.AutoFit
   End With
   
   Application.ScreenUpdating = True ' das Bildschirm-Update wieder zulassen

End Sub

Gruß Peter