how to seperate cells with equal blank cells

[yaling]

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,

 

[Gord Dibben]

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

 

[yaling]

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]

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.

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

 

[yaling]

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

 

[Gord Dibben]

You could enter them into your Tools>Autocorrect 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

 

[yaling]

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]

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

 

[yaling]

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]

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