Concatenating a series of cells

  • Thread starter Thread starter erikd
  • Start date Start date
E

erikd

I'm not sure where to start with this. I am currently accomplishing
this by a cut & paste from Excel into an editor, formatting the data
with an editor macro and cutting & paste back into Excel.

I have approximately 3,000 rows of data and need to place one of the
columns (a 9 character alpha numeric word) into a line of 75 words
separated by a special character. So I have what looks like this:
1abcdefg
2abcdefgh
3abcdefgh

and I need it into this form:

1abcdefgh%2abcdefgh%3abcdefgh

The reason for doing this is to take an employee identifier and import
it on a weekly basis for automated reporting. The 3,000 lines of data
are slightly dynamic so it's not always 3,000 lines. The preferred
number of words per line is anywhere between 75-100 words per line.

Although I can continue to use my editor to do it, it would be great
if I can do it all in Excel and give the spreadsheet to a co-worker to
do it when I'm out of the office. A series of concatenate formulas
with a copy and paste special isn't the best solution since it really
bloats the file. Is a macro the answer?

Erik
 
How about something like:

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim myStr As String
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long
Dim myChar As String
Dim myCol As String

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

myStep = 75
myChar = "%"
oRow = 0
myCol = "A"

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, myCol).End(xlUp).Row
myStr = ""
For iRow = FirstRow To LastRow
If (iRow Mod myStep) = 0 Then
oRow = oRow + 1
newWks.Cells(oRow, myCol).Value = Mid(myStr, 2)
myStr = ""
Else
myStr = myStr & "%" & .Cells(iRow, myCol).Value
End If
Next iRow

If myStr = "" Then
'do nothing more
Else
oRow = oRow + 1
newWks.Cells(oRow, myCol).Value = myStr
myStr = ""
End If

End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Thanks for the pointer to the intro material on macro's Dave. Your
assumption was correct, I'm an absolute newby with macros.

I'm going to have to do some studying and start using macros, the
sample you gave shows me that I've been doing some things in Excel
pretty inefficiently. I started to do some reading and I'm not sure
where the best place is to start. There's a lot of material on the web
regarding macro's but I haven't found the right beginners page. The
help file isn't ideal for my current level of expertise.

I ran the macro and it skips every 75th value. I started to read the
reference you sent, but it's going to take awhile for me to get enough
up to speed to find the bug. The other thing the macro does is places
a % at the beginning of the final row of output. I'm not sure if it's
related or if it's a second bug. Any chance I can ask you to take a
second look at the macro and see where it goes wrong?

Erik
 
Oops. Slight bug in my code:

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim myStr As String
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long
Dim myChar As String
Dim myCol As String

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

myStep = 75
myChar = "%"
oRow = 0
myCol = "A"

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, myCol).End(xlUp).Row
myStr = ""
For iRow = FirstRow To LastRow
myStr = myStr & "%" & .Cells(iRow, myCol).Value
If (iRow Mod myStep) = 0 Then
oRow = oRow + 1
newWks.Cells(oRow, myCol).Value = Mid(myStr, 2)
myStr = ""
End If
Next iRow

If myStr = "" Then
'do nothing more
Else
oRow = oRow + 1
newWks.Cells(oRow, myCol).Value = myStr
myStr = ""
End If

End With
End Sub

If you're interested in books--nice to read in front of the tv:

For excel books, Debra Dalgleish has a big list of books at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. John Green (and others) is nice,
too. See if you can find them in your local bookstore and you can choose what
one you like best.

And if you check David McRitchie's site, you'll see a page with a bunch of
links:

http://www.mvps.org/dmcritchie/excel/excel.htm
look for: Excel Lessons & Tutorials (#tutorials)
 
Back
Top