Das letzte Clever-Excel-Forum.de - Treffen
fand vom 15. - 17. September 2017 in Friedrichroda /
Thüringen / Region Großer Inselsberg statt.

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.
Gruß Günter
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
Hinweise zu meiner Hilfe in Foren
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.
Gruß Günter
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
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.
Gruß Günter
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ß
Opa 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
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ß
Opa Edgar

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


Gehe zu:


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