Sub AB()
Dim rng As Range
Dim cell As Range
Dim arr() As Long
Dim ub As Long
Set rng = Range("A1").CurrentRegion.Resize(, 4).Cells
ub = Application.Max(rng.Columns(1))
ReDim arr(1 To ub, 0 To 6)
For Each cell In rng
If cell.Column = 1 Then
Id = cell.Value
arr(Id, 0) = Id
Else
arr(Id, cell.Value) = arr(Id, cell.Value) + 1
End If
Next
Range("H1").Resize(ub, 7).Value = arr

End Sub

--
Regards,
Tom Ogilvy




"zaisaki" wrote in
message ...

Hi All,
I have a data set with variables id, a, b, c, d and looks like:


1 2 3 5 1
1 5 6 5 3
1 2 4 2 4
2 1 3 4 5
2 5 6 2 3
2 4 5 2 1


I would like to summarize data for each id and create a new variable
representing each code occuring in variables a, b and c.


Essentially the new data set should have variables id, v1 - v6.
v1 represents the number of times code 1 occurs in variables a, b and c

in the original data set, and so on for v2 - v6.


The data set would like;
1 1 3 2 2 3 1
2 2 2 2 2 1 1


Your help is much appreciated.


Thanks very much,
Zai Saki


--
zaisaki
------------------------------------------------------------------------
zaisaki's Profile:
http://www.excelforum.com/member.php...o&userid=13755
View this thread: http://www.excelforum.com/showthread...hreadid=480390