Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Data
Hi all,
I have a something that I would have thought Excel could handle but have no idea how to actually do it. I have an excel sheet with 20,000 rows which is populated with a unique ID number and a phone number however rather than each phone number being placed on the same line that corresponds with each phone number it has been placed on the next line down ie Unique ID Phone Number 123 0161 555 2269 123 0161 555 1234 123 and I need it to be Unique ID Phone Number 123 0161 555 2269 0161 555 1234 thanks in advance Michael |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Data
If the data is always in groups of 3 lines as posted,
then you could easily draw it out this way Assume data in A2:B2 down In C2: =OFFSET($A$2,ROWS($1:1)*3-3,) In D2: =OFFSET($B$2,ROWS($1:1)*3-3+COLUMNS($A:A)-1,) Copy D2 to F2. Select C2:F2, fill down as far as required to exhaust the data, until zeros are returned. Cols C to F returns the desired results. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Michael" wrote: I have a something that I would have thought Excel could handle but have no idea how to actually do it. I have an excel sheet with 20,000 rows which is populated with a unique ID number and a phone number however rather than each phone number being placed on the same line that corresponds with each phone number it has been placed on the next line down ie Unique ID Phone Number 123 0161 555 2269 123 0161 555 1234 123 and I need it to be Unique ID Phone Number 123 0161 555 2269 0161 555 1234 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Data
Hi,
Assuming that the data is already sorted Name the headers below the data table ie in the blanks cells after the table is over. Unique id and phone numbers Then under unique id you can put the number. Then select & copy the phone numbers then under the header phone number put the cursor on the cell and right clickPaste specialcheck the transpose boxOk this will paste the data and will give you the result as expressed by you in your example. -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Michael" wrote: Hi all, I have a something that I would have thought Excel could handle but have no idea how to actually do it. I have an excel sheet with 20,000 rows which is populated with a unique ID number and a phone number however rather than each phone number being placed on the same line that corresponds with each phone number it has been placed on the next line down ie Unique ID Phone Number 123 0161 555 2269 123 0161 555 1234 123 and I need it to be Unique ID Phone Number 123 0161 555 2269 0161 555 1234 thanks in advance Michael |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Data
But I'm not sure that's practical here, given the 20,000 rows,
ie OP would have to repeat the steps ~ 6,700 times ? -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Suleman Peerzade" wrote in message ... Assuming that the data is already sorted Name the headers below the data table ie in the blanks cells after the table is over. Unique id and phone numbers Then under unique id you can put the number. Then select & copy the phone numbers then under the header phone number put the cursor on the cell and right clickPaste specialcheck the transpose boxOk this will paste the data and will give you the result as expressed by you in your example. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Data | Excel Discussion (Misc queries) | |||
Sorting data to match existing data | Excel Discussion (Misc queries) | |||
colors of bar charted data don't follow data after sorting | Charts and Charting in Excel | |||
Sorting Data | Excel Discussion (Misc queries) | |||
help on sorting data | Excel Discussion (Misc queries) |