Convert horizontal values to match vertical ones in a table

Hello everyone, I'm trying to figure out a way to readjust my table, so that the columns with my size values would shift to match their vertical Color and Article counterparts. In summery, what I want to do is to divide my data, so that every Article+Color+Size+Quantity combination would have their own unique line, because i need to generate a unique barcode for each article+color+size combination. Any help appreciated.
excel.png

Welcome to the forum!

You have marked this thread as solved - is it?

Here is a VBA solution.
I assume:
1) Your original data is on a worksheet named "Source"
2) Your data starts at cell A1
3) A blank worksheet named "Dest" exists and is where your re-arranged data will be written to.

To do the data conversion:
1) Clear contents on the "Dest" worksheet
2) Populate the "Source" worksheet with your stock data.
3) Run the "reorg_data" macro.
4) The "Dest" worksheet will be populated with the reorganized data.

Note that I have propagated the "Article" number onto each row of the "Dest" worksheet contrary to your example. This will really help later if you need to perform further analysis of the data. If you really need blanks in the "Article" column then the macro can easily be changed to do that.

See the attached workbook.

Here's the VBA:

'Re-arrange stock data
Option Explicit
Dim rng As Range, rngO As Range
Dim sourceRowNum As Integer, sourceColNum As Integer, destRowNum As Integer

Sub reorg_data()
    With Worksheets("Source")
        Set rng = .UsedRange
        destRowNum = 2
        Set rngO = Worksheets("Dest").Range("A1")
        rngO.Cells(1, 1) = "Article"
        rngO.Cells(1, 2) = "Color"
        rngO.Cells(1, 3) = "Size"
        rngO.Cells(1, 4) = "Quantity"
        For sourceRowNum = 2 To rng.Rows.Count
            'Article
            rngO.Cells(destRowNum + 0, 1) = .Cells(sourceRowNum, 1)
            rngO.Cells(destRowNum + 1, 1) = .Cells(sourceRowNum, 1)
            rngO.Cells(destRowNum + 2, 1) = .Cells(sourceRowNum, 1)
            rngO.Cells(destRowNum + 3, 1) = .Cells(sourceRowNum, 1)
            'Color
            rngO.Cells(destRowNum + 0, 2) = .Cells(sourceRowNum, 2)
            rngO.Cells(destRowNum + 1, 2) = .Cells(sourceRowNum, 2)
            rngO.Cells(destRowNum + 2, 2) = .Cells(sourceRowNum, 2)
            rngO.Cells(destRowNum + 3, 2) = .Cells(sourceRowNum, 2)
            ' S, M, L, XL
            rngO.Cells(destRowNum + 0, 3) = .Cells(1, 3)
            rngO.Cells(destRowNum + 1, 3) = .Cells(1, 4)
            rngO.Cells(destRowNum + 2, 3) = .Cells(1, 5)
            rngO.Cells(destRowNum + 3, 3) = .Cells(1, 6)
            ' Quantities
            rngO.Cells(destRowNum + 0, 4) = .Cells(sourceRowNum - 0, 3)
            rngO.Cells(destRowNum + 1, 4) = .Cells(sourceRowNum - 0, 4)
            rngO.Cells(destRowNum + 2, 4) = .Cells(sourceRowNum - 0, 5)
            rngO.Cells(destRowNum + 3, 4) = .Cells(sourceRowNum - 0, 6)
            destRowNum = destRowNum + 4
        Next sourceRowNum
    End With
End Sub