Format Report-Vertical to Horizontal?

[sammipd]

My spreadsheet of 700 rows looks like Table1 and I must create a report formatted as Table2. I'm having no success with pivot tables and know no other way. The File#'s each have between 1 and 15 rows of Content. Can anyone help? I'm using Excel 2007. Thanks!
[TABLE="width: 50"]
<TBODY>[TR]
[TD]Table[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]File#[/TD]
[TD]Content[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]aaaa[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]bbbb[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]cccc[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]xxxx[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]yyyy[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]zzzz[/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 50"]
<TBODY>[TR]
[TD]Table[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]File #[/TD]
[TD]Content-1[/TD]
[TD]Content-2[/TD]
[TD]Content-3[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]aaaaa[/TD]
[TD]bbbb[/TD]
[TD]cccc[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]xxxx[/TD]
[TD]yyyy[/TD]
[TD]zzzz[/TD]
[/TR]
</TBODY>[/TABLE]

 

[pplstuff]

Why not just copy/pastespecial/transpose? Or, =transpose() entered as an array and then copy/pastespecial/values?? If you only have to do it 10 times it shouldn't take more than 3 to 5 minutes.

***Edit:

Don't do what I said above, I didn't notice your data set.

Use Vlookup instead!

 

[sammipd]

My VLookup skills are minimal. How do I tell Excel to look for the 2-15 of the Content rows for the same File#?

 

[mirabeau]

My spreadsheet of 700 rows looks like Table1 and I must create a report formatted as Table2. I'm having no success with pivot tables and know no other way. The File#'s each have between 1 and 15 rows of Content. Can anyone help? I'm using Excel 2007. Thanks!
[TABLE="width: 50"]
<tbody>[TR]
[TD]Table[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]File#[/TD]
[TD]Content[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]aaaa[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]bbbb[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]cccc[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]xxxx[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]yyyy[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]zzzz[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 50"]
<tbody>[TR]
[TD]Table[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]File #[/TD]
[TD]Content-1[/TD]
[TD]Content-2[/TD]
[TD]Content-3[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]aaaaa[/TD]
[TD]bbbb[/TD]
[TD]cccc[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]xxxx[/TD]
[TD]yyyy[/TD]
[TD]zzzz[/TD]
[/TR]
</tbody>[/TABLE]

If you're happy with VBA macros, try running this one on a test sheet. It should start your reordered data from cell D1

Sub reorder()Dim d As Object, u(), c()
Dim a, e, ra As Long, i As Long
Set d = CreateObject("scripting.dictionary")
a = Range("A1").CurrentRegion
ra = UBound(a, 1)
ReDim u(1 To ra, 1 To 2), c(1 To ra + 1)
For i = 2 To ra
    e = a(i, 1)
    If Not d.exists(e) Then
        d(e) = d.Count + 1
        u(d(e), 1) = e
        u(d(e), 2) = a(i, 2)
        c(d(e)) = 2
    Else
        c(d(e)) = c(d(e)) + 1
        If c(d(e)) > UBound(u, 2) Then _
            ReDim Preserve u(1 To ra, 1 To c(d(e)))
        u(d(e), c(d(e))) = a(i, 2)
    End If
Next i
Cells(1, 4) = "File#"
For i = 1 To UBound(u, 2) - 1
    Cells(1, 4 + i) = "Content" & i
Next i
Cells(2, 4).Resize(d.Count, UBound(u, 2)) = u
End Sub

This is faster than doing it with a pivot table, and less setting up needed.