zico2000,
Here is another macro for you to consider, that is based on your flat text displays, that uses two arrays in memory, and, should be fast.
Sample raw data in the active worksheet:
Excel 2007
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Type[/TD]
[TD="bgcolor: #FFFFFF"]Store[/TD]
[TD="bgcolor: #FFFFFF"]Value[/TD]
[TD="bgcolor: #FFFFFF"]France[/TD]
[TD="bgcolor: #FFFFFF"]Germany[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Colour[/TD]
[TD="bgcolor: #FFFFFF"]UK[/TD]
[TD="bgcolor: #FFFFFF"]Red[/TD]
[TD="bgcolor: #FFFFFF"]Rouge[/TD]
[TD="bgcolor: #FFFFFF"]Rot[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Shape[/TD]
[TD="bgcolor: #FFFFFF"]UK[/TD]
[TD="bgcolor: #FFFFFF"]Round[/TD]
[TD="bgcolor: #FFFFFF"]Ronde[/TD]
[TD="bgcolor: #FFFFFF"]Rund[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Colour[/TD]
[TD="bgcolor: #FFFFFF"]UK[/TD]
[TD="bgcolor: #FFFFFF"]Green[/TD]
[TD="bgcolor: #FFFFFF"]Verte[/TD]
[TD="bgcolor: #FFFFFF"]Grun[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Sheet1
And, after the macro in the same worksheet, beginning in the third column to the right of the last used column of the raw data:
Excel 2007
| A | B | C | D | E | F | G | H | I | J |
---|
Type | Store | Value | | | | | | | | |
Colour | UK | Red | | | | | | | | |
FR | Rouge | | | | | | | | | |
DE | Rot | | | | | | | | | |
Shape | UK | Round | | | | | | | | |
FR | Ronde | | | | | | | | | |
DE | Rund | | | | | | | | | |
Colour | UK | Green | | | | | | | | |
FR | Verte | | | | | | | | | |
DE | Grun | | | | | | | | | |
| | | | | | | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Type[/TD]
[TD="bgcolor: #FFFFFF"]Store[/TD]
[TD="bgcolor: #FFFFFF"]Value[/TD]
[TD="bgcolor: #FFFFFF"]France[/TD]
[TD="bgcolor: #FFFFFF"]Germany[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Colour[/TD]
[TD="bgcolor: #FFFFFF"]UK[/TD]
[TD="bgcolor: #FFFFFF"]Red[/TD]
[TD="bgcolor: #FFFFFF"]Rouge[/TD]
[TD="bgcolor: #FFFFFF"]Rot[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Shape[/TD]
[TD="bgcolor: #FFFFFF"]UK[/TD]
[TD="bgcolor: #FFFFFF"]Round[/TD]
[TD="bgcolor: #FFFFFF"]Ronde[/TD]
[TD="bgcolor: #FFFFFF"]Rund[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Colour[/TD]
[TD="bgcolor: #FFFFFF"]UK[/TD]
[TD="bgcolor: #FFFFFF"]Green[/TD]
[TD="bgcolor: #FFFFFF"]Verte[/TD]
[TD="bgcolor: #FFFFFF"]Grun[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Sheet1
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
Code:
Sub RearrangeData()
' hiker95, 02/23/2017, ME992758
Dim a As Variant, i As Long, c As Long
Dim o As Variant, j As Long
With ActiveSheet
a = .Cells(1, 1).CurrentRegion
ReDim o(1 To (UBound(a, 1) - 1) * (UBound(a, 2) - 2), 1 To 3)
For i = 2 To UBound(a, 1)
For c = 3 To UBound(a, 2)
If c = 3 Then
j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, 2)
o(j, 3) = a(i, c)
ElseIf a(1, c) = "France" Then
j = j + 1: o(j, 2) = "FR": o(j, 3) = a(i, c)
ElseIf a(1, c) = "Germany" Then
j = j + 1: o(j, 2) = "DE": o(j, 3) = a(i, c)
End If
Next c
Next i
.Columns(UBound(a, 2) + 3).Resize(, 3).ClearContents
With .Cells(1, UBound(a, 2) + 3).Resize(, 3)
.Value = Array("Type", "Store", "Value")
.Font.Bold = True
End With
.Cells(2, UBound(a, 2) + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
.Columns(UBound(a, 2) + 3).Resize(, 3).AutoFit
End With
End Sub
Then run the
RearrangeData macro.