VBA Pivot Table : Output / Concatenate multiple row Data into a String depending on a Value

[ZogXLS]

Hello all,


I am trying to implement the below concept in VBA:

I have a Pivot Table that has the data below ( I am actually viewing the data in tabular form, but this is arbitary)


Lets call this Pivot1_1

Toy	Region	Country
Toy1	Region1	Country1
Toy1	Region1	Country2
Toy1	Region2	Country3
Toy1	Region2	Country4
Toy2	Region1	Country1
Toy2	Region3	Country5
Toy2	Region4	Country6
Toy2	Region4	Country7

<tbody>
</tbody>

Another Way to view this is :


Pivot1_2

Toy	Region	Country
Toy1	Region1	Country1
		Country2
	Region2	Country3
		Country4
Toy2	Region1	Country1
	Region3	Country5
	Region4	Country6
		Country7

<tbody>
</tbody>

The output I am looking to get is the regions and countries per toy to be in one string in one cell as below (the data appear wrapped however imagine that in the table below B2 value is "region1, region2":

Toy1	region1, region2	country1, country2, country3, country4
Toy2	region1, region3, Region4	country1, country5, country6, country7

<tbody>
</tbody>

Now I have found online a Function for concatenation of array items which I use for other purposes

Function MyConCat(myDelimiter As String, Avar) As String
    Dim b As Variant, Dum As String
    If IsMissing(myDelimiter) Then myDelimiter = ""
    For Each b In Avar
        Dum = IIf(Len(b) > 0, Dum & myDelimiter & b, Dum)
    Next
    MyConCat = IIf(Len(myDelimiter) > 0, Mid(Dum, Len(myDelimiter) + 1, Len(Dum)), Dum)
End Function

What I am looking for is the VBA that could do that transition. I can think of several logic language ways to do this but I do not know at all how to implement that in VBA.

Excuse my notation, it is just meant to show the logic not the actual code

E.g. in Pivot1_1

- For loop - through Toy Column row by row
- Count the number of appearances of "Toy_name"
- If count > 1 then create an array to hold the regions and one to hole the countries
- Go line by line checking whether the region is a new one and whether it should be added to the region array or not etc.
- Country_array = MyconCat(" ,", array(coutrycolumn_row1: countrycolumn_row1+counter))

E.g. in Pivot1_1 aslternative logic

- Initiate an empty Region array and a country array
- Go Line by Line
- Add the first row region and country to the relevant arrays
- Go to the next linechecking whether Toyname is the same as the previous line
- If YES then add the cell on the right to the region array and the cell two places to the right to the country array
- If NO copy paste the line to the output table or generally create a new table and just populate the toy name to C1R1, then C2R1 populate myconcat(region array), etc ..
Move one row down etc.

I am sure there are plenty of algorithms to implement this, as stated already, I just do not know good enough VBA to do this :S