Massive Transpose

I

iklektic

New Member
Joined
Nov 12, 2009
Messages
6
Hi All!

I have a library of information in an excel sheet that I want to be able to format in a certain way.

Currently the way that it is set up is like this: (here's a snippet)
Movie Title
CategoryDrama
Minutes142
Year1994
Main ActorTim Robbins
Rating9.2
Movie TitleThe Godfather
CategoryDrama
Minutes175
Year1972
Main ActorMarlon Brando
Rating9.2

<tbody>
[TD="width: 156"]The Shawshank Redemption[/TD]

</tbody>


This table is extremely large (70,000 + rows)

I would like to know of a way to convert the aformentioned format into a more manageable row format (example below)

Movie Title
The Shawshank RedemptionDrama1421994Tim Robbins9.2
The GodfatherDrama1751972Marlon Brando9.2

<tbody>
[TD="width: 156"] Category [/TD]
[TD="width: 156"] Minutes [/TD]
[TD="width: 156"] Year [/TD]
[TD="width: 156"] Main Actor [/TD]
[TD="width: 156"] Rating [/TD]

</tbody>


Any ideas would be GREATLY APPRECIATED! Thank you.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Is the source data in subsequent columns, or does it repeat the same rows all the way down?

/AJ
 
Upvote 0
The source data is in two columns and they repeat the same way all the way down:
[TABLE="width: 120"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Movie Title[/TD]
[/TR]
[TR]
[TD]Category[/TD]
[/TR]
[TR]
[TD]Minutes[/TD]
[/TR]
[TR]
[TD]Year[/TD]
[/TR]
[TR]
[TD]Main Actor[/TD]
[/TR]
[TR]
[TD]Rating[/TD]
[/TR]
</tbody>[/TABLE]
and then over again.
 
Upvote 0
Cool. Assuming it's a one-time thing then, I'd put in an extra column in the source data to look something like this...

Excel 2010
ABC
KeyDataMovie Title
Movie TitleThe Shawshank RedemptionThe Shawshank Redemption
CategoryDramaThe Shawshank Redemption
MinutesThe Shawshank Redemption
YearThe Shawshank Redemption
Main ActorTim RobbinsThe Shawshank Redemption
RatingThe Shawshank Redemption
Movie TitleThe GodfatherThe Godfather
CategoryDramaThe Godfather
MinutesThe Godfather
YearThe Godfather
Main ActorMarlon BrandoThe Godfather
RatingThe Godfather

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]142[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1994[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9.2[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]175[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1972[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]9.2[/TD]

</tbody>
Source​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(A2=$C$1, B2, C1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Use copy/paste values; remove duplicates to put the film names in to a new sheet. And then in your destination sheet you could use this to pull the data. It'll be a bit slow but once it's done you can copy/paste values and it's all AOK.

Excel 2010
ABCDEF
Movie TitleCategoryMinutesYearMain ActorRating
The Shawshank RedemptionDramaTim Robbins
The GodfatherDramaMarlon Brando

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]142[/TD]
[TD="align: right"]1994[/TD]

[TD="align: right"]9.2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]175[/TD]
[TD="align: right"]1972[/TD]

[TD="align: right"]9.2[/TD]

</tbody>
Dest​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=INDEX(Source!$B:$B, MATCH(B$1&$A2,Source!$A:$A&Source!$C:$C,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



How's that for you?

/AJ
 
Upvote 0
Macro solution:
Rich (BB code): 
Sub TransposeData()
 
  Dim a(), b()
  Dim c As Long, i As Long, r As Long
 
  ' Check source data presence in the active sheet
  On Error Resume Next:
  a() = ActiveSheet.UsedRange.Resize(, 2).Value
  If UCase(a(1, 1)) <> "MOVIE TITLE" Or UCase(a(2, 1)) <> "CATEGORY" Then
    MsgBox "Source data in the 1st column have to be:" & vbLf _
           & "Movie Title" & vbLf & "Category" & vbLf & "e.t.c.", _
           vbExclamation, "Source data not found"
    Exit Sub
  End If
 
  ' Prepare output array
  ReDim b(1 To UBound(a) / 6 + 1, 0 To 5)
 
  ' Fill output titles
  For i = 1 To 6
    b(1, i - 1) = a(i, 1)
  Next
 
  ' Transpose
  i = 2
  For r = 1 To UBound(a)
    b(i, c) = a(r, 2)
    c = r Mod 6
    If c = 0 Then i = i + 1
  Next
 
  ' Find or create output sheet
  With Sheets("Transposed"): End With
  If Err Then
   With Sheets.Add(After:=ActiveSheet)
    .Name = "Transposed"
   End With
  End If
 
  ' Put transposed data to the output sheet
  On Error GoTo exit_:
  With Sheets("Transposed")
    .UsedRange.ClearContents
    .Range("A1").Resize(i - 1, 6).Value = b()
    .UsedRange.Columns.AutoFit
    .Activate
  End With
 
exit_:
 
  ' Report
  If Err Then
    MsgBox Err.Description, vbCritical, "Error #" & Err.Number
  Else
    MsgBox "Well done!", vbInformation, "Ok"
  End If
 
End Sub
 
Upvote 0
adam087 said:
Cool. Assuming it's a one-time thing then, I'd put in an extra column in the source data to look something like this...

Excel 2010
ABC
KeyDataMovie Title
Movie TitleThe Shawshank RedemptionThe Shawshank Redemption
CategoryDramaThe Shawshank Redemption
MinutesThe Shawshank Redemption
YearThe Shawshank Redemption
Main ActorTim RobbinsThe Shawshank Redemption
RatingThe Shawshank Redemption
Movie TitleThe GodfatherThe Godfather
CategoryDramaThe Godfather
MinutesThe Godfather
YearThe Godfather
Main ActorMarlon BrandoThe Godfather
RatingThe Godfather

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]142[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1994[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9.2[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]175[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1972[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]9.2[/TD]

</tbody>
Source​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(A2=$C$1, B2, C1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Use copy/paste values; remove duplicates to put the film names in to a new sheet. And then in your destination sheet you could use this to pull the data. It'll be a bit slow but once it's done you can copy/paste values and it's all AOK.

Excel 2010
ABCDEF
Movie TitleCategoryMinutesYearMain ActorRating
The Shawshank RedemptionDramaTim Robbins
The GodfatherDramaMarlon Brando

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]142[/TD]
[TD="align: right"]1994[/TD]

[TD="align: right"]9.2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]175[/TD]
[TD="align: right"]1972[/TD]

[TD="align: right"]9.2[/TD]

</tbody>
Dest​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=INDEX(Source!$B:$B, MATCH(B$1&$A2,Source!$A:$A&Source!$C:$C,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



How's that for you?

/AJ
Click to expand...

Thank you very much! I appreciate your help!!
 
Upvote 0
Thank you! I realized after that there were 7 rows per set. How do I modify this script to look for 7 rows instead of 6? Do I just replace all the "6's" with "7's"?
 
Upvote 0
iklektic said:
Thank you! I realized after that there were 7 rows per set. How do I modify this script to look for 7 rows instead of 6? Do I just replace all the "6's" with "7's"?
Click to expand...
Yes, replace 6 by 7 in the code (four times)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Splitting text into columns where text is located across multiple adjacent cells?
Replies
7
Views
1K
Peter_SSs
Peter_SSs
P
  • Question Question
Correcting Data
Replies
0
Views
560
parvezs27
P

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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