Slicing A 2d Array

  • I have an array I loaded from a file


    EG:

    "I","Doors","Y",1,2,3

    "I","cows","M",11,21,31

    "I","Horses","Y",111,112,113

    "E","Swift","Y",14,24,34

    "E","John","Y",18,28,38


    the above is contained in an array called lnarr


    the code:

    Code
    1. Dim sv As Variant
    2. Dim tfn() As Variant
    3. Dim t1()
    4. j1=-1
    5. For i = 0 To UBound(lnarr) - 1
    6.   sv = Split(lnarr(i), ",", , 1): dt = sv(0)
    7.   sw = awf.Index(sv, 0, Array(2, 3, 4))  'awf=application.worksheetfunction
    8.   If dt = "I" Then j1 = j1 + 1: ReDim Preserve t1(j1): t1(j1) = sw
    9. Next i


    The above code does exactly what i want it to.

    it retrieve only columns 2,3,4


    eg:


    "Doors","Y",1

    "cows","M",11

    "Horses","Y",111


    However,

    when i load the array without splitting it by col,

    eg:

    Code
    1. For i = 0 To UBound(lnarr) - 1
    2.   sv = Split(lnarr(i), ",", , 1): dt = sv(0)
    3.   If dt = "I" Then ac(1) = ac(1) + 1: ReDim Preserve t1(ac(1)): t1(ac(1)) = sv
    4. Next i


    The code above produces

    an array with the following


    t1(0)(0)= "I"

    t1(0)(1)= "Doors"

    t1(0)(2)= "Y"

    t1(0)(3)= 1

    t1(0)(4)= 2

    t1(0)(5)= 3


    t1(1)(0)= "I"

    t1(1)(1)= "cows"

    t1(1)(2)= "M"

    t1(1)(3)= 11

    t1(1)(4)= 21

    t1(1)(5)= 31


    t1(2)(0)= "I"

    t1(2)(1)= "Horses"

    t1(2)(2)= "Y"

    t1(2)(3)= 111

    t1(2)(4)= 112

    t1(2)(5)= 113


    when i try to extract columns 2,3,4 from this array,

    it only retrieves the first row (t1(0) (2,3,4))


    Code
    1. t2= application.worksheetfunction.index(t1,0,Array(2,3,4))
    2. 'tried this to
    3. t2= application.worksheetfunction.index(t1,0,application.worksheetfunction.Transpose(Array(2,3,4)))


    it seems i can extract cols 2,3,4 when i loop through each row

    i thought application.worksheetfunction.index(t1,0,Array(2,3,4))

    should retrieve cols 2,3,4 in all rows


    What am i missing?

  • Not entirely sure what your aim is, but you can retrieve certain columns from an array like

    Code
    1. Dim a As Variant, b As Variant
    2. a = Range("A1:Z20").Value
    3. b = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(2, 3, 4))
  • Fluff13,


    i have attached a workbook and a test file.

    put file in same location as workbook.

    open work book view logdata macro - put breakpoint where indicated

    run macro -hold mouse over each array var for contents


    do the same with logdata1 macro


    the difference is the way it extracts the columns

    logdata macro loops and extracts row by row cols 2,3,4 and puts into t1()


    logdata1 macro loops and put entire contents row by row into into t1()

    then tries to extract cols 2,3,4 from the array t1 using


    awf.index(t1,0,Array(2,3,4))

    Files

    • slice2d.xlsm

      (22.7 kB, downloaded 136 times, last: )
    • logs.txt

      (109 Byte, downloaded 125 times, last: )
  • I'm still none the wiser, about what you are trying to do, but as far as I know you cannot use Index on a jagged array the way you are trying to do.

  • @ rabsofty

    Hi

    I am not quite sure what you are missing, because I am not quite sure what your thinkings are behind what you were trying to do. You may be getting slightly mixed up with slicing an array (https://usefulgyaan.wordpress.…t-loop-application-index/ ) and doing the the “Index(array(), Rws(), Clms()) ” type of formula mentioned in Fluff’s post # 2 , Slicing A 2d Array

    ( Having said that, there is no reason why you should not use combinations of those 2 techniques… )

    I thought initially I could actually do what you wanted, but then on closer inspection of what you actually want , I found I could do it, but probably not quite as simply neatly, or dynamically as you would like.._


    _... Like Fluff I got a bit lost in your long macros in the uploaded file. You seem to be doing everything in an untypical long way around. Or maybe it is just a way that I am unfamiliar with. But It is interesting following through your uploaded files, since you have 'commented it very well, but it takes a while. ( a small typo: your macros look for a logs.dta file, - it should be logs.txt )


    But anyway, if I have understood correctly, it all boils down to

    _a) you have a “jagged” array, that is to say an array containing arrays as elements – an array of arrays.

    and

    _b) you want to use that often seen “Index(array(), Rws(), Clms()) ” type of formula, ( or a combination of that and the slicing idea) , to have the same “jagged” array as output but with “column” elements reduced in the arrays that are the elements of the main “outer” array

    ( It threw me off for a while you asking for getting specific columns, as did also throw me off your Thread title containing 2d array, since , initially anyway, we are looking at 1 Dimensional arrays…. Or as it finally comes out, maybe we aint…


    Let me try to explain a bit more

    _a) you end up at a point in your macro Sub logdata1() , with an array of arrays, t1

    That is to say, a main array t1, whose elements are themselves, arrays. Let me call those the “Inner” arrays. ( I think what you end up with is sometimes called, as suggested, a jagged array. I suppose in your case we might call it an “unjagged” jagged array, since you have the same number of elements in each of the “inner” arrays . )

    Showing it pseudo pictorially, simplified, you end up with something like this

    a b c d e __ f g h i j __ k l m n o

    In that simplified example, you have a main 1 D array with three “inner” 1 D arrays in it. Each “inner” array has 5 elements in it. ( I guess we could those 5 elements 5 “columns”, but that is a debatable point in a 1D array )

    _b) What you want, ( which took me a while to realise, ) is to have a simple code line, without the need to do any looping to get an output, also a “unjagged” jagged array, like, pseudo

    b c d __ g h i __ l m n

    Loosely speaking, to offend and upset the academics, we can say you have picked out columns 2 , 3 and 4

    You wanted to do that like in this code line of yours

    tfn = awf.Index(t1, 0, Array(2, 3, 4))

    As you found, it did not work, and I have not been able to figure out yet how to do it exactly in the way you tried with a simple “Index(array(), Rws(), Clms()) ” type code line..


    But

    It seems as if Excel holds a jagged array like yours somehow as if it was actually a 2D array like, pseudo pictorially

    a b c d e

    f g h I j

    k l m n o

    At any rate, Index seems to handle it as if it were that 2D array

    So you can get the answer you wanted like this

    tfn = Array(awf.Index(t1, 1, Array(2, 3, 4)), awf.Index(t1, 2, Array(2, 3, 4)), awf.Index(t1, 3, Array(2, 3, 4)))

    It gets your answer without looping, but the problem is that it is not dynamic, and I can’t see yet an easy way to make it dynamic


    _._______________

    Out of passing interest, I was surprised to find that Excel was apparently holding such a jagged array as if it was 1 2D array. . I did not expect that. I expect other people wouldn’t?

    Out of interest I would welcome any comments on that from people familiar with these Index way of doing things to return parts of an array. Maybe someone else noticed it.


    BTW , I can think of one quite useful application of it. …. The following…

    There is a fairly common way of importing a text file into an array and/ or into a spreadsheet: .. it is like this…A single complete text string of the text file is first put into a simple string variable. Then

    _(i) That string is split by the line separator to get a 1 D array of all the lines ( “rows” )

    _(ii)a That array is looped through , and at each loop we split again each element by the value separator to get a 1D array of the “columns”, and then further

    _(ii)b at each loop we loop again along the elements ( “columns” ) – At each of these “inner” loops we fill either a spreadsheet or an array with an element

    What we can do is do away with the inner loop _(ii)b and instead at _(ii)a just fill a 1 D array with the 1 D array of the “columns”

    Then finally, to get our array / range we apply a code line of pseudo like

    Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})

    ( Of course you can pick out whatever set of rows or columns that you want instead )

    I expect that might be a bit more efficient, but I am not sure.


    In the uploaded file, I have put two example text import macros which work on your logs.txt file saved at the same place as the file. One does the more typical way, the other demonstrates the way I tried to explain…

    Neither macro is particularly efficient, - I am mainly trying to demo what I was on about


    Alan

    Files

    • slice2d.xlsm

      (54.09 kB, downloaded 141 times, last: )
  • then tries to extract cols 2,3,4 from the array t1 using


    awf.index(t1,0,Array(2,3,4))

    You can't use 0 for the row argument, you need an array of row numbers - for example:


    Code
    1. tfn = awf.Index(t1, Evaluate("ROW(A1:A" & UBound(t1) + 1 & ")"), Array(2, 3, 4))

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • You can't .........

    Sn-ap :)

    Hi Rony. You possibly missed the point , like I did on first read.

    The OP actually has in t1 a 1 D array. In that array he has 5 elements. Those elements are themselves 1 D arrays with 6 elements in them. ( Maybe you might call that an “Unjagged” jagged array?,I am not too sure what the definition is of a jagged array)

    What the OP wanted to do is, rather than looping, to apply a Index type thing to that t1 1 D array of 1 D arrays, so as return him a similar 1 D array of 1 D arrays but with just 3 of the previous 6 elements in the “inner” arrays.

    I was surprised to find that you can actually do that, all be it in a limited way. .._

    _... The thing what surprised me is that it appears that Index treats that t1 1D array of 1 D arrays as if it was a 2 D array. So that means you can in fact apply that code line like you and Fluff suggested to get what he wanted. Its all in my post

    Alan