Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)
Alternative Codes using [ ] shorthand
Using LOOKUP to get the last value
For the particular formula that I am trying to get to and understand, ( one of this sort of form =LOOKUP(2;1/(C:C<>"");C:C) ) ,I need to look a bit further into the behaviour of LOOKUP, in particular the second argument.
A few observations:
_observation
VLOOKUP seems to assume that the list/ range of numbers is in ascending order
For example, this sort of thing,
1
3
4
2
| Row\Col |
E |
|---|
| 37 |
1 |
| 38 |
3 |
| 39 |
4 |
| 40 |
2 |
| 41 |
|
| 42 |
|
, with the LOOKUP formula of these sort of things
=LOOKUP(5;E37:E40)
=LOOKUP(5;{1;3;4;2})
, will return 2. In fact it always seems to return the last value. This initially points us into perhaps a direction of thinking to just look for any number likely to be bigger than any in the list of numbers (in the second argument range)
_observation
Extend the range a bit , to example include some empty cells,
=LOOKUP(5;E37:E43)
, and once again the answer is 2.
This is very interesting and helpful. It seems not to error when not getting numbers, (as I was thinking that the second argument would be always expecting numbers). The empty cells simply seem to be ignored
But at this point some alarm bells are ringing in my head. The final solution forms I am envisaging, are likely to involve array type things, so I want to continue as I have done so far, to consider the array alternative, and I am getting worried now – because…
depending on your viewpoint, an empty cell can be regarded and one if at least 3 things I know about
___ Empty
___ 0
___ a zero length string, often represented by ""
Well, I can’t be too sure about how to distinguish all 3 in a formula, but I can do a partial job by considering these two array formula versions
=LOOKUP(5;{3;4;2;0;0;0}) ' returns 0
=LOOKUP(5;{3;4;2;"";"";""}) ' returns 2
The difference in those two results is enough for me to ponder about for a while. But I don’t need tom ponder too long.
It makes sense where we get 0, since 0 is a number, and in line with what we have already said
_____ VLOOKUP takes the very last number in a list if it never finds either the number it is looking for , or if it never finds a larger number at any other position in a list, (in this latter case it would take the number before that bigger number)
For the purposes of the array type formula, "" , can be regarded, (with some caution perhaps), of behaving like our spreadsheet empty cell. We will come back to this last point, but let’s give a pictorial summary so far of where we are
| Row\Col |
E |
F |
|---|
| 37 |
1 |
=LOOKUP(5,E37:E40) |
| 38 |
3 |
=LOOKUP(5,{1;3;4;2}) |
| 39 |
4 |
=LOOKUP(5,E37:E43) |
| 40 |
2 |
=LOOKUP(5,{3;4;2;0;0;0}) |
| 41 |
|
=LOOKUP(5,{3;4;2;"";"";""}) |
| 42 |
|
|
| 43 |
|
|
| Row\Col |
E |
F |
|---|
| 37 |
1 |
2 |
| 38 |
3 |
2 |
| 39 |
4 |
2 |
| 40 |
2 |
0 |
| 41 |
|
2 |
| 42 |
|
|
| 43 |
|
|
In Excel functions and formulas generally, the difference between using a number and a text can be significant. The fact that the array version both accepts ( does not error with ) the "" , and gives us perhaps a useful answer , the last number, could be useful….
Also it may be worth checking what happens if the text is not zero length.
Here we go, lets try filling the empty cells with some text:
| Row\Col |
E |
F |
|---|
| 37 |
1 |
=LOOKUP(5,E37:E41) |
| 38 |
3 |
=LOOKUP(5,{1;3;4;2}) |
| 39 |
4 |
=LOOKUP(5,E37:E43) |
| 40 |
2 |
=LOOKUP(5,{1;3;4;2;0;0;0}) |
| 41 |
a |
=LOOKUP(5,{1;3;4;2;"a";"b";"c"}) |
| 42 |
b |
|
| 43 |
c |
|
| Row\Col |
E |
F |
|---|
| 37 |
1 |
2 |
| 38 |
3 |
2 |
| 39 |
4 |
2 |
| 40 |
2 |
0 |
| 41 |
a |
2 |
| 42 |
b |
|
| 43 |
c |
|
Hmm…. So it seems to treat an actual text just like the zero length text
In the next post we will develop/ explain a solution
Array List Sort of Referrences
Referrences in suppost of this post:
http://www.excelfox.com/forum/showth...=9985#post9985
and solution to this post
http://www.excelforum.com/excel-prog...ml#post4507157
' http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
' https://usefulgyaan.wordpress.com/20...1/#comment-587
' https://usefulgyaan.wordpress.com/20...1/#comment-515
Code:
' https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/comment-page-1/#comment-587
Sub M_snbSortof() ' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
Dim rngVoll As Range: Set rngVoll = Tabelle3.Range("A1:E10")
Dim snAll() As Variant, Sported() As Variant
Let snAll() = rngVoll.Value
Dim j As Long, jj As Long
With CreateObject("System.Collections.Arraylist")
For j = 1 To UBound(snAll(), 1)
.Add snAll(j, 3)
Next
.Sort
Let Sported() = .ToArray
.Clear
For j = 0 To UBound(Sported())
For jj = 1 To UBound(snAll(), 1)
If snAll(jj, 3) = Sported(j) Then
' Use Range to overcome Array size Limits of Worksheets Functions
'Dim Clm As Range: Set Clm = Application.Index(rngVoll, jj, 0)
' .Add Clm.Value
' .Add (Application.Index(rngVoll, jj, 0).Value)
' Use Cells to overcome Array size Limits of Worksheets Functions
Dim LB As Long, UB As Long '…User Given start and Stop Column as a Number
Let LB = LBound(snAll(), 2): Let UB = UBound(snAll(), 2)
Dim strLtrLB As String, strLtrUB As String '…Column Letter corresponding to Column Number
'There are many ways to get a Column Letter from a Column Number – excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html
Let strLtrLB = Split(Cells(1, LB).Address, "$")(1) 'An Address Method
Let strLtrUB = Replace (Replace(Cells(1, UB).Address, "1", ""), "$", "") 'A Replace Method
'Obtain Column Indicies using Spreadsheet Function Column via VBA Evaluate Method
Dim clms() As Variant
Let clms() = Evaluate("column(" & strLtrLB & ":" & strLtrUB & ")") 'Returns 1 D “pseudo” Horizontal Array of sequential numbers from column number of LB to UB
'Or
clms() = Evaluate("column(" & Split(Cells(1, LB).Address, "$")(1) & ":" & Replace (Replace(Cells(1, UB).Address, "1", ""), "$", "") & ")")
.Add (Application.Index(Tabelle3.Cells, jj, clms()))
'Let snAll(jj, 3) = ""
Exit For
End If
Next jj
Next j
For j = 0 To .Count - 1
Tabelle3.Cells(j + 1 + 10, 1).Resize(, UBound(snAll, 2)) = .Item(j)
Next j
End With
End Sub
'
Sub M_snb()
Dim sn, sp, j As Long, jj As Long
sn = Tabelle3.Range("A1:E10")
With CreateObject("System.Collections.Arraylist")
For j = 1 To UBound(sn)
.Add sn(j, 3)
Next
.Sort
sp = .ToArray
.Clear
For j = 0 To UBound(sp)
For jj = 1 To UBound(sn)
If sn(jj, 3) = sp(j) Then
.Add Application.Index(sn, jj)
sn(jj, 3) = ""
Exit For
End If
Next
Next
For j = 0 To .Count - 1
Tabelle3.Cells((j + 1) + 10, 1).Resize(, UBound(sn, 2)) = .Item(j)
Next
End With
End Sub
'Rem Ref
' http://www.excelforum.com/excel-programming-vba-macros/1139207-how-to-move-a-userform-and-module-from-one-book-to-another-2.html
' http://www.excelforum.com/excel-programming-vba-macros/1138300-vba-userform-value-check-if-user-form-buttons-checked-not-working-check-button-on-open.html
' http://www.excelforum.com/excel-programming-vba-macros/1139742-workbooks_open-crashes-when-file-opened-with-code-manually-open-ok-userform-issue.html
' http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9985#post9985
' http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
' http://www.excelforum.com/showthread.php?t=1154829&page=4#post4502593
' http://www.excelforum.com/excel-programming-vba-macros/1160648-how-to-create-a-pop-up-notification-for-two-different-conditions-at-the-same-time.html#post4507157
' http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9985#post9985
http://www.excelforum.com/showthread...=4#post4502593
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hWn2pGBeSS
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg. 9hJRnEjxQrd9hJoCjomNI2
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg. 9hJOZEEZa6p9hJqLC7El-w
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg. 9hIlxxGY7t49hJsB2PWxC4
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg. 9hIKlNPeqDn9hJskm92np6
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg. 9hI2IGUdmTW9hJuyaQawqx
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg. 9hI1CQJMLLo9hJwTB9Jlob
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eekDyfS0CD
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eevG7txd2c
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg. 9dPo-OdLmZ09dc21kigjmr
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg. 9cXui6zzkz09cZttH_-2Gf
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
