Hi,
Try this - input from "Sheet1" to output "Sheet2"

Sub a()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long, n As Integer
Dim rnga As Range
r = 2

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws2.Range("a1:f1") = Array("E-Mail", "Category", "Category", "Category",
"Category", "Category")

With ws1
lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
Set rnga = ws1.Range("a2:a" & lastrow)
Do
n = Application.CountIf(rnga, .Cells(r, "A"))
.Cells(r, "A").Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2)
.Cells(r, "B").Resize(n, 1).Copy
ws2.Cells(ws2.Cells(Rows.Count, "A").End(xlUp).Row, "B").Resize(1,
n) _
.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
r = r + n
Loop Until r lastrow
End With
End Sub

"saybut" wrote:


Hi, I have quite a tricky problem.. (I've attached a sheet as an
example, I'll explain it further down)

I have a list of names in column a (a very large amount), there are
lots of duplicate names, and next to them in column b are the
categories associated with each name. So if a name is repeated 5 times
in column a, it will have 5 different categories.

I need to summarize it so that I only have 1 name per person, but all
of the categories associated with the person going along in each column
as per the sheet attached.

I've been trying to figure it out but can't do it (except manual paste
specialtranspose which would take me weeks!)

If anyone has any idea how I could possibly automate this I'd be very
grateful.

thanks very much for any suggestions.

regards.


+-------------------------------------------------------------------+
|Filename: Example_Transpose.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4426 |
+-------------------------------------------------------------------+

--
saybut
------------------------------------------------------------------------
saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949
View this thread: http://www.excelforum.com/showthread...hreadid=519801