Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 | |
|
|
![]() |
||||
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) |