Macro to convert column data to "raw data"

[michaelr81]

Hello all,
I would like to convert the data arranged in columns into a raw data format. Something similar to a reverse pivot table. It's possible that I will have around 2000 rows (items) and 1000 columns (locations) in the original format, maybe even more. Also the data may start from a different row or column number, but I can rearrange it as required.

Unfortunately transpose does not work here. Could any one kindly help ? Thank you : )

 

[mohadin]

Hi
What about

Sub test()
Dim a, d
Dim i&, ii&, c&
a = Sheets("sheet3").UsedRange  ' Change sheets name to yours
With CreateObject("scripting.dictionary")
        For ii = 4 To UBound(a, 2)
            d = Split(a(1, ii)): d = Join(Array(d(0), d(1)))
            For i = 2 To UBound(a)
                     If a(i, ii) <> "" Then .Add a(i, 1) & "|" & a(i, 2) & "|" & a(i, 3) & "|" & d & "|" & a(i, ii), ""
            Next
        Next
a = Application.Transpose(.keys)
End With
With Sheets("Destination").Cells(2, 1).Resize(UBound(a) ) ' Change sheets name to yours
    .CurrentRegion.ClearContents
    .Offset(-1).Resize(1, 5) = Array("item", "color", "Description", "location Name", "Order")
    .Value = a
    .Resize(UBound(a)).Columns(1).TextToColumns .Cells(1, 1), 1, other:=True, otherchar:="|"
    .Offset(-1).Resize(, 5).HorizontalAlignment = xlCenter
    .Columns.AutoFit
   End With
End Sub

 

[michaelr81]

works fine thank you

 

[mohadin]

You are very welcome
And thank you for the feedback
Be happy and safe