Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema RefEdit
BildScreenshot zu RefEdit RefEdit-Seite mit Beispielarbeitsmappe aufrufen

mehrfache Werte aus einem Bereich


Betrifft: mehrfache Werte aus einem Bereich von: erichm
Geschrieben am: 07.01.2018 09:25:34

Hallo,

ich habe eine umfangreiche Tabelle, bei denen in den Spalten C bis IS, Zeilen 2 bis 151 viele unterschiedliche, aber auch viele doppelte / mehrfache Werte stehen.

Daraus will ich eine Liste in einer Spalte erstellen, die jeden vorkommenden Wert (egal ob einfach oder mehrfach) nur einmal auflistet.

Ich habe grundsätzlich bereits eine Formellösung gefunden:
http://www.excelformeln.de/formeln.html?welcher=194

Diese Formel ist aber nur für zwei Spalten geschrieben (in meinem Fall für die Spalten C und D). Soweit kann ich die Formel für mich übertragen.

Jetzt scheitere ich aber daran, die Formel so zu erweitern, dass auch die Spalten ab E berücksichtigt werden.

Evtl. gibt es ja mit AGGREGAT eine Lösung?

Vielen Dank.

mfg

  

Betrifft: AW: mehrfache Werte aus einem Bereich von: Hajo_Zi
Geschrieben am: 07.01.2018 09:39:48


Tabelle2

 J
131538
229820
3330
4290
5252
6216
7182
80
90
100
110
120
130

verwendete Formeln
Zelle Formel Bereich N/A
J1=MAX(C2:I151)  
J2:J13{=MAX(WENN(C2:I151<J1;C2:I151))}$J$2 
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 25.14 einschl. 64 Bit



GrußformelHomepage

Ich gebe keinen Dank für eine Rückmeldung, da ich durch solche Beiträge nicht meine Beitragszahl erhöhen muss.
Also ich schreibe keine Beiträge mit dem Betreff "Gerne u. Danke für die Rückmeldung....."
Rückmeldung ist ja in der Heutigen Zeit nicht üblich und die wenigen die eine Rückmeldung geben,
mögen mir das verzeihen, das kein Danke für eine Rückmeldung kommt.

Beiträge von Werner, Luc, robert und folgende lese ich nicht.


  

Betrifft: AW: mehrfache Werte aus einem Bereich von: Sepp
Geschrieben am: 07.01.2018 09:44:55

Hallo Erich,

wenn es sich nur um Zahlen handelt, dann so.

Tabelle1

 A
1Werte
21
32
43
54
65
76
87
98

Formeln der Tabelle
ZelleFormel
A2=MIN($C$2:$IS$151)
A3=WENNFEHLER(AGGREGAT(15;6;$C$2:$IS$151/($C$2:$IS$151>A2); 1); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Formel aus A3 runterziehen.

Tabelle1

 A
1Werte
21
32
43
54
65
76
87
98

Formeln der Tabelle
ZelleFormel
A2=MIN($C$2:$IS$151)
A3=WENNFEHLER(AGGREGAT(15;6;$C$2:$IS$151/($C$2:$IS$151>A2); 1); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8



Gruß Sepp



  

Betrifft: AW: mehrfache Werte aus einem Bereich von: erichm
Geschrieben am: 07.01.2018 10:23:50

Hallo Sepp,

danke - aber es sind wie in dem Formelbeispiel "gemischte Inhalte", also nicht nur Zahlen.

Da das Formelbeispiel den erstmals vorkommenden Wert schön durchnumeriert hat, scheitere ich einfach daran, die jeweiligen Werte in den linken Spalten (im Formelbeispiel A + B) durch die aufsteigenden Zahlen in den rechten Spalten (im Formelbeispiel D + E) zu finden.

mfg


  

Betrifft: AW: mit einer AGGREGAT()-Formel möglich, aber ... von: ... neopa C
Geschrieben am: 07.01.2018 10:59:18

Hallo Erich,

... bei Deiner auszuwertenden Datenmenge dürfte der PC zumindest arg ins "Schwitzen" kommen, wenn er nicht gar schon "in die Knie" geht. Teste mal, denn meine nachfolgende Lösungsformel ist bestenfalls bei einer eher einmaligen Lösung zu empfehlen.

Angenommen Du willst Die Daten in Spalte A ermitteln, dann in A2:

=WENNFEHLER(INDEX(A:IS;AGGREGAT(15;6;(ZEILE(C$2:IS$151)+SPALTE(C1:IS151)%%)/(C$2:IS$151<>"") /(ZÄHLENWENN(A$1:A1;C$2:IS$151)=0);1);REST(AGGREGAT(15;6;(ZEILE(C$2:IS$151)+SPALTE(C1:IS1)%%) /(C$2:IS$151<>"")/(ZÄHLENWENN(A$1:A1;C$2:IS$151)=0);1);1)*10000);"")


und die Formel weit genug nach unten kopieren.

Gruß Werner
.. , - ...


  

Betrifft: AW: mit einer AGGREGAT()-Formel möglich, aber ... von: erichm
Geschrieben am: 07.01.2018 15:34:31

Hallo Werner,

danke für die Lösung. Stimmt, die Datenmenge ist zu groß. Ist aber kein Problem
- diese Ermittlung benötige ich selten im Projekt
- ich kann die Spaltenanzahl in Häppchen aufteilen

Somit klappt es!!

PS: Mein "VBA-Problem"
https://www.herber.de/cgi-bin/callthread.pl?index=1596321#1596668
besteht leider immer noch. Nutze derzeit nur sehr sporadisch und wenn notwendig .xlsm-Dateien. Ich warte mal ab, vielleicht bringt ein neues Update von Windows wieder eine Berichtigung. Der Support von MS konnte noch nicht helfen.

Habe gerade gesehen, dass meine aktuelle Anfrage von Werner mit einem Makro gelöst wurde - das konnte ich nutzen.

Besten Dank!

mfg



  

Betrifft: per Makro eine Alternative von: Werner
Geschrieben am: 07.01.2018 11:21:07

Hallo Erich,

mittels ScriptingDictionary.
Ausgabe der Liste im gleichen Blatt in Spalte A (anpassen).

Option Explicit

Sub keine_Doppelten()
Dim raZelle As Range, scrDic As Object
   
Application.ScreenUpdating = False
Set scrDic = CreateObject("Scripting.Dictionary")

With Sheets("Tabelle1") 'Blattname anpassen
    For Each raZelle In .Range(.Cells(2, 3), .Cells(151, 253))
        scrDic(raZelle.Value) = 0
    Next raZelle
    'hier Zieladresse anpassen
    .Range("A1").Resize(scrDic.Count) = WorksheetFunction.Transpose(scrDic.keys)
End With

Set scrDic = Nothing
Application.ScreenUpdating = True
End Sub
Gruß Werner


  

Betrifft: AW: per Makro eine Alternative von: erichm
Geschrieben am: 07.01.2018 15:35:26

Hallo Werner,

besten Dank - hat hervorragend funktioniert!!

mfg


  

Betrifft: Gerne u. Danke für die Rückmeldung von: Werner
Geschrieben am: 07.01.2018 19:28:46




Beiträge aus dem Excel-Forum zum Thema "mehrfache Werte aus einem Bereich"