Microsoft Excel

Herbers Excel/VBA-Archiv

unabhängig Spalten sortieren

Betrifft: unabhängig Spalten sortieren von: Roman Maidl
Geschrieben am: 21.10.2020 13:25:12

Hiho,

ich habe ein Tabellenblatt mit sehr viele Daten. Dort sind immer zwei Spalten von den Daten her zugehörig. Diese zugehörigen Daten möchte ich sortieren (Größe des Wertes). Dabei sollten alle anderen Spalten unberührt bleiben, immer nur die zwei gewünschten zusammen.

Das heist: Spalte A und Spalte K nach Spalte A sortieren, Spalte B und Spalte L nach Spalte B soritieren, Spalte C und Spalte M nach Spalte M sortieren,...

(Natürlich sind es bei mir mehr Daten, also Spalte A und IQ... Spalte B und Spalte IR... aber zur Veranschaulichung reicht das obere)


Ist dies mit VBA einfach möglich?

Danke für eure/deine Hilfe.

Gruß Roman

Betrifft: AW: unabhängig Spalten sortieren
von: Daniel
Geschrieben am: 21.10.2020 13:39:20

Hi
gemeinsam sortieren kannst du immer nur Spalten, die direkt nebeneinander stehen.
dh du müsstest zunächst deine Spalten so sortieren, dass A und K, B und L direkt nebeneinander stehen (dh K nach B, B nach C, nach D.
dann kannst du nacheinander die Spaltenpaare A:B, C:D usw sortieren (jedes paar muss separat soritert werden).
zum Schluss bringst du die Spalten wieder in die ursrüngliche Reihenfolge.

Gruß Daniel

Betrifft: AW: unabhängig Spalten sortieren
von: Roman
Geschrieben am: 21.10.2020 14:16:46

Wie erwähnt sind es aber sehr viele Daten. Sehr viele Spalten. da ich müsste ewig das zusammensortieren und dann nach Filtern wieder ewig auseinander sortieren. Wäre sehr ausgewändig.

Danke trotzdem

Grus Roman

Betrifft: AW: unabhängig Spalten sortieren
von: Daniel
Geschrieben am: 21.10.2020 14:42:23

Hi
ja, aber geht nicht anders.
wenn du zwei Spalten gemeinsam nach einer der beiden Spalten sortieren willst und alles andere darf nicht mitsortiert werden, müssen diese beiden Spalten direkt nebeneinander stehen.

da man in Excel nicht nur Zeilen nach einer Spalte sortieren kann, sondern auch Spalten nach einer Zeile, geht das ganze auch recht einfach, wenn du ein bisschen Vorarbeit leistest.

du brauchst zwei zusätzliche Zeilen (bspw Zeile 1 und 2, Überschrifgen dann ab Zeile 3)
in der ersten Zeile steht einfach nur die Spaltennummer (1, 2, 3, … x) als feste Zahl.
diese Zeile sichert dir die Originalreihenfolge der Spalten.
in der zweiten Zeile markiest du dann die Zeilen, die gemeinsam sortiert werden sollen mit der gleichen Zahl, bzw der gleichen Ganzzahl. Die Spalte, die das Sortierkiriterium darstellt, bekommt die kleinee Nachkommastelle.
dh in die Zeile 2 schreibst du in Spalte A: 1,1 und in Spalte IQ, die mit A sortiert werden soll, 1,2.
in Spalte B dann 2,1 und in IR 2,2 usw.
Diese Vorarbeit musst du leisten, aber irgendwie musst du ja dokumentieren und beschreiben, was zusammen sortiert werden soll. Um diese Arbeit kommst du nicht drum herum (es sei denn, man kann es irgendwie aus den Spaltenüberschriften herauslesen, aber dazu hast du noch keinen Hinweis gegeben.

wenn du diese Vorarbeit mit Zeile 1 und Zeile 2 geleistet hast, reicht dieser Code (sofern es immer 2 Spalten sind die zusammen gehören)
Sub test()
Dim S As Long
With ActiveSheet.UsedRange
    .Sort key1:=.Cells(2, 1), order1:=xlAscending, Header:=xlNo, Orientation:=2
    With .Offset(2, 0).Resize(.Rows.Count - 2, 2)
        For S = 1 To WorksheetFunction.Count(Rows(2)) Step 2
            .Columns(S).Resize(, 2).Select
            .Columns(S).Resize(, 2).Sort key1:=.Cells(1, S), order1:=xlAscending, Header:=xlNo,  _
Orientation:=1
        Next
    End With
    .Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo, Orientation:=2
End With
        
End Sub
bei Spalten, die nicht sortiert werden sollen, lässt du die Zeile 2 einfach leer.

Gruß Daniel

Betrifft: AW: unabhängig Spalten sortieren
von: Roman Maidl
Geschrieben am: 21.10.2020 14:54:18

Hey,

ich danke dir vielmals! Ein anderer User hat mein Problem gelöst.

Ich danke dir trotzdem sehr für deine Mühen!

Einen wunderschönen Tag noch.

Gruß Roman

Betrifft: AW: unabhängig Spalten sortieren
von: Daniel
Geschrieben am: 21.10.2020 14:55:51

nun dann, viel Spaß mit der anderen Lösung
Gruß Daniel

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 21.10.2020 13:44:36

Hallo Roman,

ab welcher Zeile beginnen die Daten und sind immer alle Spalten gleich gefüllt oder soll die letzte benutzte Zeile aus beiden Spalten ermittelt werden?

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman
Geschrieben am: 21.10.2020 14:05:31

Die Daten beginnen in Zeile 1. Die Spalten vorne nach denen sortiert werden soll haben Zahlenwerte (zwischen 3-12 Zeilen sind gefüllt). Und die zugehörige Spalte hat Begriffe. Wie erwähnt gehört die Spalte A zur Spalte IQ und B zur IR.

Ist leider sehr schwer zu beschreiben und nachdem es von der Arbeit ist kann ich es nicht teilen. Wenn es hilft erstelle ich Notfalls ein Beispiel?

Danke

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 21.10.2020 14:40:34

Hallo Roman,

teste mal:

Option Explicit

Public Sub Sorting()
    
    Const COLUMN_OFFSET As Long = 250
    
    Dim avntValues1 As Variant, avntValues2 As Variant
    Dim lngColumn As Long, lngLastRow As Long
    
    lngColumn = Selection.Column
    
    lngLastRow = Cells(Rows.Count, lngColumn).End(xlUp).Row
    lngLastRow = Application.Max(lngLastRow, Cells(Rows.Count, lngColumn + COLUMN_OFFSET).End(xlUp).Row)
    
    avntValues1 = Range(Cells(1, lngColumn), Cells(lngLastRow, lngColumn)).Value
    avntValues2 = Range(Cells(1, lngColumn + COLUMN_OFFSET), Cells(lngLastRow, lngColumn + COLUMN_OFFSET)).Value
    
    Call QuickSort(1, lngLastRow, avntValues1, avntValues2)
    
    Range(Cells(1, lngColumn), Cells(lngLastRow, lngColumn)).Value = avntValues1
    Range(Cells(1, lngColumn + COLUMN_OFFSET), Cells(lngLastRow, lngColumn + COLUMN_OFFSET)).Value = avntValues2
    
End Sub

Private Sub QuickSort(ByVal pvlngLBound As Long, ByVal pvlngUBound As Long, _
        ByRef avntValuses1 As Variant, ByRef avntValuses2 As Variant)

    
    Dim ialngIndex1 As Long, ialngIndex2 As Long
    Dim vntElement As Variant, vntBuffer As Variant
    
    ialngIndex1 = pvlngLBound
    ialngIndex2 = pvlngUBound
    
    vntBuffer = avntValuses1((pvlngLBound + pvlngUBound) \ 2, 1)
    
    Do
        
        Do While avntValuses1(ialngIndex1, 1) < vntBuffer
            ialngIndex1 = ialngIndex1 + 1
        Loop
        
        Do While vntBuffer < avntValuses1(ialngIndex2, 1)
            ialngIndex2 = ialngIndex2 - 1
        Loop
        
        If ialngIndex1 <= ialngIndex2 Then
            
            vntElement = avntValuses1(ialngIndex1, 1)
            avntValuses1(ialngIndex1, 1) = avntValuses1(ialngIndex2, 1)
            avntValuses1(ialngIndex2, 1) = vntElement
            
            vntElement = avntValuses2(ialngIndex1, 1)
            avntValuses2(ialngIndex1, 1) = avntValuses2(ialngIndex2, 1)
            avntValuses2(ialngIndex2, 1) = vntElement
            
            ialngIndex1 = ialngIndex1 + 1
            ialngIndex2 = ialngIndex2 - 1
            
        End If
    Loop Until ialngIndex1 > ialngIndex2
    
    If pvlngLBound < ialngIndex2 Then Call QuickSort(pvlngLBound, ialngIndex2, avntValuses1, avntValuses2)
    If ialngIndex1 < pvlngUBound Then Call QuickSort(ialngIndex1, pvlngUBound, avntValuses1, avntValuses2)
    
End Sub

Einfach den Cursor in die gewünschte Spalte setzen und dann das Makro "Sorting" aufrufen (Alt+F8).

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman Maidl
Geschrieben am: 21.10.2020 14:52:48

Hey,

erstmal Danke für deine Mühen. Das klappt, wenn ich die Spalten einzeln anwähle. Gibt es auch einen Weg dass ich nicht 250 Spalten anwählen muss?

Danke schonmal!!
Gruß Roman

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 21.10.2020 14:57:59

Hallo Roman,

versteh ich das richtig, alle Spalten von 1 bis 250 sortieren mit den jeweilig korrespondierenden Spalten 251 bis 501?

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman Maidl
Geschrieben am: 21.10.2020 15:01:24

Ja genau. So wäre das richtig.

Danke wirklich für deine Hilfe.

Gruß Roman!

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 21.10.2020 15:03:15

Hallo Roman,

dann musst du nur das erste Makro so ändern:

Public Sub Sorting()
    
    Const COLUMN_OFFSET As Long = 250
    
    Dim avntValues1 As Variant, avntValues2 As Variant
    Dim lngColumn As Long, lngLastRow As Long
    
    For lngColumn = 1 To COLUMN_OFFSET
        
        lngLastRow = Cells(Rows.Count, lngColumn).End(xlUp).Row
        lngLastRow = Application.Max(lngLastRow, Cells(Rows.Count, lngColumn + COLUMN_OFFSET).End(xlUp).Row)
        
        avntValues1 = Range(Cells(1, lngColumn), Cells(lngLastRow, lngColumn)).Value
        avntValues2 = Range(Cells(1, lngColumn + COLUMN_OFFSET), Cells(lngLastRow, lngColumn + COLUMN_OFFSET)).Value
        
        Call QuickSort(1, lngLastRow, avntValues1, avntValues2)
        
        Range(Cells(1, lngColumn), Cells(lngLastRow, lngColumn)).Value = avntValues1
        Range(Cells(1, lngColumn + COLUMN_OFFSET), Cells(lngLastRow, lngColumn + COLUMN_OFFSET)).Value = avntValues2
        
    Next
End Sub

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman Maidl
Geschrieben am: 21.10.2020 15:08:04

Bekomme leider inen Laufzeitfehler "Typen unveträglich".

Hat es einen Einfluss wenn einige Werte auch negativ sind? Oder in einer Spalte nur 1 Wert steht?

Viele Grüße
Roman

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 21.10.2020 15:20:10

Hallo Roman,

Fehlerwerte die durch Formeln entstehen haben natürlich einen Einfluss. Wie sollen die behandelt werden?

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman Maidl
Geschrieben am: 21.10.2020 15:22:18

Sind in dem keine Sinne keien Fehlerwerte, sollte in die Reihung mit einfließen. Also mit dem größten negativen wert hin zum grösten psoitiven quasi

Danke.

Gruß Roman

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 21.10.2020 16:18:47

Hallo Roman,

kann ich die Fehlerwerte durch ihren Text ersetzen?

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman Maidl
Geschrieben am: 22.10.2020 06:23:54

Entschuldigen Sie die späte Antwort. Ich hatte Feierabend.

Nein dort soll kein Text erscheinen. Es sind keine Fehlerwerte! Sie sollten einfach sortiert werden!

Danke für die Hilfe

Gruß Roman

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 22.10.2020 10:32:08

Hallo Roman,

teste mal:

Option Explicit

Public Sub Sorting()
    
    Const COLUMN_OFFSET As Long = 250
    
    Dim avntValues1 As Variant, avntValues2 As Variant
    Dim lngColumn As Long, lngLastRow As Long
    
    For lngColumn = 1 To COLUMN_OFFSET
        
        lngLastRow = Cells(Rows.Count, lngColumn).End(xlUp).Row
        lngLastRow = Application.Max(lngLastRow, Cells(Rows.Count, lngColumn + COLUMN_OFFSET).End(xlUp).Row)
        
        avntValues1 = Range(Cells(1, lngColumn), Cells(lngLastRow, lngColumn)).Value
        avntValues2 = Range(Cells(1, lngColumn + COLUMN_OFFSET), Cells(lngLastRow, lngColumn + COLUMN_OFFSET)).Value
        
        If Not IsEmpty(avntValues1) Then
            
            Call ClearErrors(avntValues1)
            Call ClearErrors(avntValues2)
            
            Call QuickSort(1, lngLastRow, avntValues1, avntValues2)
            
            Range(Cells(1, lngColumn), Cells(lngLastRow, lngColumn)).Value = avntValues1
            Range(Cells(1, lngColumn + COLUMN_OFFSET), Cells(lngLastRow, lngColumn + COLUMN_OFFSET)).Value = avntValues2
            
        End If
    Next
End Sub

Private Sub QuickSort(ByVal pvlngLBound As Long, ByVal pvlngUBound As Long, _
        ByRef avntValuses1 As Variant, ByRef avntValuses2 As Variant)

    
    Dim ialngIndex1 As Long, ialngIndex2 As Long
    Dim vntElement As Variant, vntBuffer As Variant
    
    ialngIndex1 = pvlngLBound
    ialngIndex2 = pvlngUBound
    
    vntBuffer = avntValuses1((pvlngLBound + pvlngUBound) \ 2, 1)
    
    Do
        
        Do While avntValuses1(ialngIndex1, 1) < vntBuffer
            ialngIndex1 = ialngIndex1 + 1
        Loop
        
        Do While vntBuffer < avntValuses1(ialngIndex2, 1)
            ialngIndex2 = ialngIndex2 - 1
        Loop
        
        If ialngIndex1 <= ialngIndex2 Then
            
            vntElement = avntValuses1(ialngIndex1, 1)
            avntValuses1(ialngIndex1, 1) = avntValuses1(ialngIndex2, 1)
            avntValuses1(ialngIndex2, 1) = vntElement
            
            vntElement = avntValuses2(ialngIndex1, 1)
            avntValuses2(ialngIndex1, 1) = avntValuses2(ialngIndex2, 1)
            avntValuses2(ialngIndex2, 1) = vntElement
            
            ialngIndex1 = ialngIndex1 + 1
            ialngIndex2 = ialngIndex2 - 1
            
        End If
    Loop Until ialngIndex1 > ialngIndex2
    
    If pvlngLBound < ialngIndex2 Then Call QuickSort(pvlngLBound, ialngIndex2, avntValuses1, avntValuses2)
    If ialngIndex1 < pvlngUBound Then Call QuickSort(ialngIndex1, pvlngUBound, avntValuses1, avntValuses2)
    
End Sub

Private Sub ClearErrors(ByRef pravntValues As Variant)
    
    Dim ialngIndex As Long
    
    For ialngIndex = LBound(pravntValues) To UBound(pravntValues)
        
        If IsError(pravntValues(ialngIndex, 1)) Then
            
            Select Case pravntValues(ialngIndex, 1)
                Case CVErr(xlErrDiv0)
                    pravntValues(ialngIndex, 1) = "#DIV/0!"
                Case CVErr(xlErrNA)
                    pravntValues(ialngIndex, 1) = "#N/A"
                Case CVErr(xlErrName)
                    pravntValues(ialngIndex, 1) = "#NAME?"
                Case CVErr(xlErrNull)
                    pravntValues(ialngIndex, 1) = "#NULL!"
                Case CVErr(xlErrNum)
                    pravntValues(ialngIndex, 1) = "#NUM!"
                Case CVErr(xlErrRef)
                    pravntValues(ialngIndex, 1) = "#REF!"
                Case CVErr(xlErrValue)
                    pravntValues(ialngIndex, 1) = "#VALUE!"
            End Select
        End If
    Next
End Sub

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman
Geschrieben am: 22.10.2020 10:37:39

Hab mal den Debugger bemüht. Anscheinend gibt es einige Daten, wo die zugehörige Spalte eine andere Anzahl an Daten besitzt. Quasi Spalte A hat 5 Zahlen aber die zugehörige nur 4 Werte.

Ich werde diese manuell aussoritieren und es dann testen

Danke

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 22.10.2020 10:55:01

Hallo Roman,

das ist kein Problem, ich ermittle die maximale Anzahl von Zeilen aus beiden Spalten.

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman
Geschrieben am: 22.10.2020 11:28:00

Sobald ich mehr oder weniger als 250 Spalten habe geht es, sortiert aber Schwachsinn. bei genau 250 spalten kommt der Laufzeitfehler. Wie löst sich das Problem?

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 22.10.2020 11:55:21

Hallo Roman,

kannst du die Mappe mit dem Tabellenblatt hochladen?

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman
Geschrieben am: 22.10.2020 12:22:56

Aus Datenschutzgründen meiner Arbeit leider nein

Betrifft: AW: unabhängig Spalten sortieren
von: Nepumuk
Geschrieben am: 22.10.2020 12:24:36

Hallo Roman,

dann kann ich dir leider nicht weiterhelfen.

Gruß
Nepumuk

Betrifft: AW: unabhängig Spalten sortieren
von: Roman
Geschrieben am: 22.10.2020 12:28:36

Ok, danke trotzde, für die viele Hilfe!

Betrifft: AW: unabhängig Spalten sortieren
von: Roman
Geschrieben am: 22.10.2020 10:50:43

Ich bekomme den Laufzeuitfehler nicht raus. Also entweder übersehe ich Daten oder es liegt ein anderer Fehler vor.

Danke trotzdem.

Gruß Roman

Beiträge aus dem Excel-Forum zum Thema "unabhängig Spalten sortieren "