Duplicate Names by factor of 4

[Guest]

I have an excel spreadsheet of 2000 names and account numbers. Currently, each row has l column of names, followed by l column of a discreet account number. I'm wish to take this data base of names and account numbers and automatically repeat the entries by a factor of 4 so that the end result is a list of 4 consecutive duplicate entries. Plus, I need to add quotes around each name and account number...with Account Number on row below Name, as follows in a Text File

Currently
John Smith 01234

Need
"John Smith
"012345
"John Smith
"012345
"John Smith
"012345
"John Smith"
"012345

How do I do this is easy to follow instructions. Many thanks for your detailed and quick response
Thank you. Lee

 

[Bob Phillips]

Hi Lee,

Here's a macro
Sub FormatTimes4()
Dim iLastRow As Long
Dim i As Long

Application.ScreenUpdating = False

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
Cells(i, "A").Offset(1, 0).Resize(7, 1).EntireRow.Insert
With Cells(i, "A")
.Value = Chr(34) & .Value & Chr(34)
.Offset(1, 0).Value = Chr(34) & .Offset(0, 1).Value & Chr(34)
.Offset(2, 0).Value = .Value
.Offset(3, 0).Value = Chr(34) & .Offset(0, 1).Value & Chr(34)
.Offset(4, 0).Value = .Value
.Offset(5, 0).Value = Chr(34) & .Offset(0, 1).Value & Chr(34)
.Offset(6, 0).Value = .Value
.Offset(7, 0).Value = Chr(34) & .Offset(0, 1).Value & Chr(34)
End With
Next i
Columns(2).ClearContents

Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have an excel spreadsheet of 2000 names and account numbers. Currently,

each row has l column of names, followed by l column of a discreet account
number. I'm wish to take this data base of names and account numbers and
automatically repeat the entries by a factor of 4 so that the end result is
a list of 4 consecutive duplicate entries. Plus, I need to add quotes
around each name and account number...with Account Number on row below Name,
as follows in a Text File.

 

[Bernie Deitrick]

Lee,

If your names start in cell A2, and the account numbers start in B2, try the
macro below, which will create a text file "Output.txt".

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim FileNum As Integer
Dim myCell As Range
Dim i As Integer
Dim myStr As String
FileNum = FreeFile()
Open "Output.txt" For Output Access Write As FileNum
For Each myCell In Range("A2", Range("A65536").End(xlUp))
For i = 1 To 4
Print #FileNum, """" & myCell.Text & """"
Print #FileNum, """" & myCell(1, 2).Text & """"
Next i
Next myCell
Close FileNum
End Sub

I have an excel spreadsheet of 2000 names and account numbers. Currently,

each row has l column of names, followed by l column of a discreet account
number. I'm wish to take this data base of names and account numbers and
automatically repeat the entries by a factor of 4 so that the end result is
a list of 4 consecutive duplicate entries. Plus, I need to add quotes
around each name and account number...with Account Number on row below Name,
as follows in a Text File.

 

[Bill]

A brute force method would be to
First: put a quote mark in a cell (Say E1)
if the names are in Column A and the numbers are in coumn
B and columns c and d are empty
second: put the following equation in C1
=$E$1 & A1 & $E$1
third: copy this cell and paste into cells C1 to D2000
fourth: insert new column C:
Fifth: Fill C1 to C2000 with 1 to 2000
Sixth: Copy the range C1:E2000
fifth: select C1:E8000 and paste special values only
sixth: select C1:E8000 and sort using column C
seventh: Fill C1 to C8000 with odd numbers from 1 to 15999
eighth: insert new column E and fill E1 to E8000 with even
numbers from 2 to 16000
ninth: move E1:F8000 to C800116000
tenth: sort C116000 by column C
I would finish up by deleting unwanted columns

-----Original Message-----
I have an excel spreadsheet of 2000 names and account

numbers. Currently, each row has l column of names,
followed by l column of a discreet account number. I'm
wish to take this data base of names and account numbers
and automatically repeat the entries by a factor of 4 so
that the end result is a list of 4 consecutive duplicate
entries. Plus, I need to add quotes around each name and
account number...with Account Number on row below Name, as
follows in a Text File.

Currently:
John Smith 012345

Need:
"John Smith"
"012345"
"John Smith"
"012345"
"John Smith"
"012345"
"John Smith"
"012345"


How do I do this is easy to follow instructions. Many

thanks for your detailed and quick response.

 

[AlfD]

Try this:

Put a string (eg abc) in A1
Put=""""&A1&"""" in B1
Read B1 - it should read "abc"

Note: count the " : there are 4 of them each side of the ampersands
There are no spaces

I guess you can build on this.

Al