Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim iNext As Long
Dim iRow As Long
Dim iCol As Long
Dim tmp

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iNext = 1
Range("D1").Value = "ID"
For i = 2 To iLastRow
iRow = 0
On Error Resume Next
iRow = Application.Match(Cells(i, "A").Value, Range("D:D"), 0)
On Error GoTo 0
If iRow = 0 Then
iNext = iNext + 1
Cells(iNext, "D").Value = Cells(i, "A").Value
End If
iCol = 0
On Error Resume Next
iCol = Application.Match(Cells(i, "C").Value, Range("1:1"), 0)
On Error GoTo 0
If iCol = 0 Then
iCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, iCol).Value = Cells(i, "C").Value
End If
If iRow = 0 Then
Cells(iNext, iCol).Value = Cells(i, "B").Value
Else
Cells(iRow, iCol).Value = Cells(i, "B").Value
End If
Next i

Columns("D:AZ").AutoFit
Columns("A:C").Delete

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"philawde" wrote in
message ...

hey, i have a list of id numbers of students with phone numbers and
types of number like this below - the problem is that each student has
a different amount of numbers and different types of number

ID TEL Num Type of Number
g2045012 01755465464 PR1
g2045012 02114574684 BU1
g2045012 35346347373 MU1
g2045012 52141513613 PR2
h42376347 35236276247 MU1
h42376347 23624724727 PR1
h42376347 12362762462 BU1
adgh12322 15595683464 PR1
adgh12322 32473573478 MU1

i am tryin to change the layout so that it will be one line per id
number with all the numbers on 1 row with an individual column for each
different type of number(like below) can anyone help at all please!!!!

ID PR1 BU1
MU1 PR2
g2045012 01755465464 02114574684 35346347373 52141513613
h42376347 23624724727 12362762462 35236276247
adgh12322 15595683464 12362762462 32473573478


--
philawde
------------------------------------------------------------------------
philawde's Profile:
http://www.excelforum.com/member.php...o&userid=29030
View this thread: http://www.excelforum.com/showthread...hreadid=487582