ExcelBanter - Macro to concatenate multiple rows in a column, without selecting each group manually

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro to concatenate multiple rows in a column, without selecting each group manually (https://www.excelbanter.com/excel-programming/364524-macro-concatenate-multiple-rows-column-without-selecting-each-group-manually.html)

Macro to concatenate multiple rows in a column, without selecting each group manually

I have a list of methods for the analysis of water quality parameters,
each with a long detailed description. Somewhere before this data made
its way to me, the long descriptions were parsed into multiple cells in
the same column, and numbered in the adjacent column to the left to
indicate the order in which they would have to be put back together.
These groups range from 2 to 30 cells. I have about 14,500 records to
go through and have been trying to build a macro, either in Excel of
Acess, that will recognize that when this number goes back to 1, all
cells in the column to the right of it (description column) need to be
concatenated, up until the next 1 is reached. Each cluster has a unique
ID as well, sequentially increasing in increments of 1. This could be
another option for the macro: whenever this number increases, the
concatenation begins, then ends before the next increase. I have
limited experience with VBA and am having trouble putting this
together.

Anyone have any ideas?

--
MikeM
------------------------------------------------------------------------
MikeM's Profile: http://www.excelforum.com/member.php...o&userid=35502
View this thread: http://www.excelforum.com/showthread...hreadid=552694

Macro to concatenate multiple rows in a column, without selecting each group manually

Sure...here's some sample data in a zip file:

+-------------------------------------------------------------------+
|Filename: SampleData.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4898 |
+-------------------------------------------------------------------+

--
MikeM
------------------------------------------------------------------------
MikeM's Profile: http://www.excelforum.com/member.php...o&userid=35502
View this thread: http://www.excelforum.com/showthread...hreadid=552694

Macro to concatenate multiple rows in a column, without selecting each group manually

sorry we don't get those or other files in the ms newsgroups. Care to give a
full and detailed explanation if you desire assistance.

--
Don Guillett
SalesAid Software

"MikeM" wrote in
message ...

Sure...here's some sample data in a zip file:

+-------------------------------------------------------------------+
|Filename: SampleData.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4898 |
+-------------------------------------------------------------------+

--
MikeM
------------------------------------------------------------------------
MikeM's Profile:
http://www.excelforum.com/member.php...o&userid=35502
View this thread: http://www.excelforum.com/showthread...hreadid=552694

Macro to concatenate multiple rows in a column, without selecting each group manually

Mike, here a little bit of code you can try that will get you the results
your looking for. If you have any questions please post me back. Please
note changes where needed,, ie sheet name and etc.

hth Rick (Fairbanks, AK {Land of the Midnight Sun})

=================================================
Sub DeleteUnWantedRows()
Dim ws4 As Worksheet
Dim lrow As Long, cnt As Long
Dim test As Integer

Application.ScreenUpdating = False
Set ws4 = Worksheets("Sheet4")

lrow = ws4.Range("A2").End(xlDown).Row
cnt = lrow
Do
test = ws4.Cells(lrow, 1)
Do
cnt = cnt - 1
If (test = ws4.Cells(cnt, 1)) Then
ws4.Cells(lrow, 1).EntireRow.Delete
lrow = lrow - 1
Else
lrow = lrow - 1
Exit Do
End If
Loop
Loop While (cnt 2)

End Sub

============================================

"MikeM" wrote in
message ...

Sure...here's some sample data in a zip file:

+-------------------------------------------------------------------+
|Filename: SampleData.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4898 |
+-------------------------------------------------------------------+

--
MikeM
------------------------------------------------------------------------
MikeM's Profile:
http://www.excelforum.com/member.php...o&userid=35502
View this thread: http://www.excelforum.com/showthread...hreadid=552694

Macro to concatenate multiple rows in a column, without selecting each group manually

Morning Mike,
Please disregaurd the first code I send you early this morning. I failed
to put in the the concatenation of the "Description field" in the code last
night. Now the macro gives the same results as your examples. Sorry about
the mistake. I guess thats what I get trying to write code after getting off
a 10 hour shift at work. If have any questions on the code, please post me
back. (See new attached marco code)

enjoy, Rick (Fkbs, Ak)

PS: Note I copied your before data to Sheet4 to run this code, if wondering
why I used sheet4.
============================================
Option Explicit

Sub DeleteUnWantedRows2()
Dim ws4 As Worksheet
Dim lrow As Long, cnt As Long
Dim test As Integer
Dim hold As String

Application.ScreenUpdating = False
Set ws4 = Worksheets("Sheet4")

lrow = ws4.Range("A2").End(xlDown).Row
cnt = lrow
Do
test = ws4.Cells(lrow, 1)
hold = ""
Do
cnt = cnt - 1
If (test = ws4.Cells(cnt, 1)) Then
If hold = "" Then
hold = ws4.Cells(lrow, 4)
Else
hold = ws4.Cells(lrow, 4) & " " & hold
End If
ws4.Cells(lrow, 1).EntireRow.Delete
lrow = lrow - 1
Else
ws4.Cells(lrow, 4) = ws4.Cells(lrow, 4) & " " & hold
lrow = lrow - 1
Exit Do
End If
Loop
Loop While (cnt 2)

End Sub

================================================

"Rick Hansen" wrote in message
...
Mike, here a little bit of code you can try that will get you the results
your looking for. If you have any questions please post me back. Please
note changes where needed,, ie sheet name and etc.

hth Rick (Fairbanks, AK {Land of the Midnight Sun})

=================================================
Sub DeleteUnWantedRows()
Dim ws4 As Worksheet
Dim lrow As Long, cnt As Long
Dim test As Integer

Application.ScreenUpdating = False
Set ws4 = Worksheets("Sheet4")

lrow = ws4.Range("A2").End(xlDown).Row
cnt = lrow
Do
test = ws4.Cells(lrow, 1)
Do
cnt = cnt - 1
If (test = ws4.Cells(cnt, 1)) Then
ws4.Cells(lrow, 1).EntireRow.Delete
lrow = lrow - 1
Else
lrow = lrow - 1
Exit Do
End If
Loop
Loop While (cnt 2)

End Sub

============================================

"MikeM" wrote in
message ...

Sure...here's some sample data in a zip file:

+-------------------------------------------------------------------+
|Filename: SampleData.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4898 |
+-------------------------------------------------------------------+

--
MikeM
------------------------------------------------------------------------
MikeM's Profile:
http://www.excelforum.com/member.php...o&userid=35502
View this thread:
http://www.excelforum.com/showthread...hreadid=552694

Macro to concatenate multiple rows in a column, without selecting each group manually

Worked like a charm!
Much appreciated Rick. Thanks a lot.

--
MikeM
------------------------------------------------------------------------
MikeM's Profile: http://www.excelforum.com/member.php...o&userid=35502
View this thread: http://www.excelforum.com/showthread...hreadid=552694

Macro to concatenate multiple rows in a column, without selecting each group manually

Mike, It was my pleasure. Have a great day

Rick (Fbks, AK)

"MikeM" wrote in
message ...

Worked like a charm!
Much appreciated Rick. Thanks a lot.

--
MikeM
------------------------------------------------------------------------
MikeM's Profile:
http://www.excelforum.com/member.php...o&userid=35502
View this thread: http://www.excelforum.com/showthread...hreadid=552694