Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find equal values with blank cells | Excel Discussion (Misc queries) | |||
How to seperate 2 values to 2 different cells | Excel Discussion (Misc queries) | |||
Excel inserted 0's in cells linked to blank cells | Excel Discussion (Misc queries) | |||
How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) | |||
Seperate First and Last Name into different cells | Excel Worksheet Functions |