Registriert seit: 27.04.2014
Version(en): Privat: Office Home & Business 2019 / Arbeit: MS365
Hallo liebe Excelgemeinde,
für folgendes Vorhaben benötige ich wieder eure Hilfe! :)
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
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
Hi Alexandra,
mal als Ansatz ohne genaue Kenntnis deiner Tabelle. Vielleicht hilft dir dies schon mal weiter.
excelformeln.de / Spezialfilter ohne Duplikate
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Registriert seit: 12.04.2014
Version(en): Office 365
Hallo Alexandra
Zelle | Formel | 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!
... Detlef
Meine Beiträge können Ironie oder Sarkasmus enthalten.
Registriert seit: 14.04.2014
Version(en): 2003, 2007
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
Registriert seit: 27.04.2014
Version(en): Privat: Office Home & Business 2019 / Arbeit: MS365
Hallo shift-dell,
habe diese auf meine Bedürfnisse angepasst, es funktioniert aber es sortiert nicht!
Irgendeine Idee?
DAnke
LG
Alexandra
Registriert seit: 12.04.2014
Version(en): Office 365
Hallo Alexandra
Zitat:es funktioniert aber es sortiert nicht!
Stimmt, das habe ich zu spät bemerkt.
Wir sehen uns!
... Detlef
Meine Beiträge können Ironie oder Sarkasmus enthalten.
Registriert seit: 05.05.2014
Version(en): 2010 + 2016 Home and Business
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 | 1 | Sortierte Ausgabe ohne Duplikate | 2 | aber | 3 | der | 4 | doppelten | 5 | gelistet | 6 | listen | 7 | nicht | 8 | soll | 9 | sortiert | 10 | Text | 11 | werden | 12 | | Formeln der Tabelle | Zelle | Formel | N2 | {=INDEX(Produkte!AO:AO;VERGLEICH(MAX(ZÄHLENWENN(Produkte!AO2:AO9999;">"&Produkte!AO2:AO9999));ZÄHLENWENN(Produkte!AO2:AO9999;">"&Produkte!AO2:AO9999);)+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:N2;)));ZÄHLENWENN(Produkte!AO$2:AO$9999;">="&Produkte!AO$2:AO$9999)*ISTNV(VERGLEICH(Produkte!AO$2:AO$9999;N$2:N2;));)+1))} |
| Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! | Matrix verstehen | Produkte | AO | 1 | Unsortierte Texte mit Duplikate | 2 | Text | 3 | der | 4 | sortiert | 5 | gelistet | 6 | werden | 7 | soll | 8 | | 9 | aber | 10 | der | 11 | doppelten | 12 | Text | 13 | nicht | 14 | listen | 15 | soll | 16 | | Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Folgende(r) 1 Nutzer sagt Danke an neopa für diesen Beitrag:1 Nutzer sagt Danke an neopa für diesen Beitrag 28
• cysu11
Registriert seit: 27.04.2014
Version(en): Privat: Office Home & Business 2019 / Arbeit: MS365
Hallo Werner,
funktioniert perfekt und du hast Recht, das bremst den PC ganz schön aus! :)
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!! :)
Vielen Dank und einen schönen Feiertag noch
LG
Alexandra
Registriert seit: 10.04.2014
Version(en): 2016 + 365
09.06.2014, 12:23
(Dieser Beitrag wurde zuletzt bearbeitet: 09.06.2014, 12:25 von Rabe.)
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
Registriert seit: 14.04.2014
Version(en): 2003, 2007
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
|