copying made simplier

[msam137]

I have a spread sheet that has a list of numbers and a company name.
The problem is all the numbers associated with name is in one cell. I
want to take all the numbers and give them thier indivdual cell and I
was wondering if there is away to make sure that name is easily copied
over to the cell without having to use the auto fill feature.

Thanks in advance.

 

[Nick Hodge]

Could we have a better example. If there is only one space in the entry for
example, or a comma between the number and Co Name you could use Data>Text
to columns, else there is probably a formula that could be devised.

Give us a few examples and check the examples are uniform or not

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Kramer]

This is probably not the most elegant way, but it works, if I'm
understanding the problem correctly. Working with a backup file, and
assuming company names are in column A, and telephone numbers are in
column B;

Replace all spaces in company names (column A) with a unique character,
such as the "|" (pipe) symbol. Save as a text file. Re-open the new
text file, as de-limited with tab and space. You should now have the
company names and the telephone numbers arranged in columns.

Company A Inc | 111-6543 | 111-7654 | 111-9765
Company B | 222-6543 | 222-7654 | 222-9765 | 222-5891 |

Change the "|" in column A back to space.

Run this routine to arrange into 2 columns, company name and one
telephone number.

Company A Inc | 111-6543 |
Company A Inc | 111-7654 |
Company A Inc | 111-9765 |
Company B | 222-6543 |
Company B | 222-7654 |
Company B | 222-9765 |
Company B | 222-5891 |


Sub RowToColumn()
Dim currRow As Integer, currCol As Integer, newRow As Integer
ActiveSheet.Select
currRow = 1: currCol = 3: newRow = 2
Do While Cells(currRow, 1).Text <> ""
Do While Cells(currRow, currCol).Text <> ""
Rows(newRow).Select
Selection.Insert Shift:=xlDown
Cells(newRow, 1) = Cells(currRow, 1).Text
Cells(newRow, 2) = Cells(currRow, currCol).Text
Cells(currRow, currCol) = ""
currCol = currCol + 1
newRow = newRow + 1
Loop
currRow = newRow: newRow = newRow + 1: currCol = 3
Loop
End Sub

good luck,

Dave

 

[Maurice Samueks]

First dave where do i type that code in. Second should i modify to
start in the cell that i want because it is not the first cell. Is
there a way to put the results of this statement in a new worksheet or
workbook. I am also trying to a figure out a way for me to display i
haven't had any luck ccan somebody give me their email address and i
will send you the data and the results.