Array of array/jagged array: how to place columns of various lengths into jagged array

[JONABRA]

Hi all. I have a spreadsheet with x number of columns, I'm looking for the string "YES" in each row of a specified column and once found I place the name linked to the fruit into a jagged array. For example I have a name, surname and fruit columns. The reason I would like a jagged array is that I would like to use less code, like maybe 1 or 2 nested for loops to populate the array. Also I don't want to have any empty spaces in the array, which I believe jagged arrays overcome.

	A	B	C	D	E
1	name	surname	apple fruit(1)	banana fruit(2)	pear fruit(3)
2	Herschelle	Gibbs	YES		YES
3	Dale	Abrahams	YES	YES
4	Dale	Steyn	YES
5	Sipho	Masiko	YES	YES	YES

here is an example of what I would like in the jagged arrays of varying lengths, fruit is the array name:

fruit(1)("Herschelle Gibbs","Dale Abrahams","Dale Steyn","Sipho Masiko")
fruit(2)("Dale Abrahams","Sipho Masiko")
fruit(3)("Herschelle Gibbs"."Sipho Masiko")

I know the following bit of code will add the linked name and surname of only one fruit columns into an array but how do I achieve this over multiple columns, with varying lengths, using a few for loops perhaps?

Dim name, surname, foundyes(), namesurname() as variant

'I got lrow using the usual method of rows.count etc
'i use countyes to dimension the length of the array
countyes = Application.WorksheetFunction.countIF(sheet1.Range("c1:c" & lrow), "YES")

'there are two columns in foundyes one for the surname and one for the name, namesurname is to combine them together
ReDim foundyes(1 To 2, 1 To countyes)
ReDim namesurname(1 To countyes)

counter =1
For i = 2 To lrow

    If Range("c" &  i) = "YES" Then
        foundyes(1, counter) = Range("a" & i).Value
        foundyes(2, counter) = Range("b" & i).Value

        name = foundyes(1, counter)
        surname = foundyes(2, counter)
        namesurname(counter) = name & " " & surname
        counter = counter + 1
    End If

Next i

Much thanks in advance, and I hope this explanation makes sense.

 

[JONABRA]

I would use a native vba Range.Find method rather than a worksheet Index/Match approach.

I tried googling this, how does one determine, what is a worksheet approach and what is a native one? Does the prefix "worksheetfunction" signify this? And should I go this route because its quicker perhaps?

also I've been trying to understand this line in your code could you explain :

.Columns(3).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))

Thanks Peter, will definitely, post back, and I appreciate all the kind help so far, it makes learning code less daunting and mysterious and simply more fun.

 

[Peter_SSs]

I tried googling this, how does one determine, what is a worksheet approach and what is a native one? Does the prefix "worksheetfunction" signify this? And should I go this route because its quicker perhaps?

Yes & No.
Yes - Worksheetfunction does indicate that you are using a worksheet function, not a vba function.
No - Generally vba functions will be quicker in vba than worksheet functions will be in vba.

also I've been trying to understand this line in your code could you explain :

.Columns(3).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))

For Dale Abrahams in post 5 my code would initially create the string "/APPLE/BANANA/LEMON" and place that in column C (3) of the output sheet. To make it look like what you want we need to remove the leading "/".
I assume that you are familiar with Text to Columns. So we do a Text to Columns on that data using the Fixed Width option. The trick here is understanding the array of arrays at the end. Each of those inner arrays contain information about how wide each column is and what format to apply to the resulting column. The fact that there are 2 inner arrays means that we are splitting the data (notionally) into 2 columns.

Reading those 2 inner arrays from right to left
Array(1,1)
The red 1 indicates that the second column starts immediately after position 1 in the string. That is, after that leading "/", at position 2. The fact that there are no further inner arrays to the right of this means that the width of text goes to the end of whatever text there is.
The blue 1 indicates that the format for that column is "General"
So this column gets us our final required string "APPLE/BANANA/LEMON"

Array(0,9)
The red 0 indicates that the first column starts after position 0 in the string. That is, it starts at position 1
The blue 9 indicates "do not import this column".
Given that the second array started at position 2, it must be that this first column is only one character wide.
So that first column (single character) is simply discarded & in fact you end up with just one column, the second of the 'notional' two.

Hope that made sense.

 

[JONABRA]

HI Peter, I had to go over it a few times before I understood it, your explanation was thorough though!
I need to do some googling to find the list of parameters eg: 1 =general, 9 =do not import,... etc.

Thanks again for your help, more useful ideas and code to add to my understanding

 

[Peter_SSs]

I need to do some googling to find the list of parameters eg: 1 =general, 9 =do not import,... etc.

Have a look towards the bottom of this page

If the source data has fixed-width columns, the first element of each two-element array specifies the starting character position in the column (as an integer; 0 (zero) is the first character). The second element of the two-element array specifies the parse option for the column as a number from 1 through 9, as listed above.

 

[JONABRA]

Brilliant, thanks Peter! took slow me a while to realise that even though there are 10 "constants" in that list, number 9 on that list is only relevant if Taiwanese language support is installed. Which then makes number 10 on that list number 9, I'm presuming. Might be useful if anyone else comes across this thread.

 

[Peter_SSs]

Might be useful if anyone else comes across this thread.