I would suggest a PivotTable for arranging data.

See Debra Dalgleish's site for more on PT's

http://www.contextures.on.ca/tiptech.html

Scroll down to "P"


Gord

On Fri, 11 May 2007 14:34:02 -0700, yaling
wrote:

Hi Gord,

Thanks. It is fun to play with Excel.

I am deriving Share Prices for 20 years from Datastream. Each company's
prices are displayed in one colume from row 1 to row 20 when derived from
Datastream. Defferent companies are in different columes. In order to run a
statistic program, I need to rearrange Share prices to put all data in one
colume followed company by company. Is there any efficient way to have this
job done?

The Share prices could also be displayed in different rows from colume 1 to
20 if I choose "Transpose data". Which way is easy to be rearranged?

Thanks, and have a nice weekend.

Yaling

"Gord Dibben" wrote:

You could enter them into your ToolsAutocorrect Options and have them available
for all workbooks.

That could lead to some strange results if those words are typed and you don't
want the numbers.

In that case type the word then hit <space + CTRL + z to bypass the correction.


Gord

On Thu, 10 May 2007 17:34:00 -0700, yaling
wrote:

Thank you Gord Dibben. I really appreciate your help.

Now I need to replace multiple words with numbers. For example, replace bank
with 23, replace retailer with 32, ..... And I will do the same thing for
several different files. How can I save these words and number to avoid
retyping them when I do replacing again and again?

Thank you again.

Yaling

"yaling" wrote:

Dear Gord Dibben,

Your solution is really helpful. You are so smart.

Can I ask you another question? After i got them separated, i need them to
repeat themselves in the blank rows below the company name? How can i do that?

Thanks a lot. Looking forward to your reply.

Yaling

"Gord Dibben" wrote:

yaling

Are you up for some VBA macro?

The following will insert a blank row at each change in value in Column A

Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) < Cells(i, 1) Then _
Cells(i, 1).Resize(1, 1).EntireRow.Insert
'change .Resize(1, 1) to (2, 1) for two blank rows.
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 9 May 2007 16:50:01 -0700, yaling
wrote:

hello,

i have a company name list with thousands of companies. they are in one
colume and followed one by one. i need them to be seperated with equal blank
cells between each two companies. Is there any efficient way to get the job
done?

thanks,