| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How do I convert the format? (Please see msg for details)
I have once sheet with more than 500 blocks of data in this format:
--------------------------------------|-----------------|-------------- comapny1 --------------------------------------|-----------------|-------------- Website: web1 --------------------------------------|-----------------|-------------- Location: loca1 --------------------------------------|-----------------|-------------- How can I convert them to a arranged format like this: --------------------------------------|-----------------|-------------- COMPANY | WEBSITE | LOCATION --------------------------------------|-----------------|-------------- company 1 |web1 | loca1 --------------------------------------|-----------------|-------------- company 2 |web2 | loca2 --------------------------------------|-----------------|-------------- company 3 |web3 | loca3 --------------------------------------|-----------------|-------------- Upendra Singhai ______________ visit: www.upendrasinghai.com |
|
#2
|
|||
|
|||
|
Look up "Transpose Rows To Columns" in the help files.
-- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Upendra Singhai" wrote in message ... I have once sheet with more than 500 blocks of data in this format: --------------------------------------|-----------------|-------------- comapny1 --------------------------------------|-----------------|-------------- Website: web1 --------------------------------------|-----------------|-------------- Location: loca1 --------------------------------------|-----------------|-------------- How can I convert them to a arranged format like this: --------------------------------------|-----------------|-------------- COMPANY | WEBSITE | LOCATION --------------------------------------|-----------------|-------------- company 1 |web1 | loca1 --------------------------------------|-----------------|-------------- company 2 |web2 | loca2 --------------------------------------|-----------------|-------------- company 3 |web3 | loca3 --------------------------------------|-----------------|-------------- Upendra Singhai ______________ visit: www.upendrasinghai.com |
|
#3
|
|||
|
|||
|
Another play to try ..
Assuming data is in col A, from row1 down, with no blank rows between the "blocks" of 3 lines Put in B1: =OFFSET($A$1,ROWS($A$1:A1)*3-3+COLUMNS($A$1:A1)-1,) Copy B1 across to D1, fill down until zeros appear, signalling exhaustion of data from col A (Since you have 500 blocks, fill down till D500 thereabouts) Cols B to D will return the data from col A in the desired format Freeze the values in cols B to D with a copypaste specialvalues either in-place or elsewhere --- And if there's 1 blank row in-between each "block" Put instead in B1: =OFFSET($A$1,ROWS($A$1:A1)*4-4+COLUMNS($A$1:A1)-1,) (Rest of the steps remain unchanged) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Upendra Singhai" wrote in message ... I have once sheet with more than 500 blocks of data in this format: --------------------------------------|-----------------|-------------- comapny1 --------------------------------------|-----------------|-------------- Website: web1 --------------------------------------|-----------------|-------------- Location: loca1 --------------------------------------|-----------------|-------------- How can I convert them to a arranged format like this: --------------------------------------|-----------------|-------------- COMPANY | WEBSITE | LOCATION --------------------------------------|-----------------|-------------- company 1 |web1 | loca1 --------------------------------------|-----------------|-------------- company 2 |web2 | loca2 --------------------------------------|-----------------|-------------- company 3 |web3 | loca3 --------------------------------------|-----------------|-------------- Upendra Singhai ______________ visit: www.upendrasinghai.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert numbers from text format to number format | Excel Discussion (Misc queries) | |||
| Convert hours and minutes in time format into fractions of hours.. | Excel Worksheet Functions | |||
| Create a custom format to convert seconds to minutes. | Excel Discussion (Misc queries) | |||
| convert a numerical currency into word format | Excel Worksheet Functions | |||
| convert .123 format to Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
