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.
 

JONABRA

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

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

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
Top