|
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 |
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 |
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 |
| All times are GMT +1. The time now is 02:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com