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

JONABRA

New Member
Joined
Mar 15, 2019
Messages
19
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.

ABCDE
1namesurnameapple fruit(1)banana fruit(2)pear fruit(3)
2HerschelleGibbsYESYES
3DaleAbrahamsYESYES
4DaleSteynYES
5SiphoMasikoYESYESYES

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?

VBA Code:
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.
 
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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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. :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top