copying made simplier

  • Thread starter Thread starter msam137
  • Start date Start date
M

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.
 
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)
 
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
 
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.
 
Back
Top