Xte-kleinste Zahl mit Bedingung

Forumthread: Xte-kleinste Zahl mit Bedingung

18.02.2016 16:33:46

MAM

Hallo zusammen
Ich habe wieder einmal ein Problem, bei dem ich keine Lösung im Netz gefunden habe. Ich habe eine Tabelle mit mehreren Spalten und über 30000 Einträgen. Eine davon hat eine Rangliste über die Gesamte Liste. Eine 2. Spalte hat ein Filter Kriterium. Nun möchte ich mit VBA jeweils den tiefsten Wert in der 1. Spalte finden, wenn ich verschiedene Filter bei der 2. Spalte eingstellt habe. So ähnlich wie: SMALL(IF(B2:B35000=99), C2:C35000, 3) um den 3. kleinsten Wert zu finden, von allen Werten, welche in der Spalte B der Wert 99 haben.
Dies benötige ich um nachher ein Sverweis mit dieser Zahl machen zu können.
Vielen Dank für eure Hilfe
MAM

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: nachgefragt ...

18.02.2016 17:00:05

...

Hallo,
... meinst Du aus einer gefilterten Liste oder meinst nur bezogen auf ein zusätzliche bedingungskriterium.
Im letzteren Fall kann man eine einfache AGGREGAT()-Formel einsetzen. Im ersteren Fall wird es etwas komplexer, aber es bedarf auch keines VBA
Gruß Werner
.. , - ...

AW: nachgefragt ...

18.02.2016 17:07:39

MAM

Hallo Werner
Nein, die Liste hat keinen "Filter". Es sind nur zusätzliche Bedingungskriterien, wobei auch zum teil mehr als 1 Bedingung sein könnte...
Wie würde so eine Aggregat-Formel aussehen?
Vielen Dank
MAM

AW: =AGGREGAT(15;6;C2:C35000/(B2:B35000=99);3) owT

18.02.2016 17:11:29

...

Gruß Werner
.. , - ...

AW: =AGGREGAT(15;6;C2:C35000/(B2:B35000=99);3) owT

18.02.2016 17:48:54

MAM

Hallo Werner
Vielen Dank, aber ich würde es mit VBA benötigen. Gibt es da auch eine einfach Lösung? Vor allem wie sieht es aus mit dem Einbezug von mehreren Bedingungen? Also zB nicht nur 99 sondern zusätzlich noch 77 und 44 in einer Suche.
Danke im Voraus
MAM

AW: gewünschte Formelerweit. ist kein Problem ...

18.02.2016 18:26:10

...

Hallo,
... dann einfach so: =AGGREGAT(15;6;C2:C35000/(B2:B35000={99.77.44});3)
Jedoch aus VBA-Lösungen bin ich außen vor, doch hätte ich zumindest Zweifel, ob es da eine schnellere und einfacher Lösung gibt.
Gruß Werner
.. , - ...

Hat sonst noch jemand eine Idee mit VBA?

19.02.2016 07:30:18

MAM

Hallo zusammen
Ist dies wirklich nicht möglich in VBA? Es gibt diverse andere Formeln wo dies funktioniert z.B: countifS usw.
Hat jemand anders eine Idee in VBA?
PS. Vielen Dank Werner für deine Hilfe
MAM

AW: Hat sonst noch jemand eine Idee mit VBA?

19.02.2016 08:55:46

MAM

Hallo zusammen
Hab noch etwas gesucht und eine Lösungsansatz über ein Array gefunden, welches ich nun für mich angepasst habe. Folgend die Lösung, wenn es einfacher, schneller und besser geht, bin ich natürlich weiterhin froh um jeden Input.
Liebe Grüsse
MAM

Sub Arraybefuellen()
Dim r As Long           'Zeilen#
Dim myArray()
Dim a As Long           'Index Array
Dim b As Long           'Index Array
Dim c As Long           'Index Array
Dim d As Long           'Index Array
Dim LoL As Long         'letzte Zeile
Dim gesuchte_zahl_01 As Variant '1. gesuchte Zahl
Dim gesuchte_zahl_02 As Variant '2. gesuchte Zahl
Dim gesuchte_zahl_03 As Variant '3. gesuchte Zahl
gesuchte_zahl_01 = 23
gesuchte_zahl_02 = 33
gesuchte_zahl_03 = 44
LoL = Cells(Rows.Count, "A").End(xlUp).Row
b = WorksheetFunction.CountIf(Range("A2:A" & LoL), "=" & gesuchte_zahl_01)
c = WorksheetFunction.CountIf(Range("A2:A" & LoL), "=" & gesuchte_zahl_02)
d = WorksheetFunction.CountIf(Range("A2:A" & LoL), "=" & gesuchte_zahl_03)
a = b + c + d
ReDim myArray(1 To a, 1 To 1)
a = 0
For r = 1 To LoL
If Range("A" & r) = gesuchte_zahl_01 Or Range("A" & r) = gesuchte_zahl_02 Or Range("A" & _
r) = gesuchte_zahl_03 Then
a = a + 1
myArray(a, 1) = Range("B" & r)       'Zahl
End If
Next r
Range("F1:F" & UBound(myArray)) = myArray        'Kontroll-Listung
Range("G1") = Application.WorksheetFunction.Small(myArray, 1)
Range("G2") = Application.WorksheetFunction.Small(myArray, 2)
Range("G3") = Application.WorksheetFunction.Small(myArray, 3)
Range("G4") = Application.WorksheetFunction.Small(myArray, 4)
Range("G5") = Application.WorksheetFunction.Small(myArray, 5)
Range("G6") = Application.WorksheetFunction.Small(myArray, 6)
End Sub

AW: Hat sonst noch jemand eine Idee mit VBA?

19.02.2016 11:29:30

Luschi

Hallo MAM,
warum mit Vba-Arrays in Schleifen rumhantieren, wenn es mit 2 definierten Namen und der von Werner geposteten Formellösung viel einfacher geht.
Natürlich benutze ich auch Vba, aber nur, um zu überprüfen, ob es die 2 Defdinierten Namen auch gibt und um sie den Gegebenheiten anzupassen.
Hier mal mein Beispiel: https://www.herber.de/bbs/user/103705.xlsm
Gruß von Luschi
aus klein-Paris

AW: Hat sonst noch jemand eine Idee mit VBA?

19.02.2016 13:35:36

MAM

Hallo Luschi
Vielen Dank für die Beispiel Datei. Habe nicht gewusst, dass dies mit der Formel so in VBA funktioniert (und auch die Aggregate Formel nicht gekannt). Habe es versucht für meine Bedürfnisse anzupassen, kann man in der Aggregate Formel auch andere Suchkriterien eingeben als Zahlen? Irgendwie hat das nicht funktioniert bei mir.
s = "=AGGREGATE(15,6,Tabelle1!$B$2:$B$" & j & "/(Tabelle1!$A$2:$A$" & j & "={"AAV1", "BBX4", "CCZ9"}),lfdPos)"

SyntaxFehler

19.02.2016 14:49:31

Luc:-?

Hi, MAM;
innerhalb von an-/ausführend mit " gekennzeichneten Texten müssen weitere TextKennzeichnungen gedoppelt wdn, also: "={""AAV1"",""BBX4"",""CCZ9""}),lfdPos)"
Gruß, Luc :-?
Besser informiert mit …

Beliebteste Forumthreads (12 Monate)

Forumthread: Xte-kleinste Zahl mit Bedingung

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread