0

I want to accomplish this in either Excel or Access. I need to find the most efficient way to do this otherwise my users will not consistently do it. Ultimately, I would want it to be in the final form in order to make queries off of in Access, but I listed Excel as an option because I can always import into Access from a spreadsheet.

In a nutshell, I want to transpose every column except for the first 2 columns. Then, I want to assign each transposed row the original corresponding item and description.

The method I used was copying and special pasting the 3rd, 4th, and 5th rows using the transpose paste in Excel, then manually copying each item and description to the corresponding rows, but this isn't feasible for the nearly 400 rows and 50 columns that my users will be working with.

I have experience with Access/VB for Access, Excel (but no VB experience here), and SQL. I want to find the fastest way to do this. I don't mind building query upon query or going in on some VB code, I just really want this to work.

whatwhatwhat
  • 1,991
  • 4
  • 31
  • 50
  • Similar: https://stackoverflow.com/questions/36365839/excel-macrovba-to-transpose-multiple-columns-to-multiple-rows/36366394#36366394 – Tim Williams May 24 '16 at 16:52

1 Answers1

1

Before running the code below, please delete all data in columns H through K. Otherwise, the code will not work.

Sub CreateTable()
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    startRow = 2
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    startCol = 3
    counter = 2

    For rowCnt = startRow To lastRow
        itemStr = Cells(rowCnt, 1).Value
        descStr = Cells(rowCnt, 2).Value
            For colCnt = startCol To lastCol
                dateStr = Cells(1, colCnt).Value
                qnty = Cells(rowCnt, colCnt).Value
                Cells(counter, 8).Value = itemStr
                Cells(counter, 9).Value = descStr
                Cells(counter, 10).Value = qnty
                Cells(counter, 11).Value = dateStr
                counter = counter + 1
            Next
        Next

    Cells(1, 8).Value = "item"
    Cells(1, 9).Value = "description"
    Cells(1, 10).Value = "expected_qty"
    Cells(1, 11).Value = "expected_job_date"

End Sub
poppertech
  • 1,286
  • 2
  • 9
  • 17
  • This worked great! Is this scale-able to more rows and columns?? – whatwhatwhat May 24 '16 at 18:41
  • 1
    Yes, this line counts the number of rows based on the data in the first column: lastRow = Cells(Rows.Count, 1).End(xlUp).Row. Then, this line counts the number of columns based on the data in the first row:lastCol = Cells(1, Columns.Count).End(xlToLeft).Column. Please remember to upvote and mark the question as answered. – poppertech May 24 '16 at 18:43