Merging Rows and Columns

[Michael]

Hi,
I have been given a lot of data which is formated in the wrong way and
wondered if there was a solution to format it into the right order?

The data looks like this

Unique ID Tel Type Number
1234 Home 012345678
1234 Mobile 987654321
4321 Home 12345678
4321 Mobile 654987123

and it should look like this

Unique ID Tel Type Number Tel Type Number
1234 Home 012345678 Mobile 987654321
4321 Home 12345678 Mobile 654987123

Many thanks

Michael

 

[Mike H]

Michael

Looking after the new column D is simply a matter of typing in mobile and
dragging it down. To extract the mobile numbers to coumn E put this formula
in E2 and drag down

=INDEX($C$2:$C$20,MATCH(1,($A$2:$A$20=A2)*($B$2:$B$20="Mobile"),0))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Select this newly created list of mobile Nos and copy it. Paste it back over
itself with
Edit|Paste Special values


You now need to delete the duplicate rows and 1 way is to put this in F2 and
drag down
=E2=C2

Sort all your data by this helper column and delete all the rows that
evaluate as TRUE.

Mike