How do I convert the format? (Please see msg for details)

[Guest]

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

 

[Ragdyer]

Look up "Transpose Rows To Columns" in the help files.

 

[Max]

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 copy>paste special>values 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)