Rearranging Data, Sheet 1 into Sheet 2

Thanks for the info ...

30 Columns and 5'000 rows will require using Arrays

As soon as I have a moment ... will dive into your code ...

Attached is your Version 2 test file

Hope this will help

Glad to hear it is an improvement ...

Quite honestly ... have not had the time to test it ...

As soon as I have a moment ... will check the step calculation ...

Quickly checked at my end ... everything seems fine ...

Would you mind posting a sample file with the number of languages you want ... and say 5 to 10 records ...

Hello,

Not exactly the same format ...

In your initial file, Column B or English had to be skipped ... 7 Columns and 6 Languages to display ...

Up until now ...the Number of Languages was designed to be a manual adjustment ...

Will prepare the two modifications .... as soon as you do confirm Column B has to be re-integrated in the process ...

Attached is your last Version 3

Hope this will help

Thanks a lot for your Likes

Hello,

To be on the safe side, in terms of performance, for 30 Columns and 5'000 rows, below is your optimized Array Transformation

to handle all your languages and translations ....

Sub Array_Transformation()
Dim iArray As Variant               
Dim oArray() As String         
Dim i As Long, j As Long, k As Long
Dim lstep As Long
    Sheet2.[a1].CurrentRegion.ClearContents
    iArray = Sheet1.[a1].CurrentRegion
    lstep = UBound(iArray, 2) - 1
    ReDim Preserve oArray(0 To (lstep * (UBound(iArray) - 1)) - 1, 1 To 3)
        For i = LBound(iArray) + 1 To UBound(iArray)
            j = (i - 2) * lstep
               For k = 0 To lstep - 1
                    oArray(j + k, 1) = iArray(i, 1)
                    oArray(j + k, 2) = iArray(1, 2 + k)
                    oArray(j + k, 3) = iArray(i, 2 + k)
               Next k
        Next i
    Sheet2.Range("A2").Resize((lstep * (UBound(iArray) - 1)), 3) = oArray()
Application.Goto Sheet2.[a1]
End Sub

Hope this will help