Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Upendra Singhai
 
Posts: n/a
Default 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   Report Post  
Ragdyer
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert numbers from text format to number format merlin68 Excel Discussion (Misc queries) 7 June 20th 07 08:03 PM
Convert hours and minutes in time format into fractions of hours.. Akern Excel Worksheet Functions 4 April 21st 05 03:56 PM
Create a custom format to convert seconds to minutes. XOXO Excel Discussion (Misc queries) 3 April 13th 05 12:34 AM
convert a numerical currency into word format Rojo Excel Worksheet Functions 1 February 25th 05 12:31 AM
convert .123 format to Excel? Doug Rainville Excel Discussion (Misc queries) 5 January 28th 05 10:45 PM


All times are GMT +1. The time now is 08:34 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"