CSV issues

J

Jason Sands

The AllTel QuickLink program will only take one number per line for
the Motorola W315. The second number must be moved to a different line
and the name also entered on that line. I have the .XLS file. There
are up to four numbers per name.

This:
Bailey Jeff 5012352651 5019490607
Baker Angie 5012352626 5019049681

needs to become this:
Bailey Jeff 5012352651
Bailey Jeff 5019490607
Baker Angie 5012352626
Baker Angie 5019049681

I greatly appreciate any help in sorting out these 300 names.
 
D

Dave Peterson

How about a macro:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim HowManyRows As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
'subtract name
HowManyRows = Application.CountA(.Rows(iRow)) - 1
NewWks.Cells(oRow, "A").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Resize(HowManyRows, 1).Value _
= Application.Transpose(.Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft)).Value)
oRow = oRow + HowManyRows
Next iRow
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
J

Jason Sands

Thanks for the information and for the help links.
I was wondering where some RTMs are :)
 
J

Jason Sands

Your macro works great!

I have [column last name], [column first name], numbers...
I tinkered with it but only managed to mess up more than fix it.
Why I always keep a good backup :)

Can you point me in the right direction?
 
D

Dave Peterson

First, it's usually better to describe your requirements with your initial
post. Makes less work for both you and responders:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim HowManyRows As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow

'subtract first name and lastname
HowManyRows = Application.CountA(.Rows(iRow)) - 2

NewWks.Cells(oRow, "A").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value

NewWks.Cells(oRow, "B").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "B").Value

NewWks.Cells(oRow, "C").Resize(HowManyRows, 1).Value _
= Application.Transpose(.Range(.Cells(iRow, "C"), _
.Cells(iRow, .Columns.Count).End(xlToLeft)).Value)

oRow = oRow + HowManyRows
Next iRow
End With

End Sub


Jason said:
Your macro works great!

I have [column last name], [column first name], numbers...
I tinkered with it but only managed to mess up more than fix it.
Why I always keep a good backup :)

Can you point me in the right direction?

How about a macro:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim HowManyRows As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow
'subtract name
HowManyRows = Application.CountA(.Rows(iRow)) - 1
NewWks.Cells(oRow, "A").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Resize(HowManyRows, 1).Value _
= Application.Transpose(.Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft)).Value)
oRow = oRow + HowManyRows
Next iRow
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
Click to expand...
Click to expand...
 
J

Jason Sands

That worked! It will take a long time for me to figure that out.
Working on it, though.
Thank you for time! I will be sure and make my statements clearer.


First, it's usually better to describe your requirements with your initial
post.  Makes less work for both you and responders:

Option Explicit
Sub testme()
    Dim CurWks As Worksheet
    Dim NewWks As Worksheet
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim iRow As Long
    Dim oRow As Long
    Dim HowManyRows As Long

    Set CurWks = Worksheets("Sheet1")
    Set NewWks = Worksheets.Add

    With CurWks
        FirstRow = 1
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        oRow = 1
        For iRow = FirstRow To LastRow

            'subtract first name and lastname
            HowManyRows = Application.CountA(.Rows(iRow)) -2

            NewWks.Cells(oRow, "A").Resize(HowManyRows, 1).Value _
                = .Cells(iRow, "A").Value

            NewWks.Cells(oRow, "B").Resize(HowManyRows, 1).Value _
                = .Cells(iRow, "B").Value

            NewWks.Cells(oRow, "C").Resize(HowManyRows, 1).Value _
                = Application.Transpose(.Range(.Cells(iRow, "C"), _
                    .Cells(iRow, .Columns.Count).End(xlToLeft)).Value)

            oRow = oRow + HowManyRows
        Next iRow
    End With

End Sub



Jason said:
Your macro works great!
Click to expand...
I have [column last name], [column first name], numbers...
I tinkered with it but only managed to mess up more than fix it.
Why I always keep a good backup :)
Click to expand...
Can you point me in the right direction?
Click to expand...
Click to expand...
 
D

Dave Peterson

It really doesn't do too much. The loop is where most of the work is done.
For iRow = FirstRow To LastRow

'subtract first name and lastname
HowManyRows = Application.CountA(.Rows(iRow)) - 2

NewWks.Cells(oRow, "A").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value

NewWks.Cells(oRow, "B").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "B").Value

NewWks.Cells(oRow, "C").Resize(HowManyRows, 1).Value _
= Application.Transpose(.Range(.Cells(iRow, "C"), _
.Cells(iRow, .Columns.Count).End(xlToLeft)).Value)

oRow = oRow + HowManyRows
Next iRow
Click to expand...

HowManyRows counts how many cells in column A:IV (in xl2003) have data. But it
subtracts 2 for the stuff in columns A:B (first and last name). Say you have
both names + 6 numbers. That means howmanyrows = 6.

NewWks.Cells(oRow, "A").Resize(HowManyRows, 1))
says to start in column A of the row in the new worksheet, but resize it to 6
(howmanyrows) by 1 column.

Then plop the last name in that range.

Then do the same thing in column B of the new worksheet.

And finally, it takes those 6 numbers (in columns c:h) and essentially does a
copy|paste special|transpose to the new worksheet.

Then it gets ready for the next row of the input worksheet by dropping down 6
(howmanyrows) rows.

Jason said:
That worked! It will take a long time for me to figure that out.
Working on it, though.
Thank you for time! I will be sure and make my statements clearer.

First, it's usually better to describe your requirements with your initial
post. Makes less work for both you and responders:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim HowManyRows As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
For iRow = FirstRow To LastRow

'subtract first name and lastname
HowManyRows = Application.CountA(.Rows(iRow)) - 2

NewWks.Cells(oRow, "A").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value

NewWks.Cells(oRow, "B").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "B").Value

NewWks.Cells(oRow, "C").Resize(HowManyRows, 1).Value _
= Application.Transpose(.Range(.Cells(iRow, "C"), _
.Cells(iRow, .Columns.Count).End(xlToLeft)).Value)

oRow = oRow + HowManyRows
Next iRow
End With

End Sub



Jason said:
Your macro works great!
Click to expand...
I have [column last name], [column first name], numbers...
I tinkered with it but only managed to mess up more than fix it.
Why I always keep a good backup :)
Click to expand...
Can you point me in the right direction?
Click to expand...
How about a macro:
Click to expand...
Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim HowManyRows As Long
Click to expand...
Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add
Click to expand...
With CurWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Click to expand...
oRow = 1
For iRow = FirstRow To LastRow
'subtract name
HowManyRows = Application.CountA(.Rows(iRow)) - 1
NewWks.Cells(oRow, "A").Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Resize(HowManyRows, 1).Value _
= Application.Transpose(.Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft)).Value)
oRow = oRow + HowManyRows
Next iRow
End With
Click to expand...
End Sub
Click to expand...
If you're new to macros:
Click to expand...
Debra Dalgleish has some notes how to implement macros here:http://www.contextures.com/xlvba01.html
Click to expand...
David McRitchie has an intro to macros:http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...
Ron de Bruin's intro to macros:http://www.rondebruin.nl/code.htm
Click to expand...
(General, Regular and Standard modules all describe the same thing.)
Click to expand...
Jason Sands wrote:
Click to expand...
The AllTel QuickLink program will only take one number per line for
the Motorola W315. The second number must be moved to a different line
and the name also entered on that line. I have the .XLS file. There
are up to four numbers per name.
Click to expand...
This:
Bailey Jeff 5012352651 5019490607
Baker Angie 5012352626 5019049681
Click to expand...
needs to become this:
Bailey Jeff 5012352651
Bailey Jeff 5019490607
Baker Angie 5012352626
Baker Angie 5019049681
Click to expand...
I greatly appreciate any help in sorting out these 300 names.

Dave Peterson
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

London Underground 8
Importing csv file 4
Excel Data Transfer Issue (strange situation) 1
Still looking for a solution for renaming worksheets 4
Convert a csv to xls prior to import 2
Import files into access 1
Newbie needs a direction 9
Renaming sheets - Performance problem 10

Top