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.

 

[Fluff]

Rather than a jagged array, would a dictionary of dictionaries work for you?

 

[JONABRA]

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]

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]

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:

NAME	SURNAME	FARM	APPLE	BANANA	ORANGE	LEMON	STRAWBERRY	GUAVA	Head Office	Supplier code
Sipho	Masiko	Free state Farms	YES		YES	YES		YES	Cape Town	1234
Dale	Abrahams	Happy Valley	YES	YES		YES			Johannesburg	5432

the output of the code: ("output_sheet")

NAME	SURNAME	FRUIT
Dale	Steyn	APPLE/ORANGE/LEMON/GUAVA
Dale	Abrahams	APPLE/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]

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]

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]

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.

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]

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]

... 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.