VBA or Power Query or combo to unpivot my table

[gaosiyou]

Hi, I've been tring to make my file lighter on code and powerqueries to speed things up. my source table has over 40 columns and thousands of rows but for simplicity's skae, let's say I have the following table

ID	ColumnArray	ColumnArray2
1​	a	e
2​	b	f
3​	b	g
4​	c
5​	d	h
6​	d	g
7​	d	i

Where both Arrays have same columns but with different names (and different values). I'm trying to finish with a table with the following configuration

ID	ColumnArray
1.1	a
1.2	e
2.1	b
2.2	f
3.1	b
3.2	g
4.1	c
5.1	d
5.2	h
5.3	g
5.4	i

Also have an additional constraint that I shouldn't modify the raw values in any way. For example, I haven't been able to copy paste "001" as it should stay like that and not become "1". and I haven't been able to do that with copy.
So far however, I've managed to get it done although rather clumsily in VBA with If statements. and with destrange("a1") = sourcerange.cell(1,1) lines for each column in each array, looping through the source data rows.

Any Ideas on something prettier / smoother ?

Thank you

 

[mse330]

I know that you can unpivot using Power Query or through VBA as shown below

Sub test()

Dim a
a = [a1].CurrentRegion '<-- assuming your data starts from cell A1
ReDim b(1 To UBound(a) * UBound(a, 2), 1 To 2)

For x = 2 To UBound(a)
    For y = 2 To UBound(a, 2)
        If a(x, y) <> "" Then
            i = i + 1: ii = ii + 1
            b(i, 1) = a(x, 1) & "." & ii
            b(i, 2) = a(x, y)
        End If
    Next
    ii = 0
Next

With [H2].Resize(i, 2)
    .NumberFormat = "@"
    .Value = b
End With

End Sub