Transpose Row with mutiple columns to Column with single row

B

brendanolear

Active Member
Joined
Apr 26, 2008
Messages
366
Hi,

I am trying to convert a table in the following way. Is there a way to realise this using a make table query?

[TABLE="width: 336"]
<colgroup><col width="64" style="width: 48pt;" span="7"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Name[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]WK1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]WK2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]WK3[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]WK4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]WK5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]WK6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bill[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl66, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl66, bgcolor: #F2F2F2"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Jack[/TD]
[TD="class: xl66, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl66, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Name[/TD]
[TD="class: xl65, bgcolor: transparent"]Week[/TD]
[TD="class: xl65, bgcolor: transparent"]Total[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bill[/TD]
[TD="class: xl65, bgcolor: transparent"]WK1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bill[/TD]
[TD="class: xl65, bgcolor: transparent"]WK2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bill[/TD]
[TD="class: xl65, bgcolor: transparent"]WK3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bill[/TD]
[TD="class: xl65, bgcolor: transparent"]WK4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tom[/TD]
[TD="class: xl65, bgcolor: transparent"]WK6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]33[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Glad to see that you are normalizing your table. Smart move!

Quite frankly, I think the easiest way (at least for me) is to export to Excel, change the structure there, and re-import to Access.
 
Upvote 0
sql-wise you would write an insert statement for each column (which would be a little ugly but not as bad as it sounds since you can just do some copy pasting and the only thing really changing is the column name).

You could write a script for this if it's a regular thing but getting it into a normalized table will be good and just a one off thing in that case.

Edit: note that you will have to think about how you want to treat the null/blank values (i.e.Bill weeks 5 and 6 and Jack weeks 1 and 2) - based on your example output it sounds like you want to not include them (this however means that reports that need to show all weeks for Bill or Jack will require some additional steps to show the "missing" weeks).
 
Last edited:
Upvote 0
Here is some Excel VBA code I whipped up for you that should do it, based on your sample data. I documented it to explain what each step does.
Code: 
Sub TransposeData()

    Dim ss As Worksheet
    Dim ds As Worksheet
    Dim lastCol As Long
    Dim lastRow As Long
    Dim recNum As Long
    Dim myCol As Long
    Dim myRow As Long
    Dim myName As String
    Dim myWeek As String
    Dim myNum As Double
    
    Application.ScreenUpdating = False
    
'   Set source and destination sheets
    Set ss = Sheets("Sheet1")
    Set ds = Sheets("Sheet2")
    
'   Find last row and column on sheet
    lastCol = ss.Cells(1, Columns.Count).End(xlToLeft).Column
    lastRow = ss.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Add titles to destination sheet
    ds.Range("A1") = "Name"
    ds.Range("B1") = "Week"
    ds.Range("C1") = "Total"
    
    recNum = 1
    
'   Loop through all rows starting in row 2
    For myRow = 2 To lastRow
'       Get name
        myName = ss.Cells(myRow, "A")
'       Loop through all columns starting in column 2
        For myCol = 2 To lastCol
'           Check to see if any value (if they do, add entry)
            If ss.Cells(myRow, myCol) <> 0 Then
'               Get values
                myWeek = ss.Cells(1, myCol)
                myNum = ss.Cells(myRow, myCol)
'               Write to destination sheet
                recNum = recNum + 1
                ds.Cells(recNum, "A") = myName
                ds.Cells(recNum, "B") = myWeek
                ds.Cells(recNum, "C") = myNum
            End If
        Next myCol
    Next myRow
    
    Application.ScreenUpdating = True
                
    MsgBox "Process complete!"
                
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Summing a group?
Replies
5
Views
577
Fluff
Fluff
B
  • Question Question
Transpose Table
Replies
7
Views
1K
Joe4
Joe4
M
  • Question Question
Bill Of Material Forecast Based on Timing of Material Change
Replies
4
Views
1K
Swayzy
S
M
  • Question Question
Transposing data to a single row
Replies
4
Views
474
Mega_man87
M
B
  • Question Question
Weeks of cover including negatives
Replies
4
Views
1K
Eric W
Eric W

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top