Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Formel für Sortieren ohne Duplikate!
#1
Hallo liebe Excelgemeinde,


für folgendes Vorhaben benötige ich wieder eure Hilfe! Smile

In mein Tabellenblatt "LN" möchte ich in Stalte N ab N2 die Begriffe aus Spalte AO aus dem Tabellenblatt "Produkte" alphabetisch sortieren und ohne Duplikate auflisten!

=WENN(produkte!AO2="";"";INDEX(produkte!$AO$2:$AO$10000;VERGLEICH(KKLEINSTE(ZÄHLENWENN(INDIREKT("produkte!$AO$2:$AO$"&MAX(ZEILE(produkte!$2:$10000)*(produkte!$AO$2:$AO$10000<>"")));"<"&INDIREKT("produkte!$AO$2:$AO$"&MAX(ZEILE(produkte!$2:$10000)*(produkte!$AO$2:$AO$10000<>""))));ZEILE(produkte!A1));ZÄHLENWENN(INDIREKT("produkte!$AO$2:$AO$"&MAX(ZEILE(produkte!$2:$10000)*(produkte!$AO$2:$AO$10000<>"")));"<"&INDIREKT("produkte!$AO$2:$AO$"&MAX(ZEILE(produkte!$2:$10000)*(produkte!$AO$2:$AO$10000<>""))));0)))
Diese Formel habe ich aus dem Internet, diese sortiert zwar die Einträge aber mit Duplikate!

Wie müsste ich diese ändern, damit es ohne Duplikate funktioniert?

Vielen lieben Dank für eure Mühe

LG
Alexandra
to top
#2
Hi Alexandra,

mal als Ansatz ohne genaue Kenntnis deiner Tabelle. Vielleicht hilft dir dies schon mal weiter.

excelformeln.de / Spezialfilter ohne Duplikate
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#3
Hallo Alexandra

 N
2wurst

ZelleFormel
N2{=WENNFEHLER(INDEX(Produkte!AO:AO;KKLEINSTE(WENN(HÄUFIGKEIT(VERGLEICH(Produkte!$AO$2:$AO$9;Produkte!$AO$2:$AO$9;0);VERGLEICH(Produkte!$AO$2:$AO$9;Produkte!$AO$2:$AO$9;0))>0;ZEILE(Produkte!$AO$2:$AO$9));ZEILEN(N$2:N2)));"")}
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
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
to top
#4
Hallo Alexandra,

da Du ja auch VBA einsetzt, hier eine Lösung mit Spezialfilter und Sortierung danach plus das Einlesen
des Bereichs in eine Combobox:

Code:
Option Explicit

Sub Makro1()
  
   Dim lngLetzte As Long
  
   Worksheets("LN").Columns("N").ClearContents
  
   With Sheets("Produkte")
      lngLetzte = .Cells(.Rows.Count, "AO").End(xlUp).Row
       Sheets("Produkte").Range("AO1:AO" & lngLetzte).AdvancedFilter Action:=xlFilterCopy, _
           CopyToRange:=Sheets("LN").Range("N1"), Unique:=True
   End With
  
   With Sheets("Ln")
      lngLetzte = .Cells(.Rows.Count, "N").End(xlUp).Row
       .Sort.SortFields.Clear
       .Sort.SortFields.Add Key:=Range("N1"), _
           SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      With .Sort
          .SetRange Range("N1:N" & lngLetzte)
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      lngLetzte = .Cells(.Rows.Count, "N").End(xlUp).Row
      With ComboBox41
           .ListRows = 12 'Anzahl der Dropdownlist Einträge
           .Clear
           .List = ThisWorkbook.Worksheets("Ln").Range("N2:N" & lngLetzte).Value  'Bereich N2:N bis letzte Zeile aus Tabelle Liste in Combobox einlesen
           .Style = fmStyleDropDownCombo 'freie Eintragungen möglich
           '.ListIndex = 1  'Setze combobox auf ersten Eintrag
        End With
   End With
End Sub

Der Code ist von mir aufgezeichnet und nachbearbeitet worden.
Wenn Du ihn in einer Userform einsetzen möchtest, dann kannst Du im Activate oder Initialize Ereignis
der Userform die Prozedur aufrufen. Die Prozedur kann sich in einem allgemeinen Modul befinden.
Gruß Atilla

Excel 2007
to top
#5
Hallo shift-dell,


habe diese auf meine Bedürfnisse angepasst, es funktioniert aber es sortiert nicht!

Irgendeine Idee?


DAnke
LG
Alexandra
to top
#6
Hallo Alexandra

Zitat:es funktioniert aber es sortiert nicht!
Stimmt, das habe ich zu spät bemerkt.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
to top
#7
Hallo Alexandra,

... nachfolgend zeig ich zwar auf, dass es formeltechnisch realisierbar ist, aber gleichzeitig rate ich bei der von Dir angegebenen 10.000 auszuwertenden Datensätzen von einer solchen Formellösung ab. Hier werden die PC-Ressourcen dann doch arg strapaziert.

Wenn eine einmalige Lösung gesucht wird oder auch, wenn für Dich eine händische Aktualisierung der sortierten Listung (nach neuen Daten) ausreichend ist/wäre, dann gibt es eine bessere und einfachere wie schnellere Methode: PIVOTauswertung.

Hier meine Formelkonstrukte (Formel N3 nach unten kopieren), die allerdings noch nicht optimiert sind, denn dafür ist mir mittlerweile schon wieder zu warm.

LN

N
1Sortierte Ausgabe
ohne Duplikate
2aber
3der
4doppelten
5gelistet
6listen
7nicht
8soll
9sortiert
10Text
11werden
12
Formeln der Tabelle
ZelleFormel
N2{=INDEX(Produkte!AO:AO;VERGLEICH(MAX(ZÄHLENWENN(Produkte!AO2:AO9999;">"&Produkte!AO2:AO9999));ZÄHLENWENN(Produkte!AO2:AO9999;">"&Produkte!AO2:AO9999)Wink+1)}
N3{=WENN(ZEILE(Produkte!A1)>=SUMME(N(HÄUFIGKEIT(ZÄHLENWENN(Produkte!AO$2:AO$9999;"<="&Produkte!AO$2:AO$9999);ZEILE($N$2:$N$9999))>0));"";INDEX(Produkte!AO:AO;VERGLEICH(MAX(ZÄHLENWENN(Produkte!AO$2:AO$9999;">="&Produkte!AO$2:AO$9999)*ISTNV(VERGLEICH(Produkte!AO$2:AO$9999;N$2:N2Wink));ZÄHLENWENN(Produkte!AO$2:AO$9999;">="&Produkte!AO$2:AO$9999)*ISTNV(VERGLEICH(Produkte!AO$2:AO$9999;N$2:N2Wink)Wink+1))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Produkte

AO
1Unsortierte Texte
mit Duplikate
2Text
3der
4sortiert
5gelistet
6werden
7soll
8
9aber
10der
11doppelten
12Text
13nicht
14listen
15soll
16

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
[-] Folgende(r) 1 Benutzer sagt Danke an neopa für diesen Beitrag:
cysu11
to top
#8
Hallo Werner,


funktioniert perfekt und du hast Recht, das bremst den PC ganz schön aus! Smile

Habe mir deswegen einen Code gebastelt, funktioniert bedeutend schneller, für die die das interessiert:

Code:
Sub SortierenLieferanten()
    ThisWorkbook.Sheets("produkte").Select
    Range("AO:AO").Select
    Selection.Copy
    Sheets("LN").Select
    Range("N1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Range("N2:N1000").Select
    ActiveSheet.Range("N2:N1000").RemoveDuplicates Columns:=1, Header:=xlNo
        ActiveSheet.Range("N2:N1000").Select
         Selection.Sort Key1:=ActiveSheet.Range("N2"), Order1:=xlAscending, Header:=xlGuess, _
         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
         DataOption1:=xlSortNormal
    cmdAbbrechen_Click
End Sub

Wenn jemand diesen optimieren möchte, gerne!! Smile

Vielen Dank und einen schönen Feiertag noch
LG
Alexandra
to top
#9
Hi Alexandra,

versuche es mal so ohne die Selects:

Code:
Option Explicit

Sub SortierenLieferanten()
    ThisWorkbook.Sheets("produkte").Range("AO:AO").Copy
    Sheets("LN").Range("N1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    With ActiveSheet.rang("N2:N1000")
       .RemoveDuplicates Columns:=1, Header:=xlNo
       .Sort Key1:=ActiveSheet.Range("N2"), Order1:=xlAscending, Header:=xlGuess, _
         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
    cmdAbbrechen_Click
End Sub
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#10
Hallo Alexandra,

Zitat:Wenn jemand diesen optimieren möchte, gerne!!
Einen optimalen Code habe ich oben doch schon eingestellt.

Für solche Fälle eignet sich der Spezialfilter am besten, da sehr schnell, weil Filtern ohne Duplikate
und das Kopieren an andere Stelle passiert gleichzeitig und sind nur zwei Zeilen Code.
Das Andere ist aufgezeichneter und bereinigter Code zum Sortieren.
Der in frage kommende Bereich wird dynamisch eingelesen.
Gruß Atilla

Excel 2007
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Sortieren mit Makro ohne Überschrift Excel 2003 Babsi 0 13 Vor 1 Stunde
Letzter Beitrag: Babsi
  Summe ohne Duplikate mit mehreren Kriterien ConDucTi 7 160 08.11.2016, 13:40
Letzter Beitrag: ConDucTi
  Lösung ohne Marko nur mit Formel artcreativity 14 325 31.10.2016, 15:20
Letzter Beitrag: artcreativity
  Formel kopieren ohne Bezüge in andere Dokumente Simon 5 186 25.09.2016, 07:47
Letzter Beitrag: IchBinIch
  Liste in eine andere Spalte ausgeben ohne Duplikate Rabe79 7 321 19.08.2016, 20:26
Letzter Beitrag: Gast 123
  Duplikate nach Kriterium einreihen Telematix 17 919 21.07.2016, 16:01
Letzter Beitrag: Telematix
  Sortieren ohne "Sortierfunktion" professorte 18 632 02.07.2016, 14:32
Letzter Beitrag: Jockel
  Text-Duplikate entfernen Mahadmakandis 26 1.549 02.07.2016, 11:45
Letzter Beitrag: Mahadmakandis
Photo Spalten sortieren und leere Zeilen ohne Lücke auffüllen XCow 3 396 16.06.2016, 16:15
Letzter Beitrag: XCow
  Quality Control Program -> ALLE Duplikate finden Spike87 13 653 10.06.2016, 14:17
Letzter Beitrag: Fennek

Gehe zu:


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