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

Hi Phil

You could do it with a Pivot Table
Mark your complete range of data.
DataPivot TableNextNextLayout
Drag ID to Row Area, drag Type to Column Area and drag Tel Num to Data Area
Double click on Tel Num filed in data Area and ensure Sum is selected OK
Click Optionsdeselect Grand Total for Columns and Grand Total for RowsOK
Click Finish

Regards

Roger Govier


philawde wrote:
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

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

hey ive tried both of these methods... using the pivot table jst meant i
had the right columns and headings but for the numbers i jst got loads
of 0's in the correct boxes but not the numbers for that id. and weneva
i ty and use that code it keeps crashing! ne help?? email me at
i can show file if needs b


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

and for the other version?

--

HTH

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


"philawde" wrote in
message ...

hey ive tried both of these methods... using the pivot table jst meant i
had the right columns and headings but for the numbers i jst got loads
of 0's in the correct boxes but not the numbers for that id. and weneva
i ty and use that code it keeps crashing! ne help?? email me at
i can show file if needs b


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

when i entered code and tried to run it, excel just kept crashing and i
cudnt do nething else, i tried 3 times trying diff methods and it stil
wouldnt have it! any ideas?


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

Hi

I get the telephone numbers, using the sample data you posted.
I guess your numbers are Text and not numbers, that would give 0's
throughout the PT.

Format your Tel No column as FormatCellsNumberCustom 00000000000
Enter a 1 into a blank cell on your sheet. Copy that cell.
Mark your range of numbers and Paste SpecialMultiply. This will turn them
to numerics.
Refresh the PT and you should see the result as required.

Regards

Roger Govier


philawde wrote:
hey ive tried both of these methods... using the pivot table jst meant i
had the right columns and headings but for the numbers i jst got loads
of 0's in the correct boxes but not the numbers for that id. and weneva
i ty and use that code it keeps crashing! ne help?? email me at
i can show file if needs b

Can't help then, it worked fine for me.

--

HTH

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


"philawde" wrote in
message ...

when i entered code and tried to run it, excel just kept crashing and i
cudnt do nething else, i tried 3 times trying diff methods and it stil
wouldnt have it! any ideas?


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