Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Rows and Columns
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Rows and Columns
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 "Michael" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging Columns | Excel Worksheet Functions | |||
Merging Two Columns | Excel Discussion (Misc queries) | |||
merging columns | New Users to Excel | |||
Merging columns | New Users to Excel | |||
Merging Columns | Excel Worksheet Functions |