and ... present you GETIF()
GetIf() will now work with arrays and in some point with inequalities
syntax: GetIf (Range1;Criteria;[Range2];[ArraySize])
in sort: finds n (n<=arraysize) ranges2() so that ranges1()=criteria
It does what you can do with V/Hlookup and Match and some more functions, but simpler. Advanced users with array experience have probably solved such issues and dont need GetIf().
Users unfamiliar with Arrays will find it very useful.
It will work in simple or Array form. See notes and examples for usage in a typical Excel database, in conjunction with the Offset()
Code:
' EDIT_3 (Jun 2012): Few MAJOR changes.
' Added "Optional ArraySize As Long = 1"
' now: a = range to look into for match (could be range/ranges/array/name)
' b = lime to look for OR lime with comparison elements (ex: >=2)
' c Optional: If c exists then GetIf will return items from c (range/array...) where b matches a
' if c is missing then GetIf returns the indexes of matches found
' ArraySize Optional: Default = 1 : return 1 result
' special = -1 : find quantity of matches through CountIF and return so many results
' normal 1 to...: find as many as exist in an array as big as Asked!
' When covering an array of N elements with GetIf it is especially useful to send an
' Arraysize of N since it will return 0 elemements in empty slots while (-1) or a
' smaller number will return #N/A in empty slots.
' GetIf will always return a Vertical Array of elements according to ArraSize {getif(N,1)}. Can be used normaly
' for one result (default) without Array experience. Use Traspose() of Excel to convert to Horizontal
' ex: transpose( GetIf(N,1) ) = GetIf(1,N)
'
' TIPS: Use with excel formulas: Offset(),Index(),Smaller(),Larger(),Rows(1:N),Columns(1:N) for unlimited usage.
' See examples
'
' Original (Apr 2008)
' Syntax exactly like SUMIF, that is "GetIf (Range1;Criteria;Range2)"
' and finds range2(i) so that range1(i)=criteria
' No controls/checks, no speed or other improvements made by function
'
' Goylandris Apostolos
'
Public Function GetIf(a, b As String, Optional c, Optional ArraySize As Long = 1)
Dim Counter As Long, Counter2 As Long, Counter3 As Long, dum1, EvalFL As Boolean, matchFL As Boolean
Dim Counters() As Long, Holder()
' Fix ArraySize
If ArraySize = 0 Then ArraySize = Evaluate(Application.WorksheetFunction.CountIf(a, B))
If ArraySize < 1 Then ArraySize = 1 ' Restore to 1 even if evaluate finds 0 to avoid errors
' properly allocate arrays for VERTICAL results. Use TRANSPOSE() in excel fo HORIZONTAL
ReDim Counters(1 To ArraySize, 1 To 1), Holder(1 To ArraySize, 1 To 1)
' Enable trapping for Greater than or Smaller than functions
If InStr(b, ">") > 0 Then EvalFL = True
If InStr(b, "<") > 0 Then EvalFL = True
Counter = 0: Counter2 = 0: Counter3 = 0
For Each dum1 In a ' Search for index
Counter = Counter + 1: matchFL = False
If EvalFL Then
If Evaluate(Format(dum1, "#0") + B) Then matchFL = True
Else
If b = Trim(dum1) Then matchFL = True
End If
If matchFL Then
Counter3 = Counter3 + 1
Counters(Counter3, 1) = Counter
If Counter3 = ArraySize Then Exit For 'Search only as many as asked for
End If
Next
If Counter3 > 0 Then ' if something was found
If IsMissing(c) Then ' Edit:Jun2012 Ommitting range2 (c) returns Position within range
GetIf = Counters
Else
Counter = 1
For Each dum1 In c ' Use index to find equivalent
Counter2 = Counter2 + 1
If Counter2 = Counters(Counter, 1) Then
Holder(Counter, 1) = dum1
Counter = Counter + 1
If Counter > Counter3 Then Exit For 'Search only as many as asked for
End If
Next
GetIf = Holder
End If
Else
GetIf = "-":
End If
End Function
look for "getif advanced" for download with some examples in english but regular maintainance and fixes would at site of Michanikos.gr (look for getif v3.00)
Bookmarks