Automatically reorganizing 1 row into multiple columns

[GijsPraat]

Hello,

The last few days i've been trying to import a database from excel into our SQL-database. I however ran into a problem.
Background info: i built a C# app to solve a problem within our company, which has previously been solved within an Excel-sheet and database within that Excel-file. To import the Excel-database into our SQL-database, i have to re-organize 325 rows with anywhere from 4 through 12 columns into rows of 4 columns. In this case: per ColorID, KCNumber, Color and Quantity 1 row so i can call the data with a query within my C#-app.

In the sprits of 'One picture means more than a thousand words', hereby a snapshot of the database. On the left the status quo, on the right the desired situation. The total should come out to 1000+ rows, so manually adjusting things is not an option ;)
EXAMPLE.xlsx

Does anyone have a bright idea on how to approach this challenge? I've been contemplating about building a custom app which reads the database per cell, and then manually inserting them into my SQL-database but this seems like overkill.

Thanks in advance!

[bebo021999]

May be:

Option Explicit 
Sub test() 
Dim lr&, i&, j&, k&, rng, res(1 To 100000, 1 To 4) 
lr = Cells(Rows.Count, "B").End(xlUp).Row ' last used row in column B 
rng = Range("B3:O" & lr).Value ' read value into array "rng" 
For i = 1 To UBound(rng) ' row loop 
    For j = 3 To UBound(rng, 2) 'column loop from column D  to O 
        If rng(i, j) <> "" Then 'if cell is not empty 
            If WorksheetFunction.IsOdd(j) Then ' if column index =1,3,5 (color column) 
                k = k + 1 ' result row+ 1 
                res(k, 1) = rng(i, 1): res(k, 2) = rng(i, 2): res(k, 3) = rng(i, j): res(k, 4) = rng(i, j + 1) 
            End If 
        End If 
    Next 
Next 
Range("Q3:T10000").ClearContents 
Range("Q3").Resize(k, 4).Value = res 
End Sub  

[GijsPraat]

Thanks for your quick answer - it worked like a charm! You're the best, thanks!

[Czeslaw]

Power Query

Please Login or Register  to view this content.