Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Daten Sortieren und Duplikate addieren
#1
Hallo Liebes Forum,

ich habe folgendes Problem.
Ich habe zwei Datensätze, zu je vier Spalten und einigen Zeilen, in etwa so:

Datensatz 1:
A B C D
5 1 1 1
5 2 1 1
5 3 1 1
5 4 1 1

Datensatz 2:
E F G H
5 -2 1 1
5 -1 1 1
5 0 1 1
5 1 1 1

Jetzt möchte ich, dass Excel mit daraus eine viespaltige Tabelle macht, in der alle Daten vorhanden sind. Sollte allerdings der Fall auftreten, dass die Werte der Spalten BCD und FGH gleich sind, sollen die Werte der zugehörigen Spalte A und E addiert werden. (siehe Fett markiert).

Als Ergebnis sollte in diesem einfachen Beispiel dann folgendes rauskommen:

I J K L
5 -2 1 1
5 -1 1 1
5 0 1 1
10 1 1 1
5 2 1 1
5 3 1 1
5 4 1 1

ich habe leider überhaupt keine Ahnung wie das geht, und hoffe mir kann jemand helfen.

Beste Grüße, Bob
to top
#2
Hi Bob,

hier ein Lösungsvorschlag mit Hilfsspalten, die du natürlich ausblenden kannst.

Tabelle1

ABCDEFGHIJ
1511151115-215-21
2521152115-115-11
35311531150115011
45411541151115111
510111
65211
75311
85411
Formeln der Tabelle
ZelleFormel
E1=A1&B1&C1&D1
J1=F1&G1&H1&I1
E2=A2&B2&C2&D2
J2=F2&G2&H2&I2
E3=A3&B3&C3&D3
J3=F3&G3&H3&I3
E4=A4&B4&C4&D4
J4=F4&G4&H4&I4
F5=WENN(ZÄHLENWENN($J$1:$J$4;E1)=1;A1*2;A1)
G5=WENN(F5<>"";$B1;"")
H5=WENN(G5<>"";C1;"")
I5=WENN(H5<>"";D1;"")
F6=WENN(ZÄHLENWENN($J$1:$J$4;E2)=1;A2*2;A2)
G6=WENN(F6<>"";$B2;"")
H6=WENN(G6<>"";C2;"")
I6=WENN(H6<>"";D2;"")
F7=WENN(ZÄHLENWENN($J$1:$J$4;E3)=1;A3*2;A3)
G7=WENN(F7<>"";$B3;"")
H7=WENN(G7<>"";C3;"")
I7=WENN(H7<>"";D3;"")
F8=WENN(ZÄHLENWENN($J$1:$J$4;E4)=1;A4*2;A4)
G8=WENN(F8<>"";$B4;"")
H8=WENN(G8<>"";C4;"")
I8=WENN(H8<>"";D4;"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

In den Hilfsspalten erzeugst du einen zusammenhängenden und dadurch vergleichbaren Ausdruck. Unterhalb deines zweiten Blocks fügst du dann die Formeln ein und ziehst sie runter.
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#3
Hallo Günter

Ich glaube die Minuszeichen sind Vorzeichen und keine Platzhalter.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
to top
#4
(18.02.2015, 06:31)shift-del schrieb: Ich glaube die Minuszeichen sind Vorzeichen und keine Platzhalter.

Hallo Detlef,

das habe ich tatsächlich übersehen - danke für den Hinweis. Mein Vorschlag funktioniert aber auch mit den Minuszahlen.
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#5
Hallo,

hier eine VBA Lösung:

Code:
Option Explicit

Sub mach()
  Dim i As Long, j As Long, k As Long
  Dim lngZ_A As Long, lngZ_E As Long
  Dim feld
  Dim arr()
  Dim varKey
  Dim objDic As Object
  
  
  With Tabelle1
    lngZ_A = .Cells(.Rows.Count, 1).End(xlUp).Row
    lngZ_E = .Cells(.Rows.Count, 5).End(xlUp).Row
    .Range("A2:D" & lngZ_A).Copy .Range("I2")
    .Range("E2:H" & lngZ_E).Copy .Range("I" & lngZ_A + 1)
    .Range("I1:L" & lngZ_A + lngZ_E - 1).Select
    .Range("I2:L" & lngZ_A + lngZ_E - 1).Sort Key1:=.Range("J1"), Order1:=xlAscending, Key2:=.Range("I1") _
        , Order2:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    feld = .Range("I2:L" & lngZ_A + lngZ_E - 1)
       Set objDic = CreateObject("Scripting.Dictionary")
    
     For i = 1 To lngZ_A + lngZ_E - 2
        varKey = feld(i, 2) & "#" & feld(i, 3) & "#" & feld(i, 4)
        If InStr(objDic(varKey), feld(i, 2) & "#" & feld(i, 3) & "#" & feld(i, 4)) Then
          objDic(varKey) = Split(objDic(varKey), "#")(0) + feld(i, 1) & "#" & feld(i, 2) & "#" & feld(i, 3) & "#" & feld(i, 4)
        Else
          objDic(varKey) = objDic(varKey) & feld(i, 1) & "#" & feld(i, 2) & "#" & feld(i, 3) & "#" & feld(i, 4)
        End If
     Next i
    
     j = 0
      ReDim arr(objDic.Count, 3)
      For Each varKey In objDic
        For i = 0 To UBound(Split(objDic(varKey), "#"))
          arr(j, k) = Split(objDic(varKey), "#")(i)
          k = k + 1
        Next i
         k = 0
         j = j + 1
      Next varKey
      .Range("I2:L" & lngZ_A + lngZ_E - 1).ClearContents
      .Range("I2:L" & j + 1) = arr
  End With

End Sub

@Günter
Ich denke, bei Deiner Lösung müssen noch die doppelten raus gelöscht werden.
Der TE hat als Ergebnis für das Beispiel 7 Datensätze Du hast 8
Gruß Atilla

Excel 2007
to top
#6
Hi Atilla,

Zitat:@Günter
Ich denke, bei Deiner Lösung müssen noch die doppelten raus gelöscht werden.
Der TE hat als Ergebnis für das Beispiel 7 Datensätze Du hast 8

da könntest du Recht haben. Ich habe die Datensätze nicht gezählt und da in der Problembeschreibung nichts von löschen stand, bin ich auf die Idee gar nicht gekommen.

Dann ist es mM nach eh nicht mit Formeln lösbar. Und auch nicht mit dem Autofilter oder der Bordfunktion "Duplikate entfernen". Es sind nach dem Addieren ja keine doppelten Sätze mehr vorhanden.
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#7
Hallo,

1. Die beiden Ausgangs-Blöcke untereinander anordnen, da reicht die Formel, die in meinem Beispiel in A5 steht (nach rechts und unten ziehen).
2. Eine Hilfsspalte, in meinem Beispiel M
3. Die Formel in I1 nach rechts und unten ziehen.

Arbeitsblatt mit dem Namen 'Tabelle2'
 ABCDEFGHIJKLM
151115-2115-211-2
252115-1115-111-1
35311501150110
454115111102221
55-211    52112
65-111    53113
75011    54114
85111         
9             

ZelleFormel
I1=WENN($M1="";"";SUMMEWENN($B$1:$B$8;$M1;A$1:A$8))
J1=WENN($M1="";"";SUMMEWENN($B$1:$B$8;$M1;B$1:B$8))
K1=WENN($M1="";"";SUMMEWENN($B$1:$B$8;$M1;C$1:C$8))
L1=WENN($M1="";"";SUMMEWENN($B$1:$B$8;$M1;D$1:D$8))
M1=MIN($B$1:$B$8)
M2{=WENN(M1=MAX($B$1:$B$8);"";MIN(WENN($B$1:$B$8>M1;$B$1:$B$8)))}
A5=E1
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#8
Hallo Edgar,

ein Datensatz ist dann doppelt, wenn drei Spalten gleiche Werte haben.
Im Beispiel vom TE sind die Spalten B:D und J:L zu betrachten.

Wenn diese Spalten gleiche Werte haben, dann wird nur der Wert aus der ersten Spalte addiert die anderen bleiben gleich.

Änder mal in C1 die 1 in 2 um.
Dann müssen acht Datensätze ohne Addition auftauchen.

Ich erhalte dieses Ergebnis:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHIJKL
251215-2115-211
352115-1115-111
4531150115011
5541151115121
6        5111
7        5211
8        5311
9        5411
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Bei Dir kommt das raus:

Arbeitsblatt mit dem Namen 'Tabelle3'
 ABCDEFGHIJKLM
151215-2115-211-2
252115-1115-111-1
35311501150110
454115111102421
55-211    52112
65-111    53113
75011    54114
85121         
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß Atilla

Excel 2007
to top
#9
Hallo Atilla,

das läßt sich auch lösen:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHIJKLM
151115-2115-211-21
252115-1115-111-11
35311501150111
4541151111011111
55-211    521121
65-111    531131
75011    541141
85111         

ZelleFormel
I1=WENN($M1="";"";SUMMENPRODUKT((($B$1:$B$8&$C$1:$C$8)*1=$M1)*1;A$1:A$8))
J1{=WENN($M1="";"";INDEX(B:B;VERGLEICH($M1;($B:$B&$C:$C)*1;0)))}
K1{=WENN($M1="";"";INDEX(C:C;VERGLEICH($M1;($B:$B&$C:$C)*1;0)))}
L1{=WENN($M1="";"";INDEX(D:D;VERGLEICH($M1;($B:$B&$C:$C)*1;0)))}
M1{=MIN(($B$1:$B$8&$C$1:$C$8)*1)}
M2{=WENN(M1=MAX(($B$1:$B$8&$C$1:$C$8)*1);"";MIN(WENN(($B$1:$B$8&$C$1:$C$8)*1>M1;($B$1:$B$8&$C$1:$C$8)*1)))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Summe ohne Duplikate mit mehreren Kriterien ConDucTi 7 160 08.11.2016, 13:40
Letzter Beitrag: ConDucTi
  Zellen prüfen und dann addieren h76469 1 52 05.11.2016, 08:53
Letzter Beitrag: WillWissen
  Jede n-te Zelle Wert um 1 addieren McAnimate 5 90 24.10.2016, 09:37
Letzter Beitrag: WillWissen
  Zahlen in der selben Zelle addieren Rudi12345 13 322 08.10.2016, 10:46
Letzter Beitrag: XOR LX
  Daten sortieren appelschnut 7 161 06.10.2016, 18:04
Letzter Beitrag: Jockel
  Liste in eine andere Spalte ausgeben ohne Duplikate Rabe79 7 321 19.08.2016, 20:26
Letzter Beitrag: Gast 123
  Duplikate nach Kriterium einreihen Telematix 17 919 21.07.2016, 16:01
Letzter Beitrag: Telematix
  addieren, subtrahieren, multiplizieren pariser 5 341 08.07.2016, 06:35
Letzter Beitrag: Rabe
  Text-Duplikate entfernen Mahadmakandis 26 1.549 02.07.2016, 11:45
Letzter Beitrag: Mahadmakandis
  Quality Control Program -> ALLE Duplikate finden Spike87 13 653 10.06.2016, 14:17
Letzter Beitrag: Fennek

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste