Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Liste querbeet - soll sortiert werden





Frage

Neue Frage: Hallo und mal wieder was (zumindest für mich) kniffliges! habe 3 Spalten A1 = Artikelnr. B1 = Hauptkatalog (HK) C1 = Schnäppchenkatalog (SK) D1 = Sonderkatalog (SO) soweit so gut, nun kann die Artikelnr. mehrmals auftauchen und auf Seiten zu finden sein also durchaus: Artikel---------Hauptkatalog Seite----------SK Seite-------------SO Seite 2------------------------4----------------------------------------------------------------- 3------------------------ 5 ----------------------------------------------------- 5----------------------------------------------------------------------------------9--- 4-----------------------_---------------------------------3------------------------------- 2----------------------10---------------------------------------------------------------- 3--------------------------------------------------------6-------------------------------- 5------------------------8---------------------------------------------------------------- usw. möchte jetzt diese Tabelle aber in einem neuen Blatt wie folgt sortiert haben: A=Artikelnr (Aufsteigend sortiert) dahinter dann in B die Seitennr z.B. A---------------------------B----------------------C------------------------D....... 2---------------------------4----------------------10------------------------------ 3---------------------------5----------------------6-------------------------9------ 4---------------------------3--------------------------------------------------------- 5---------------------------8-----------------------9-------------------------------- usw. Hat da jemand ne Idee? Wie krieg ich also Excel dazu aus der Original Tabelle a.) Alle Seiten einer Artikelnr. zuzuordnen b.) Mir dieses in einer neuen Liste nach Artikelnr. sortiert anzuzeigen? Danke für eure Hilfe Gruß Volker

Antwort 1 von Saarbauer

Hallo,

so auf den ersten Blick, wahrscheinlich nur mit Makro zu lösen.

Gruß

Helmut

Antwort 2 von finger59

Hi Volker,

leider habe ich im Moment keine Möglichkeit Dir Dein Beispiel ins Netz zu stellen.

Ich habe Deine Anfrage mit einer Pivottabelle umgesetzt.

Deine Werte markieren - Pivottabelle aufrufen - den markierten Bereich als Datenbereich übernehmen und neues Blatt auswählen.

Dann die Artikel-Nr. in den linken Bereich - Zeilenfelder hierher schieben - reinziehen, dann die Hauptkatalog, Sonder- u. Schnäppchenkatalog in den Datenbereich reinziehen.

Das was Du jetzt siehst wird Dich vielleicht erstmal erschrecken, da ein kleines Chaos auf dem Bildschirm ist - aber nicht verzweifeln...

Jetzt mit der rechten Maustaste auf Daten klicken und in dem nun erscheinendem Fenster - Reihenfolge und dort verschieben in Spalte auswählen, so daß die drei Oberbegriffe für Deine Kataloge in drei Spalten stehen.

Als nächstes müssen wir die Zahlen bereinigen. Dazu immer einzeln vorgehen und z.B. auf Anzahl von Hauptkatalog mit der rechten Maustaste klicken. Auch jetzt erscheint ein neues Fenster. Dort wählst Du bitte folgendes aus: Feldeigenschaften - und dort unter Zusammenfassung mit den Begriff Produkt. Das wiederholst Du nun für die anderen zwei Kataloge.

Die Summenzeile bekommen wir auch noch weg...
Dazu rechter Mausklick auf Daten und unter Tabellenoptionen den Haken auf Summenbildung auf Spalte rausnehmen.

Jetzt kann man noch die Überschriften umbenennen - einfach auf die Zelle gehen wie - Produkt von Hauptkatalog und z.B. das Produkt von löschen.

Fertig ist die Pivottabelle... Da die Artikel-Nr. in der Zeilenfeldern steht wird diese automatisch sortiert.

Alle vorhandenen Seiten, die zu diesem Artikel gehören werden aufgeführt.

Allerdings einen Schönheitsfehler hat das ganze schon.

Wenn ein Artikel in einem Katalog zweimal vorkommt - z.B. Seite 1 (weil besonderes Angebot) und dann nochmal auf Seite 30, dann gibt es keine vernünftige Aussage mehr. - Bei Produkt werden dann die beiden Seitenzahlen multipliziert (Ergebnis hier 1x30 = 30 - bei Summe Addiert 1 + 30 = 31 .. bei Anzahl = 2
usw.. wäre der Artikel auf Seite 2 und 30 wäre bei Produkt halt die Zahl 60.

Vorteil: sollte sich irgendwo eine Seite ändern, so genügt in der Pivottabelle ein Klick mit der rechten Maustaste auf eines der grauen Felder und dort aktualisieren auswählen und schon ist die Tabelle wieder auf den neuesten Stand.

Am besten aber zum Tabellengestalten die Daten aus der Pivottabelle rauskopieren und in ein neues Arbeitsblatt rüberkopieren und dann die Felder mit Rahmen usw. formatieren. Das kann man zwar auch in der Pivottabelle, aber sobald dort eine Aktualisierung stattgefunden hat, sind diese Formatierungen weg. - Leider -

So... ich hoffe das diese Info Dich nicht negativ beeinflusst hat, aber probiere es einfach mal mit Deinen Zahlen hier aus und Du wirst sehen, solange wie kein Artikel zweimal in dem selben Katalog auftaucht, wird es funktionieren.

Falls es Probleme geben sollte, dann melde Dich einfach nochmal...

In diesem Sinne... have a nice Day... Gruß Helmut

Antwort 3 von Saarbauer

Hallo,

versuch es mal damit

Sub tabellet_bereinigen()
    Sheets("Tabelle2").Select
    Cells.Select
    Selection.ClearContents
    Sheets("Tabelle1").Select
    Range("A1:D" & Range("A65536").End(xlUp).Row).Select
    Selection.Copy
    Sheets("Tabelle2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    For i = Range("A65536").End(xlUp).Row To 2 Step -1
        If Range("B" & i).Value <> "" Then Range("B" & i).Value = Range("B" & i).Value & " H"
        If Range("C" & i).Value <> "" Then Range("C" & i).Value = Range("C" & i).Value & " S"
        If Range("D" & i).Value <> "" Then Range("D" & i).Value = Range("D" & i).Value & " O"
        If Range("D" & i).Value = "" Then Range("D" & i).Delete Shift:=xlToLeft
        If Range("C" & i).Value = "" Then Range("C" & i).Delete Shift:=xlToLeft
        If Range("B" & i).Value = "" Then Range("B" & i).Delete Shift:=xlToLeft
    Next i
    For i = Range("A65536").End(xlUp).Row To 3 Step -1
        If Range("A" & i).Value = Range("A" & i - 1).Value Then
            Range(Cells(i, 2), Cells(i, Range("IV" & i).End(xlToLeft).Column)).Cut
            Cells(i - 1, Range("IV" & i - 1).End(xlToLeft).Column + 1).Select
            ActiveSheet.Paste
            Rows(i).Delete Shift:=xlUp
        End If
    Next i
End Sub


hinter die Seiten habe ich einen Buchstaben gesetzt "H""S""O" damit man erkennen kann woher die Seite kommt

Gruß

Helmut

Antwort 4 von finger59

Hallo Volker,

@Saarbauer -

neidlos muss ich da die Variante von Helmut als die bessere anerkennen und werde mir das Makro auch mal für evtl. eigene Projekte abspeichern.

Tolle Arbeit.... und die Nachteile der Pivottabelle mit den doppelten Einträgen auch gelöst. Echt super..!!!!

In diesem Sinne... have a nice Day... Gruß Helmut

Antwort 5 von Saarbauer

Hallo,

danke für das Lob, aber wie ihr seht musste ich etwas basten bis es mir gefallen hat. Ob es @korfuweb zusagt müssen wird abwarten

Gruß

Helmut

Antwort 6 von korfuweb

Zunächst mal Danke für eure Hilfe,
aber irgendwie klappt das nicht
@Saarbauer

wenn ich dein Makro probiere hängt er immer bei mit einem Laufzeitfehler in folgender Pasage:
" Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal"

mhhh ich versuch mich schon seit stunden woran es hackt !

Gruß Volker

Antwort 7 von Saarbauer

Hallo,

ich nehme an du hast das Makro rüber kopiert, da ich zwischen deiner und meiner Zeile keine Differenz feststelle.

Ich vermute daher es liegt an der von dir genutzten Excelversion, meine Funktion ist mit 2003 erzeugt. Da es bei @finger59 anscheinend auch funktioniert, liegt die Vermutung nahe.

Mit dieser Funktion werden alle zeilen nach der Artikelnummer sortiert. Diese Zeile könntest du als Makro aufzeichen und dan entsprechen in das oben eingestellte Makro entsprechend einbauen. Sonst musst du mal die Tabelle zur Verfügung stellen

Gruß

Helmut

Antwort 8 von korfuweb

Ok, liegt irgendwie an der Excel Version.
Hier auf meinem Win 2000 Rechner läuft Excel 2000 - da funzt das Macro nicht!
Drüben am XP Rechner läuft 2002 - da geht es.

Mhhh kann man das Ding modifizieren das es ab 2000 läuft, da ich nicht mit Bestimmtheit sagen kann welche Version bei den Kollegen gefahren wird !

Danke für Deine Mühe.
Gruß Volker

Antwort 9 von Saarbauer

hallo,

werde heute abend, wenn ich es schaffe mal nach sehen, da ich noch einen Rechner mit Excel 2000 , aber XP habe.

Wenn es auf dem Anderen Rechner läuft, ist es denn vom Grundsatz her ok?

Gruß

Helmut

Antwort 10 von korfuweb

Ja ist bisher perfekt !
Leider halt etwas kniffelig mit den Excel Versionen.
Aber passt super.
Vielen Dank für deine Mühe.

Gruß Volker

Antwort 11 von Saarbauer

Hallo,

ersetze diese Zeile

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal


durch

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes,         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


dann müsste es klappen (mit Excel 2000 und Win XP) getestet

Gruß

Helmut

Antwort 12 von korfuweb

Hallo Helmut,

du bist ja ein Excel oder VBA Genie !
Funktioniert ;-)

Aber ein klitzekleines Problemchen hab ich noch *Grins*

bei meiner Auswertung kommt z.B. jetzt raus:
555-345-4 0 0 62

gibt es ne Möglichkeit die "0" zu eliminieren und die 1 "wirkliche" Zahl nach vorne zu schieben?

Gruß Volker

Antwort 13 von Saarbauer

Hallo,

ganz verstanden habe ich es nicht

Zitat:
bei meiner Auswertung kommt z.B. jetzt raus:
555-345-4 0 0 62

gibt es ne Möglichkeit die "0" zu eliminieren und die 1 "wirkliche" Zahl nach vorne zu schieben?


hängt wahrscheinlich mit den Abfragen zusammen, tausche diese Zeilen gegen diese hier aus, oder entsprechend ergänzen

If Range("D" & i).Value = "" or Range("D" & i).Value = 0 Then Range("D" & i).Delete Shift:=xlToLeft
If Range("C" & i).Value = "" or Range("C" & i).Value = 0 Then Range("C" & i).Delete Shift:=xlToLeft
If Range("B" & i).Value = "" or Range("B" & i).Value = 0 Then Range("B" & i).Delete Shift:=xlToLeft

Gruß

Helmut

P.S. hat nichts mit Genie zu tun, nur gewusst wo. Trotzdem danke für das Lob.

Antwort 14 von korfuweb

Vieeeelllllen Dank !

Und so Wahnsinnig schnelle Antwort -

mein Problem ist gelöst - einfach genial !

Danke nochmals für die Hilfe !

Gruß Volker

Antwort 15 von Saarbauer

Hallo,

nur eine Anmerkung für die Zukunft. Gebe beim nächsten mal die Excelversion an, dann ist das ein oder andere Problem im Vorfeld zu lösen.

Aus deiner Liste war nicht ersichtlich, dass in der Tabelle, wenn keine Seite vorhanden ist eine 0 steht, daher bin ich von einem Leerfeld ausgegangen. Daher meine Abfrage mit

Range("D" & i).Value = ""

aber jetzt haben wir es ja hinbekommen.

Danke für die Bewertung

Gruß

Helmut

Antwort 16 von korfuweb

Hallo Helmut !

Habe noch ein Problemchen mit der Formel - und zwar wenn ein Artikel 2mall auf der gleichen Seite auftaucht steht dann dort:

5333-4444 69 69 3

geht das irgendwie das die 69 nur einmal auftaucht.

Hab noch einen BUG den ich aber erst mal versuche selbst rauszufinden.
Es kommt nämlich vor, das er ab und an beim sortieren die Artikelnr. mit in die Seitenfelder reinhaut.
Nicht immer, sind aber im Augenblick bei 110 Artikel bei 2 bei denen er das macht - naja ich komm bestimmt dahinter warum er das tut.

Gruß
Volker

Antwort 17 von korfuweb

Ok den Bug mit der Artikelnr. in der sortierten Liste hab ich gelöst ! Lag an den Filtereinstellungen der Quelldatei!

Antwort 18 von Saarbauer

Hallo,

Füge die Zeilen hier ein

If Range("D" & i).Value <> "" Then Range("D" & i).Value = Range("D" & i).Value & " O"
If Range("B" & i).Value = Range("C" & i).Value Then Range("C" & i).Value = """
If Range("B" & i).Value = Range("D" & i).Value Then Range("D" & i).Value = """
If Range("C" & i).Value = Range("D" & i).Value Then Range("D" & i).Value = """

If Range("D" & i).Value = "" Then Range("D" & i).Delete Shift:=xlToLeft

dann müsste das Problem auch gelöst sein.
Ich habe die Löschung doppelter Seitennummern erst nach der Katalogzuordnung gesetzt, da es durchaus möglich ist, dass in den untersiedlichen Kataolgen die Artikel zufällig auf der gleichen Seite stehen.

Gruß

Helmut