Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Gleiche Werte von zwei Spalten in gleiche Zeile

Gleiche Werte von zwei Spalten in gleiche Zeile
08.08.2017 09:05:11
zwei
Servus zusammen.
Ich benötige einen VBA Code zum Sortieren gleicher Werte mit Text in dieselbe Zeile.
Hierzu erstmal ein Bild:
Userbild
Die Übersicht vom Dezember 2016 soll unberührt bleiben.
Im Juli 2017 soll das Makro so funktionieren dass es alle 3 Spalten (Maschinennummer, Investition und Beschreibung) in die Zeilen sortiert wo auch die Maschinennummer im Dezember 2016 steht.
So, dass zum Beispiel die Maschinennummern 9932 und 9932 in der selben Zeile stehen und man somit den direkten Vergleich beider Investitionen sieht.
Außerdem soll im Juli 2017 für die Maschinennummer 1111 die Zelle rot eingefärbt werden, weil sie ja da in der Spalte fehlt.
Ich würde das gerne in VBA selbst programmieren aber soweit reichen meine Kenntnisse dann doch nicht.
Danke schonmal :)
Anzeige

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

Betreff
Datum
Anwender
Anzeige
AW: VBA Dictionary
08.08.2017 09:14:20
Fennek
Hallo,
mein Vorschlag:
Die Maschinennr. aus 2016 als "key" in ein Dictionary laden. Dann die Tabelle aus 2017 in die "Items". Dabei bleibt die Reihenfolge von 2016 erhalten. Werte, die nicht mehr vorkommen "1111", bleiben frei.
Als letzte Schritt muss man 2017 nach neuen Werten durchsuchen und anfügen.
Jetzt bin ich auf andere Ideen gespannt.
mfg
Anzeige
AW: VBA Dictionary
08.08.2017 09:41:13
watsonenge
Fennek das klingt schon mal gut. Kannst du mir ein VBA Grundgerüst geben ? Ich bekomme das mit meinen Kenntnissen leider nicht hin...
AW: warum VBA? ...
08.08.2017 09:21:03
...
Hallo,
... mit einer Hilfsspalte mit oder auch ohne Formel kannst Du mit daran anschließender Sortierung der Julidaten das gewünschte auch schnelle so vornehmen.
Gruß Werner
.. , - ...
Anzeige
AW: warum VBA? ...
08.08.2017 09:26:29
watsonenge
Ich möchte das in VBA realisieren, weil ich einen automatisierten Befehl brauche, der mir das mit jeder Liste machen kann wo ich dann auch die Range festlege. Das Bild ist quasi nur ein Ausschnitt einer sehr großen Liste und daher würde ich es gerne in VBA programmieren.
AW: dann folge dem Vorschlag von Fennek owT
08.08.2017 09:35:58
Fennek
Gruß Werner
.. , - ...
Anzeige
AW: Gleiche Werte von zwei Spalten in gleiche Zeile
08.08.2017 10:00:26
zwei
Hallo watsonenge,
im nachfolgenden Makro muss du noch Anpassungen bzgl. der Spalten- und Zeilen-Nummern machen.
Gruß
Franz
Sub SortMaschinen()
Dim wks As Worksheet
Dim Spa_VJ As Long, Spa_J As Long
Dim Zei_1 As Long, Zei_L As Long, Zei_VJ As Long, Zei_J As Long
Dim arrData_J
Dim varMaschNr As Variant
Set wks = ActiveSheet
'die nachfolgenden Werte ggf. anpassen
Spa_VJ = 1          'Spalte mit Maschinen-Nr des Vorjahres
Spa_J = Spa_VJ + 3  'Spalte mit Maschinen-Nr des aktuellen Jahres
Zei_1 = 4           'Zeile mit 1. Maschinen-Nr. unter den Spaltentiteln
With wks
'letzte Zeile mit Masch.-Nr im Vorjahr
Zei_L = .Cells(.Rows.Count, Spa_VJ).End(xlUp).Row
'Daten des laufenden Jahres in Array laden
arrData_J = .Range(.Cells(Zei_1, Spa_J), .Cells(Zei_L, Spa_J + 2))
'Daten des laufenden Jahres löschen
.Range(.Cells(Zei_1, Spa_J), .Cells(Zei_L, Spa_J + 2)).ClearContents
'letzte Zeile mit Masch.-Nr im Vorjahr
Zei_L = .Cells(.Rows.Count, Spa_VJ).End(xlUp).Row
'Nummern im laufenden Jahr sortieren
For Zei_VJ = Zei_1 To Zei_L
varMaschNr = .Cells(Zei_VJ, Spa_VJ).Value
For Zei_J = LBound(arrData_J, 1) To UBound(arrData_J, 1)
If arrData_J(Zei_J, 1) = varMaschNr Then
.Cells(Zei_VJ, Spa_J) = varMaschNr
.Cells(Zei_VJ, Spa_J + 1) = arrData_J(Zei_J, 2)
.Cells(Zei_VJ, Spa_J + 2) = arrData_J(Zei_J, 3)
arrData_J(Zei_J, 1) = ""
Exit For
End If
Next Zei_J
Next Zei_VJ
'fehlende Nrn. markieren
For Zei_VJ = Zei_1 To Zei_L
If .Cells(Zei_VJ, Spa_J) = "" Then
.Cells(Zei_VJ, Spa_J).Interior.Color = RGB(255, 0, 0)
End If
Next Zei_VJ
'im Vorjahr nicht vorhandene Masch.Nrn. im laufenden Jahr nachtragen
For Zei_J = LBound(arrData_J, 1) To UBound(arrData_J, 1)
If arrData_J(Zei_J, 1)  "" Then
Zei_L = Zei_L + 1
.Cells(Zei_L, Spa_J) = arrData_J(Zei_J, 1)
.Cells(Zei_L, Spa_J + 1) = arrData_J(Zei_J, 2)
.Cells(Zei_L, Spa_J + 2) = arrData_J(Zei_J, 3)
End If
Next Zei_J
End With 'wks
End Sub

Anzeige
AW: Gleiche Werte von zwei Spalten in gleiche Zeile
08.08.2017 10:14:54
zwei
Hallo Franz,
schon einmal ein großes Dankschön dass du dir die Zeit genommen hast.
Leide haut das bei mir nicht so hin wie ich mir das vorgestellt hab. Hier ein Bild nach dem Ausführen des Codes.
Userbild
Auch mit den editierten richtigen Spalten & Zeilennummern funktioniert es leider nicht.
Anzeige
AW: Gleiche Werte von zwei Spalten in gleiche Zeile
08.08.2017 10:28:52
zwei
Ich habe nochmal etwas an den Spalten geändert und jetzt hat es geklappt. Super, danke dir vielmals !!!
AW: Gleiche Werte von zwei Spalten in gleiche Zeile
08.08.2017 11:28:12
zwei
Hallo Franz,
ich hab noch ein kleines Anliegen. Gibt es eine Möglichkeit per VBA die Zeilen, die rote Felder beinhalten, also Werte die im laufenden Jahr nicht existieren, nach unten zu verschieben ? Dass oben alle gefundenen Werte/Zeilen stehen und danach die roten ?
Anzeige
AW: Gleiche Werte von zwei Spalten in gleiche Zeile
08.08.2017 15:43:15
zwei
Hallo watsonge,
dazu muss du den Sierabschnitt des Makt´ros anpassen.
    'Nummern im laufenden Jahr sortieren
For Zei_VJ = Zei_1 To Zei_L
varMaschNr = .Cells(Zei_VJ, Spa_VJ).Value
For Zei_J = LBound(arrData_J, 1) To UBound(arrData_J, 1)
If arrData_J(Zei_J, 1) = varMaschNr Then
.Cells(Zei_VJ, Spa_J) = varMaschNr
.Cells(Zei_VJ, Spa_J + 1) = arrData_J(Zei_J, 2)
.Cells(Zei_VJ, Spa_J + 2) = arrData_J(Zei_J, 3)
arrData_J(Zei_J, 1) = ""
Exit For
End If
If Zei_J = UBound(arrData_J, 1) Then
Zei_L = Zei_L + 1
.Cells(Zei_L, Spa_J) = .Cells(Zei_VJ, Spa_VJ)
.Cells(Zei_L, Spa_J + 1) = .Cells(Zei_VJ, Spa_VJ + 1)
.Cells(Zei_L, Spa_J + 2) = .Cells(Zei_VJ, Spa_VJ + 2)
End If
Next Zei_J
Next Zei_VJ
Gruß
Franz
Anzeige
über neue Liste
08.08.2017 10:10:41
KlausF
Hi, auf die Schnelle:
man könnte auch die Anzahl der aktuellen Listen abfragen und dann die
Maschinennummern von Dez 2016 als neue Liste anfügen.
Dann nach der neuen (letzten) Liste sortieren lassen und die Liste wieder löschen.
Ich meine, dass Hans Herber auch mal eine Beispieldatei dazu gemacht hat.
Ungetestet.
Gruß
Klaus
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Gleiche Werte von zwei Spalten in gleiche Zeile sortieren


Schritt-für-Schritt-Anleitung

Um die gleichen Werte von zwei Spalten in einer Excel-Tabelle in dieselbe Zeile zu sortieren, kannst du ein VBA-Makro verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Füge ein neues Modul hinzu: Klicke im Menü auf "Einfügen" und dann auf "Modul".

  3. Kopiere den folgenden VBA-Code in das Modul:

    Sub SortMaschinen()
        Dim wks As Worksheet
        Dim Spa_VJ As Long, Spa_J As Long
        Dim Zei_1 As Long, Zei_L As Long, Zei_VJ As Long, Zei_J As Long
        Dim arrData_J
        Dim varMaschNr As Variant
        Set wks = ActiveSheet
        Spa_VJ = 1          'Spalte mit Maschinen-Nr des Vorjahres
        Spa_J = Spa_VJ + 3  'Spalte mit Maschinen-Nr des aktuellen Jahres
        Zei_1 = 4           'Zeile mit 1. Maschinen-Nr. unter den Spaltentiteln
        With wks
            Zei_L = .Cells(.Rows.Count, Spa_VJ).End(xlUp).Row
            arrData_J = .Range(.Cells(Zei_1, Spa_J), .Cells(Zei_L, Spa_J + 2))
            .Range(.Cells(Zei_1, Spa_J), .Cells(Zei_L, Spa_J + 2)).ClearContents
            Zei_L = .Cells(.Rows.Count, Spa_VJ).End(xlUp).Row
    
            For Zei_VJ = Zei_1 To Zei_L
                varMaschNr = .Cells(Zei_VJ, Spa_VJ).Value
                For Zei_J = LBound(arrData_J, 1) To UBound(arrData_J, 1)
                    If arrData_J(Zei_J, 1) = varMaschNr Then
                        .Cells(Zei_VJ, Spa_J) = varMaschNr
                        .Cells(Zei_VJ, Spa_J + 1) = arrData_J(Zei_J, 2)
                        .Cells(Zei_VJ, Spa_J + 2) = arrData_J(Zei_J, 3)
                        arrData_J(Zei_J, 1) = ""
                        Exit For
                    End If
                Next Zei_J
            Next Zei_VJ
    
            For Zei_VJ = Zei_1 To Zei_L
                If .Cells(Zei_VJ, Spa_J) = "" Then
                    .Cells(Zei_VJ, Spa_J).Interior.Color = RGB(255, 0, 0)
                End If
            Next Zei_VJ
    
            For Zei_J = LBound(arrData_J, 1) To UBound(arrData_J, 1)
                If arrData_J(Zei_J, 1) <> "" Then
                    Zei_L = Zei_L + 1
                    .Cells(Zei_L, Spa_J) = arrData_J(Zei_J, 1)
                    .Cells(Zei_L, Spa_J + 1) = arrData_J(Zei_J, 2)
                    .Cells(Zei_L, Spa_J + 2) = arrData_J(Zei_J, 3)
                End If
            Next Zei_J
        End With
    End Sub
  4. Ändere die Spalten- und Zeilennummern im Code entsprechend deiner Tabelle, falls nötig.

  5. Schließe den VBA-Editor und gehe zurück zu Excel.

  6. Führe das Makro aus: Drücke ALT + F8, wähle SortMaschinen und klicke auf "Ausführen".


Häufige Fehler und Lösungen

  • Fehler: "Überlauf" beim Ausführen des Makros
    Lösung: Überprüfe, ob die Spalten- und Zeilen-Nummern korrekt eingestellt sind. Achte darauf, dass die Daten in den angegebenen Spalten vorhanden sind.

  • Fehler: Rote Zellen erscheinen nicht
    Lösung: Stelle sicher, dass die Bedingung zur Färbung der Zellen im Code korrekt implementiert ist. Überprüfe, ob die Zellen tatsächlich leer sind.


Alternative Methoden

Wenn du die Verwendung von VBA vermeiden möchtest, kannst du auch die Excel-Funktionen SVERWEIS() oder INDEX() und VERGLEICH() kombinieren, um ähnliche Ergebnisse zu erzielen. Du kannst auch Hilfsspalten nutzen, um die Werte manuell zu sortieren.


Praktische Beispiele

Angenommen, du hast zwei Spalten:

  • Spalte A: Maschinen-Nr (Vorjahr)
  • Spalte D: Maschinen-Nr (aktuelles Jahr)

Wenn du das Makro ausführst, werden die Werte in Spalte D in die entsprechende Zeile von Spalte A eingetragen, und die Zellen, die keine Maschinen-Nr enthalten, werden rot eingefärbt.


Tipps für Profis

  • Verwende Named Ranges: Statt fester Zellreferenzen kannst du benannte Bereiche verwenden, um deinen Code flexibler zu gestalten.
  • Fehlerbehandlung einbauen: Implementiere On Error Resume Next im Code, um unerwartete Fehler abzufangen und zu steuern.
  • Dokumentation: Kommentiere deinen Code gut, damit du und andere Nutzer in der Zukunft leichter nachvollziehen können, was jeder Abschnitt macht.

FAQ: Häufige Fragen

1. Wie kann ich das Makro anpassen?
Du musst die Werte für Spa_VJ, Spa_J, und Zei_1 im Code anpassen, um deinen spezifischen Anforderungen gerecht zu werden.

2. Funktioniert das Makro in allen Excel-Versionen?
Ja, das Makro sollte in den meisten modernen Excel-Versionen funktionieren, die VBA unterstützen, wie Excel 2010, 2013, 2016 und 2019.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige