Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default how to seperate cells with equal blank cells

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,
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default how to seperate cells with equal blank cells

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,


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default how to seperate cells with equal blank cells

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,



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default how to seperate cells with equal blank cells

How about another macro?

You could call it at the end of your Sub InsertRow_At_Change() macro.

Just add the line Fill_Blanks above End Sub in that macro.

End With

Fill_Blanks
End Sub


Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
col = ActiveCell.Column
'or
'col = .range("b1").column

Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.NumberFormat = "General"
rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With

End With

End Sub

On Thu, 10 May 2007 09:21:03 -0700, 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,




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default how to seperate cells with equal blank cells

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,





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default how to seperate cells with equal blank cells

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,



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default how to seperate cells with equal blank cells

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,




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default how to seperate cells with equal blank cells

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,





  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default how to seperate cells with equal blank cells

Hello Gord,

I have gone through the instruction of PivotTable and could not figure out
how to restructure my data. I have copied the original data and the formate
that i need to run a statistic program. Could you please tell me more
specifically how to do that? Thanks for your time.

The following is the original data from Datastream.
AEOLUS TYRE 'A' AEROSPACE COMMS.HLDG.GP 'A' AEROSPACE INFORMATION
'A' AEROSUN 'A' #ERROR ALONG TIBET 'A'

1990 $$"ER", 2361, NO DATA
1991
1992
1993
1994 1.87
1995 1.4
1996 3.28
1997 5.18 3.06
1998 4.77 3.93
1999 4.9 3.51
2000 13.19 5.9
2001 16.19 9.47
2002 8.86 9.36 7.78
2003 6.3 7.5 8.95
2004 8.3 6.1 15.17 5.36 4.89
2005 6.92 2.87 15.04 4.58 1.69
2006 4.6 4.21 30.41 8.64 1.43

The following is the formate that i need.

Year Price
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004 8.3
2005 6.92
2006 4.6
1990
1991
1992
1993
1994 1.87
1995 1.4
1996 3.28
1997 5.18
1998 4.77
1999 4.9
2000 13.19
2001 16.19
2002 8.86
2003 6.3
2004 6.1
2005 2.87
2006 4.21
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004 15.17
2005 15.04
2006 30.41

Have a nice weekend.

Yaling

"Gord Dibben" wrote:

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,






  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default how to seperate cells with equal blank cells

Pivot Tables are not something I use or deal with much.

Consequently my expertise in that is very limited.

I would think that Debra's site should give all you need for instruction if you
dig through it but not sure.

Maybe someone else will jump in with some "real" suggestion.

Let's wait to see.


Gord


On Sun, 13 May 2007 10:40:01 -0700, yaling
wrote:

Hello Gord,

I have gone through the instruction of PivotTable and could not figure out
how to restructure my data. I have copied the original data and the formate
that i need to run a statistic program. Could you please tell me more
specifically how to do that? Thanks for your time.

The following is the original data from Datastream.
AEOLUS TYRE 'A' AEROSPACE COMMS.HLDG.GP 'A' AEROSPACE INFORMATION
'A' AEROSUN 'A' #ERROR ALONG TIBET 'A'

1990 $$"ER", 2361, NO DATA
1991
1992
1993
1994 1.87
1995 1.4
1996 3.28
1997 5.18 3.06
1998 4.77 3.93
1999 4.9 3.51
2000 13.19 5.9
2001 16.19 9.47
2002 8.86 9.36 7.78
2003 6.3 7.5 8.95
2004 8.3 6.1 15.17 5.36 4.89
2005 6.92 2.87 15.04 4.58 1.69
2006 4.6 4.21 30.41 8.64 1.43

The following is the formate that i need.

Year Price
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004 8.3
2005 6.92
2006 4.6
1990
1991
1992
1993
1994 1.87
1995 1.4
1996 3.28
1997 5.18
1998 4.77
1999 4.9
2000 13.19
2001 16.19
2002 8.86
2003 6.3
2004 6.1
2005 2.87
2006 4.21
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004 15.17
2005 15.04
2006 30.41

Have a nice weekend.

Yaling

"Gord Dibben" wrote:

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,







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
Formula to find equal values with blank cells hilltop55 Excel Discussion (Misc queries) 5 March 22nd 07 10:12 PM
How to seperate 2 values to 2 different cells nander Excel Discussion (Misc queries) 2 April 27th 06 08:06 PM
Excel inserted 0's in cells linked to blank cells lburg801 Excel Discussion (Misc queries) 5 October 28th 05 11:32 PM
How do I skip blank cells when copying over a range of cells? tawells Excel Discussion (Misc queries) 2 June 7th 05 09:36 PM
Seperate First and Last Name into different cells Sam555 Excel Worksheet Functions 5 March 11th 05 07:17 AM


All times are GMT +1. The time now is 02:55 PM.

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

About Us

"It's about Microsoft Excel"