Mailmerge data layout - vertical or horizontal

[JugglerJAF]

(Apologies of this needs to be in a Word forum, but it is 50% Excel related!)

I have some data that I use to create a mailmerge, and in its raw form, the data looks like this:

<tbody>
[TD="class: xl66"]hardware[/TD]
[TD="class: xl66"]support[/TD]
[TD="class: xl66"]price[/TD]

[TD="class: xl66"]Alpha[/TD]
[TD="class: xl66"]a1[/TD]
[TD="class: xl65"]£11[/TD]

[TD="class: xl66"]Alpha[/TD]
[TD="class: xl66"]a2[/TD]
[TD="class: xl65"]£12[/TD]

[TD="class: xl66"]Bravo[/TD]
[TD="class: xl66"]b1[/TD]
[TD="class: xl65"]£22[/TD]

[TD="class: xl66"]Bravo[/TD]
[TD="class: xl66"]b2[/TD]
[TD="class: xl65"]£24[/TD]

[TD="class: xl66"]Bravo[/TD]
[TD="class: xl66"]b3[/TD]
[TD="class: xl65"]£26[/TD]

[TD="class: xl66"]Charlie[/TD]
[TD="class: xl66"]c1[/TD]
[TD="class: xl65"]£33[/TD]

</tbody>

From this, I need a mailmerge to produce 3 letters (one each addressed to Alpha, Bravo, and Charlie) and each letter needs to show the support description and price for all available products, so the final output would be:
1 letter to Alpha, listing two levels of support (a1 and a2), and their associated prices (£11 and £12)
1 letter to Bravo, listing three levels of support (b1, b2, and b3), and their associated prices (£12, £22, and £24)
1 letter to Charlie, listing one level of support (c1), and its associated price (£33)

The current process for doing this requires the layout of the data file to be changed from the tabular "vertical" format above (6 rows, 3 columns) to a "horizontal" format (3 rows, six columns) as shown below.

<tbody>
[TD="class: xl66"]hardware[/TD]
[TD="class: xl66"]support_1[/TD]
[TD="class: xl67"]price_1[/TD]
[TD="class: xl66, width: 69"]support_2[/TD]
[TD="class: xl67, width: 52"]price_2[/TD]
[TD="class: xl66, width: 69"]support_3[/TD]
[TD="class: xl67, width: 52"]price_3[/TD]

[TD="class: xl66"]Alpha[/TD]
[TD="class: xl66"]a1[/TD]
[TD="class: xl67"]£11[/TD]
[TD="class: xl66"]a2[/TD]
[TD="class: xl67"]£12[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]Bravo[/TD]
[TD="class: xl66"]b1[/TD]
[TD="class: xl67"]£22[/TD]
[TD="class: xl66"]b2[/TD]
[TD="class: xl67"]£24[/TD]
[TD="class: xl66"]b3[/TD]
[TD="class: xl67"]£26[/TD]

[TD="class: xl66"]Charlie[/TD]
[TD="class: xl66"]c1[/TD]
[TD="class: xl67"]£33[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]

</tbody>

Is there any way of doing the mailmerge with the data in the original layout so that I don't have to amend the layout every time?

 

[Fluff]

How about using this to change the format

Sub JugglerJAF()
   Dim Cl As Range
   
   With CreateObject("Scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.count).End(xlUp))
         .Item(Cl.Value) = .Item(Cl.Value) & Cl.Offset(, 1).Value & "|" & Cl.Offset(, 2).Value & "|"
      Next Cl
      Range("F2").Resize(.count, 2).Value = Application.Transpose(Array(.Keys, .Items))
      Range("G2:G100000").TextToColumns Range("G2"), xlDelimited, , , False, False, False, False, True, "|"
   End With
End Sub

 

[JugglerJAF]

Thanks for the suggestion. I do already have a macro that converts the data, but it's not as elegant as your solution.

I'll have a look and see if I can use this shorter approach in the future.

 

[Fluff]

You're welcome & thanks for the feedback