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

[TABLE="width: 216"]
<tbody>[TR]
[TD]Toy[/TD]
[TD]Region[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]Toy1[/TD]
[TD]Region1[/TD]
[TD]Country1[/TD]
[/TR]
[TR]
[TD]Toy1[/TD]
[TD]Region1[/TD]
[TD]Country2[/TD]
[/TR]
[TR]
[TD]Toy1[/TD]
[TD]Region2[/TD]
[TD]Country3[/TD]
[/TR]
[TR]
[TD]Toy1[/TD]
[TD]Region2[/TD]
[TD]Country4[/TD]
[/TR]
[TR]
[TD]Toy2[/TD]
[TD]Region1[/TD]
[TD]Country1[/TD]
[/TR]
[TR]
[TD]Toy2[/TD]
[TD]Region3[/TD]
[TD]Country5[/TD]
[/TR]
[TR]
[TD]Toy2[/TD]
[TD]Region4[/TD]
[TD]Country6[/TD]
[/TR]
[TR]
[TD]Toy2[/TD]
[TD]Region4[/TD]
[TD]Country7[/TD]
[/TR]
</tbody>[/TABLE]


Another Way to view this is :


Pivot1_2

[TABLE="width: 192"]
<tbody>[TR]
[TD]Toy[/TD]
[TD]Region[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toy1[/TD]
[TD]Region1[/TD]
[TD]Country1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Country2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Region2[/TD]
[TD]Country3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Country4[/TD]
[/TR]
[TR]
[TD]Toy2[/TD]
[TD]Region1[/TD]
[TD]Country1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Region3[/TD]
[TD]Country5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Region4[/TD]
[TD]Country6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Country7[/TD]
[/TR]
</tbody>[/TABLE]


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":


[TABLE="width: 216"]
<tbody>[TR]
[TD]Toy1[/TD]
[TD]region1, region2[/TD]
[TD]country1, country2, country3, country4[/TD]
[/TR]
[TR]
[TD]Toy2[/TD]
[TD]region1, region3, Region4[/TD]
[TD]country1, country5, country6, country7[/TD]
[/TR]
</tbody>[/TABLE]



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