Excel: Werte mit gleicher ID zusammenfassen?

Dieses Thema Excel: Werte mit gleicher ID zusammenfassen? im Forum "Microsoft Office Suite" wurde erstellt von Ace_NoOne, 12. Juli 2006.

Thema: Excel: Werte mit gleicher ID zusammenfassen? Hallo zusammen, das Problem der doppelten Einträge in Excel-Tabellen ist ja weithin bekannt. Nun habe ich aber ein...

  1. Hallo zusammen,

    das Problem der doppelten Einträge in Excel-Tabellen ist ja weithin bekannt.
    Nun habe ich aber ein Problem, das einen Schritt weitergeht. Doppelte Einträge können nicht einfach gelöscht werden, sondern deren Werte müssen zuvor aufaddiert werden.

    Beispiel:

    [table]
    [tr]
    [td]ItemID[/td]
    [td]Value[/td]
    [/tr]
    [tr]
    [td]AAA[/td]
    [td]11[/td]
    [/tr]
    [tr]
    [td]BBB[/td]
    [td]22[/td]
    [/tr]
    [tr]
    [td]AAA[/td]
    [td]33[/td]
    [/tr]
    [tr]
    [td]BBB[/td]
    [td]44[/td]
    [/tr]
    [/table]

    Das sollte nun reduziert werden zu Folgendem:

    [table]
    [tr]
    [td]ItemID[/td]
    [td]Value[/td]
    [/tr]
    [tr]
    [td]AAA[/td]
    [td]44[/td]
    [/tr]
    [tr]
    [td]BBB[/td]
    [td]66[/td]
    [/tr]
    [/table]

    Eigentlich würde man dafür wohl eine Pivot-Tabelle benutzen, aber die macht Probleme, sobald es mehr als 8.000 ItemIDs gibt... :(

    Falls jemand eine Lösung für dieses Problem hat, wäre ich sehr dankbar!
     
  2. Hallo,

    aus meiner Sicht wäre das Einfachste:

    Leg dir (sofern du sie nicht schon irgendwo hast) zB. in->G' eine Liste mit allen vorkommenden ID's an.
    Dann gibst du in der entsprechenden Zeile in Spalte->H' folgende Formel ein:

    '=SUMMEWENN(A:B;G1;B:B)',
    kannst natürlich auch die Zeilenanzahl des Suchbereiches begrenzen:

    '=SUMMEWENN(A$1:B$10000;G1;B$1:B$10000)',

    soweit wie nötig im Spalte->H' kopieren, sollte so klappen.
     
  3. Das hatte ich mir auch schon überlegt - aber wie komme ich an die Liste der IDs?
     
  4. puh,

    dann schaun mer mal weiter.
     
  5. Hallo Ace_NoOne,

    dann schau wir mal, was die Makro-Schatztruhe zu bieten hat.

    Wenn das alte Blatt erhalten bleiben soll, müßte vorher noch das Kopieren auf ein temporäres Blatt hinzugefügt werden. Jetzt arbeitet das Makro auf dem orginalen, aktiven Blatt.

    Im Makro sind die Konstanten für die beiden Spalten und die erste Wertezeile anzupassen.

    Gruß Matjes :)
    Code:
    Option Explicit
    Sub Excel_ZeilenZusammenfassenItemIDValue()
    '<<<<< A N P A S S E N >>>>>>>>>>>>>>>>>>
      Const cSP_ITEM = 2
      Const cSP_VALUE = 3
      Const cZ_ERSTEWERTEZEILE = 2
    '<<<<< A N P A S S E N    E N D E >>>>>>>
    
      Dim ws As Worksheet, rs As Range, rd As Range
      Dim lLetzteSP As Long, lLetzteZ As Long, lLetzteZItem As Long
      Dim lSPReihenfolge As Long, lLetzteZReihenfolge As Long
      Dim lanf As Long, lend As Long
      
      Set ws = ActiveSheet
      lLetzteSP = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1
      lLetzteZ = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
      lSPReihenfolge = lLetzteSP + 1
      
     ->a) in nächste frei Spalte Reihenfolgenummer eintragen,
     ->   um die alte Reihenfolge wieder herzustellen
      ws.Cells(cZ_ERSTEWERTEZEILE, lSPReihenfolge).Value = 1
      Set rs = ws.Range(ws.Cells(cZ_ERSTEWERTEZEILE, lSPReihenfolge), _
                        ws.Cells(cZ_ERSTEWERTEZEILE, lSPReihenfolge))
      Set rd = ws.Range(ws.Cells(cZ_ERSTEWERTEZEILE, lSPReihenfolge), _
                        ws.Cells(lLetzteZ, lSPReihenfolge))
      rs.AutoFill Destination:=rd, Type:=xlFillSeries
      
     ->b) gesamten Bereich nach ITEM sortieren
      Set rs = ws.Range(ws.Cells(cZ_ERSTEWERTEZEILE, 1), _
                        ws.Cells(lLetzteZ, lSPReihenfolge))
      rs.Sort _
        Key1:=ws.Cells(cZ_ERSTEWERTEZEILE, cSP_ITEM), Order1:=xlAscending, _
        Key2:=ws.Cells(cZ_ERSTEWERTEZEILE, lSPReihenfolge), Order2:=xlAscending, _
        Header:=xlNo
       ->### Korrektur: Reihenfolge zur Sicherheit mit aufgenommen 
        
     ->c) letzte Zeile für Item bestimmen
      lLetzteZItem = ws.Cells(ws.Rows.Count, cSP_ITEM).End(xlUp).Row
    
     ->d) gleiche Zeilen zusammenfassen (VALUE addieren)
    
     ->ENDE ITEM setzen
      lend = lLetzteZItem
      Do While lend >= cZ_ERSTEWERTEZEILE
        lanf = lend
        Do While (lanf > cZ_ERSTEWERTEZEILE) And _
                (ws.Cells(lanf - 1, cSP_ITEM).Value = ws.Cells(lend, cSP_ITEM).Value)
         ->VALUE aufaddieren
          lanf = lanf - 1
          ws.Cells(lanf, cSP_VALUE).Value = _
          ws.Cells(lanf, cSP_VALUE).Value + ws.Cells(lanf + 1, cSP_VALUE).Value
        Loop
       ->ggf. Zeilen löschen
        If lanf <> lend Then ws.Rows((lanf + 1) & : & lend).Delete
       ->ENDE ITEM setzen
        lend = lanf - 1
      Loop
      
     ->e) letzte Zeile für Reihenfolgespalte bestimmen
      lLetzteZReihenfolge = ws.Cells(ws.Rows.Count, lSPReihenfolge).End(xlUp).Row
      
     ->f) Zeilen wieder in alte reihenfolge bringen
      Set rs = ws.Range(ws.Cells(cZ_ERSTEWERTEZEILE, 1), _
                        ws.Cells(lLetzteZReihenfolge, lSPReihenfolge))
      rs.Sort _
        Key1:=ws.Cells(cZ_ERSTEWERTEZEILE, lSPReihenfolge), Order1:=xlAscending, _
        Header:=xlNo
      
     ->g) zusätzlich Reihenfolge-Spalte löschen
      Set rd = ws.Range(ws.Cells(cZ_ERSTEWERTEZEILE, lSPReihenfolge), _
                        ws.Cells(lLetzteZ, lSPReihenfolge))
      rd.Clear
      
    AUFRAEUMEN:
      Set ws = Nothing: Set rs = Nothing: Set rd = Nothing
    End Sub
     
  6. Tja, was soll ich da noch sagen - es funktioniert natürlich perfekt! (Etwas anderes würde man von Matjes auch nicht erwarten... ;) )

    Vielen Dank!!
     
  7. Hab noch eine kleine Korrektur (siehe ###) eingefügt. Ist nur zur Sicherheit, daß auf jeden Fall die Reihenfolge eingehalten wird.

    Gruß Matjes  ;)
     
  8. Mr. Unstoppable... :D
     
  9. Kann ich nur zustimmen, Respekt
     
  10. Ich habe versucht, noch eine Abfrage einzubauen, damit nur solche Zeilen zusammengefasst werden, die in einer dritten Spalte einen bestimmten Wert haben.
    Eigentlich sollte das ja sehr einfach sein, aber leider finde ich mich in deinem Code nicht zurecht, Matjes...

    Beispiel:

    [table]
    [tr]
    [td]ItemID[/td]
    [td]Value[/td]
    [td]Active[/td]
    [/tr]
    [tr]
    [td]AAA[/td]
    [td]11[/td]
    [td]True[/td]
    [/tr]
    [tr]
    [td]BBB[/td]
    [td]22[/td]
    [td]True[/td]
    [/tr]
    [tr]
    [td]AAA[/td]
    [td]33[/td]
    [td]False[/td]
    [/tr]
    [tr]
    [td]BBB[/td]
    [td]44[/td]
    [td]False[/td]
    [/tr]
    [/table]

    Hier sollten dann die Zeilen ignoriert/verworfen werden, wo Active = False.

    Wäre das möglich? Wenn das allerdings mehr Arbeit ist, als eine simple IF-Abfrage einzubauen, lassen wir's so gut sein; ich habe schon zu viel von Matjes' Zeit in Anspruch genommen... :eek:
     
Die Seite wird geladen...

Excel: Werte mit gleicher ID zusammenfassen? - Ähnliche Themen

Forum Datum
Excel Tabelle Werte zu ordnen Microsoft Office Suite 23. Sep. 2016
Excel '10 - Frage zu Option "AutoVervollständigen für Zellwerte!" Microsoft Office Suite 16. Juli 2012
Excel: Summe beliebiger Anzahl Zellen (also nicht die Werte) ? Microsoft Office Suite 2. Dez. 2010
Excel - Werte unterschiedlicher Tabellenblätter für Übersicht automatisch ziehen Microsoft Office Suite 22. Aug. 2010
Excel 2007 Datenblatt - Standardwerte wiederherstellen? Windows XP Forum 9. Aug. 2009