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	1
File#	Content
1001	aaaa
1001	bbbb
1001	cccc
2002	xxxx
2002	yyyy
2002	zzzz

<TBODY>
</TBODY>

Table	2
File #	Content-1	Content-2	Content-3
1001	aaaaa	bbbb	cccc
2002	xxxx	yyyy	zzzz

<TBODY>
</TBODY>

 

[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	1
File#	Content
1001	aaaa
1001	bbbb
1001	cccc
2002	xxxx
2002	yyyy
2002	zzzz

<tbody>
</tbody>

Table	2
File #	Content-1	Content-2	Content-3
1001	aaaaa	bbbb	cccc
2002	xxxx	yyyy	zzzz

<tbody>
</tbody>

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.