Is it just me or is this tricky?

**philawde** · 11-23-2005, 07:38 AM

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

Roger Govier · 11-23-2005, 08:20 AM

Hi Phil

You could do it with a Pivot Table
Mark your complete range of data.
Data>Pivot Table>Next>>Next>Layout
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 Options>deselect Grand Total for Columns and Grand Total for Rows>OK
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
>
>

Bob Phillips · 11-23-2005, 08:40 AM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

**philawde** · 11-23-2005, 09:37 AM

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 [email protected] i can show file if needs b

Bob Phillips · 11-23-2005, 10:10 AM

and for the other version?

--

HTH

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

"philawde" <[email protected]> wrote in
message news:[email protected]...
>
> 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
> [email protected] 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
>

**philawde** · 11-23-2005, 10:14 AM

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?

Roger Govier · 11-23-2005, 11:10 AM

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 Format>Cells>Number>Custom> 00000000000
Enter a 1 into a blank cell on your sheet. Copy that cell.
Mark your range of numbers and Paste Special>Multiply. 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
> [email protected] i can show file if needs b
>
>

Bob Phillips · 11-23-2005, 12:00 PM

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

--

HTH

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

"philawde" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>