CODEWITHSUNDEEP
excelvbams-accessrows
user1572395
user1572395

Reputation: 1

Convert columns into rows respectively in SQL

I really have been looking hard for an answer and still can't find one. Please help!

I have a workbook in excel that I would like to manipulate in Access using VBA or SQL.

The current problem I am facing is I have a spreadsheet where each row is a based on one invoice (2500 rows). Each row consists of "Invoice Number" "PO Number" "Invoice Date" "Invoice Total". We can call that Invoice Data

Then in that row there are cells based on Line Item information, "Item Number" "Item Cost" "Item Quantity". Line Item Data

And the Line Item Data repeats until each line item from the invoices covered (i.e. if 70 line items, there would be 70 different line item data and a bunchhhhh of columns)

I need a way to make it so each row is based on the Line Item information, instead of the invoice information, while each row still keeps the items respective invoice info ( "Invoice Number" "PO Number" "Invoice Date" "Invoice Total").

Also the code needs to be simple enough that I don't need to tell it to copy line 1 item data then line 2 item data, etc until line item 70 data. (Maybe it can understand every 3 columns is a new item and the row it is on has its invoice info that it will take). It also can't stop when there are blank columns on row 1 because there could be 50 items in row 30. (invoice 1 had 3 line items, invoice 30 had 50 line items)

I will continue to look to find an answer and if I do find one I will post it here.. Thank you for your help in advance!

Upvotes: 0

Views: 720

Answers (1)

Tim Williams
Tim Williams

Reputation: 166725

Sub UnPivot()

Const INV_COLS As Integer = 4   'how many columns in the invoice info
Const ITEM_COLS As Integer = 3  'columns in each set of line item info

Dim shtIn As Worksheet, shtOut As Worksheet
Dim c As Range
Dim rOut As Long, col As Long


    Set shtIn = ThisWorkbook.Sheets("Data")
    Set shtOut = ThisWorkbook.Sheets("Output")

    Set c = shtIn.Range("A2") 'first cell in invoice info
    rOut = 2                  'start row on output sheet

    'while the invoice cell is non-blank...
    Do While Len(c.Value) > 0
        col = INV_COLS + 1   'column for first line item
        'while there's info in the line item cell 
        Do While Application.CountA(shtIn.Cells(c.Row, col).Resize(1, ITEM_COLS)) > 0

            'copy the invoice info (four cells)
            c.Resize(1, INV_COLS).Copy shtOut.Cells(rOut, 1)

            'copy the line item info (3 cells)
            shtIn.Cells(c.Row, col).Resize(1, ITEM_COLS).Copy _
                             shtOut.Cells(rOut, INV_COLS + 1)

            rOut = rOut + 1           'next output row
            col = col + ITEM_COLS     'move over to next line item
        Loop
        Set c = c.Offset(1, 0) 'move down one cell on input sheet
    Loop

    shtOut.Activate
End Sub

Upvotes: 1

Related Questions