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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Rather than a jagged array, would a dictionary of dictionaries work for you?
 

JONABRA

New Member
Joined
Mar 15, 2019
Messages
19
Hi Fluff thank you for the response. I haven't heard of a dictionary of dictionaries, why would it be more helpful in this case? This question is preparation for trying to understand how to approach a wider problem I'm working on, the actual excel document around 60 relevant columns. I actually want to start with name and surname, find the YES's, and return the column heading, so for example it would be :

(Dale Steyn)("apple","pear", "banana""...etc)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
so for example it would be :

(Dale Steyn)("apple","pear", "banana""...etc)
This looks like an array of fruit for each person which is the opposite of your original request for an array of names for each fruit.
fruit(1)("Herschelle Gibbs","Dale Abrahams","Dale Steyn","Sipho Masiko")
fruit(2)("Dale Abrahams","Sipho Masiko")
fruit(3)("Herschelle Gibbs"."Sipho Masiko")

Which is it that you want?

Also, can you indicate what you are going to do with the results?
That might help us understand why you don't want "empty spaces in the array" and/or may trigger thoughts of an alternative way to get to your end result if that was simpler.
 

JONABRA

New Member
Joined
Mar 15, 2019
Messages
19

ADVERTISEMENT

Hi Peter, my apologies for shifting the goalposts. My initial request of an array of Fruit was because I was appending the data of newly added columns into a sheet that was populated by an older piece of code, written by someone else. That older code would take a large spreadsheet full of meta data ("master sheet") and only present relevant data("output sheet") in a more easily readable format for my team to work with:

master sheet:
NAMESURNAMEFARMAPPLEBANANAORANGELEMONSTRAWBERRYGUAVAHead OfficeSupplier code
Sipho MasikoFree state FarmsYESYESYESYESCape Town1234
Dale AbrahamsHappy ValleyYESYES
YESJohannesburg5432

the output of the code: ("output_sheet")
NAMESURNAMEFRUIT
DaleSteynAPPLE/ORANGE/LEMON/GUAVA
Dale AbrahamsAPPLE/BANANA/LEMON

When the organisation added a new "fruit" column to the "master sheet" I would use the code in my first post above in order to find the YES's in the newly added column, get the names and surnames associated with them and add them to an array. Then I would use a for loop to run through the array(without blanks) and match the names and surnames in the array to the names and surnames on the "output sheet". Once a match was found the new fruit name would then be concatenated into the "fruit" column on the "output sheet". Using the example above, if "Guava" was the newly added column on the "master sheet", "Guava" would then be concatenated to the fruits in the fruit column on the "output sheet".

This system worked for a while however there is more and more fruit columns being added constantly and my patch code is becoming messy, therefore I would like to rewrite the old code completely. My initial question approached the problem from a "patch" perspective, however rewriting the code will mean that I need to approach the problem from the opposite angle; getting the names and surnames, and then placing the fruit associated with that name into an array. I'm thinking an array is my solution so the code doesn't need to be constantly switching between the sheets.

I've been struggling the entire weekend trying to think why I would like a jagged array each with different lengths and no blank spaces, it seemed to make sense when looping through the array and concatenating newly added fruit to the existing "output sheet", but I wonder if that approach would be useful when attempting to rewrite the entire code. I think part of the reason I asked the first questions was to simply know if it was possible, in order to stretch my thinking around this subject of arrays and jagged arrays and how to use them to perhaps solve this problem. My apologies for asking two questions in one, as a beginner coder, I think I partly thought it was perhaps a very similar question that only meant using a transpose function or something like that in order to modify it.

At this point the first question helps me achieve temporarily "patching" of the original code for now while the second helps me with visualising a path to completely rewriting the code. I would however like to respect the rules of the forum and if needed I will post the second question in a different post.

Much thanks

(the actual sheet contains thousands of tv shows and movie titles(names and surnames) and subscription packages and regions(about 60 fruit) those video go to)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
That is very 'wordy' & I'm not sure I am right on top of it all, but
  1. Would it be feasible, when the code is run to simply remove everything (or at least the columns Name, Surname and Fruit') from the output sheet and replace it with all the concatenated values from the current master sheet?
  2. If the answer to the above is "No", ..
    • Can you give a brief indication of why that wouldn't suit you?
    • Is there any reason you would especially want to append the new fruit to the existing list rather than simply replacing the whole fruit list for that person with what is in the master sheet?
      (BTW, is is possible that fruits can get removed from a person's row & would that mean it should be removed from the output sheet too?)
 

JONABRA

New Member
Joined
Mar 15, 2019
Messages
19

ADVERTISEMENT

Hi Peter, in an attempt to try and explain as clearly as possible, I think I might have made it more confusing and got too wordy.
1. Would it be feasible, when the code is run to simply remove everything (or at least the columns Name, Surname and Fruit') from the output sheet and replace it with all the concatenated values from the current master sheet?
That is ultimately what I would like to achieve when rewriting the code, and im working to understanding it, im sure you'll come across o a few more of my questions in the forum later on.
If the answer to the above is "No", ..
  • Can you give a brief indication of why that wouldn't suit you?
  • Is there any reason you would especially want to append the new fruit to the existing list rather than simply replacing the whole fruit list for that person with what is in the master sheet?
    (BTW, is is possible that fruits can get removed from a person's row & would that mean it should be removed from the output sheet too?)
That would suit me, and yes "fruits" do get removed from time to time. The old code will pick this up as every time I run it, it generates a new sheet based on whatever is on the "master sheet" at the time. I think i just have to go off and figure out how to approach that.

Maybe for now, to simplify this thread, let me stick to my original question. Is it possible to create a jagged array of varying lengths? And then have a loop, loop through each of the arrays of the array, according to the length of each?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
That is ultimately what I would like to achieve
You could try something like this. Test with a copy of the workbook.
I have assumed the 3 headings at least are on the Output sheet and that anything below that can be removed.
From what I can see there is no need for any jagged arrays as the results sheet is a consistent 3 columns for every person.

VBA Code:
Sub Fruit_List()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, uba2 As Long
  
  a = Sheets("Master").UsedRange.Value
  uba2 = UBound(a, 2)
  ReDim b(1 To UBound(a) - 1, 1 To 3)
  For i = 2 To UBound(a)
    b(i - 1, 1) = a(i, 1)
    b(i - 1, 2) = a(i, 2)
    For j = 3 To uba2
      If UCase(a(i, j)) = "YES" Then b(i - 1, 3) = b(i - 1, 3) & "/" & a(1, j)
    Next j
  Next i
  With Sheets("Output")
    .UsedRange.Offset(1).EntireRow.Delete
    With .Range("A2:C2").Resize(UBound(b))
      .Value = b
      .Columns(3).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
    End With
  End With
End Sub
 

JONABRA

New Member
Joined
Mar 15, 2019
Messages
19
Hi Peter, I really like clever bit of code! I was analysing it last night to see how I might make it work for my particular sheet, and I believe its setting my thinking on the correct path. I realized that for now, for my patch code, I can't overwrite data, as "name and surname" has to be matched with "name and surname" on the "output sheet" because there are other columns of data that go along with it. So I'll have use your method and roll it together with an index and match or find type solution.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
... index and match or find type solution.
Since you are working in vba, I would use a native vba Range.Find method rather than a worksheet Index/Match approach.
Post back with details if you need further help but glad you seem to have a path forward for the moment. (y)
 

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