If you were just flipping rows and columns you could copy the range and paste special into another sheet, checking 'transpose'.
What you want to do is take the data given and make a new combined list out of it. This would have to be done with code. Create a button on the source workbook.
This code assumes source sheet is named Sheet1 and target sheet is named Sheet2.
Paste this code into the button's routine:
------------------------------------------------------------------------------------------------------
'This routine takes given table data and formulates a combined adjusted record on new sheet
Dim oSource As Worksheet, oTarget As Worksheet
Dim iRowOnSrc As Long, iRowOnTgt As Long, iColOn As Integer
Set oSource = ActiveWorkbook.Worksheets("Sheet1")
Set oTarget = ActiveWorkbook.Worksheets("Sheet2")
'oTarget.Range("A:IV").Delete 'Remove ' at beginning if you first want to clear target sheet
With oTarget
' Writes out headings
.Range("1:1").Font.Bold = True
.Cells(1, 1).Value = "Film Name"
.Cells(1, 2).Value = "Viewer Name"
.Cells(1, 3).Value = "Viewed"
.Range("1:1").Columns.AutoFit
iColOn = 2 'Initializes Column on for source.
iRowOnTgt = 3 'Initializes target row.
' Processes current records. Assumes Heading Row is Row 1.
Do While .Cells(1, iColOn).Value <> ""
iRowOnSrc = 3 'Resets row for source for each name
Do While oSource.Cells(iRowOnSrc, 1).Value <> ""
.Cells(iRowOnTgt, 1).Value = oSource.Cells(iRowOnSrc, 1).Value
.Cells(iRowOnTgt, 2).Value = oSource.Cells(1, iColOn).Value
.Cells(iRowOnTgt, 3).Value = oSource.Cells(iRowOnSrc, iColOn).Value
iRowOnSrc = iRowOnSrc + 1
iRowOnTgt = iRowOnTgt + 1
Loop
iColOn = iColOn + 1
Loop
End With
------------------------------------------------------------------------------------------------------
This code does what is desired with the given source data resulting in desired output.
Hope this helps.
|
