VBA to merge similar columns by heading name in a table

[randal80]

Hi, in the attached I have an export from JIRA that has some repeated columns label, label2, label3 and Comment, Comment4, Comment5 these two Label and Comment can be bigger that just 3 examples but the current export happens to just have 3 of each.

I have some code already that will merge the data of the comments with a line break into a single cell after selecting the columns that are similar:

[/COLOR][COLOR=#333333]Sub JoinAndMerge()[/COLOR]
Dim outputText As String

Dim Rw As Range

Dim cell As Range



delim = vbCrLf

Application.ScreenUpdating = False

For Each Rw In Selection.Rows

    For Each cell In Rw.Cells

        outputText = outputText & cell.Value & delim

    Next cell

    With Rw

    .Clear

    .Cells(1).Value = outputText

    .Merge

    .HorizontalAlignment = xlGeneral

    .VerticalAlignment = xlCenter

    .WrapText = True

    End With

    outputText = ""

Next Rw

Application.ScreenUpdating = True
 [COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

I could do this for labels as well as it is based on a manual selection, however I would like to automate this.

I also have the following that creates a table of the exported data and hides columns we don't care about for the purpose of presenting this data, but we don't want to lose hence only hiding columns.

[/COLOR]

    Dim sht As Worksheet

    Dim LastRow As Long

    Dim LastColumn As Long

    Dim rng As Range

'Find range edges

Sub RangeFinder()



    Set sht = Sheets("Export Team Test")

        

    LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

        

    LastRow = sht.Range("A1").CurrentRegion.Rows.Count



End Sub

'Naming a range

Sub sbCreateTable()

    

    'get range for table with range finder

    RangeFinder

    

    'Then Create Table in Excel with VBA

    With sht

        sht.ListObjects.Add(xlSrcRange, Range(.Cells(1, 1), .Cells(LastRow, LastColumn)), , xlYes).Name = "myTable1"

    End With

    
 [COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

I have some basic idea of working with tables but I cant get into my head how to search for the headers that start Labels and get their numbers to then select and use the merge code I already have on them. then repeat with the comments ones.
I will try and work out the pasting excel intot he forum and update below with an example of the data from JIRA.

 

[randal80]

[TABLE="width: 1000"]
<tbody>[TR]
[TD][TABLE="width: 12754"]
<tbody>[TR]
[TD]Summary[/TD]
[TD]Issue key[/TD]
[TD]Issue id[/TD]
[TD]Issue Type[/TD]
[TD]Status[/TD]
[TD]Project key[/TD]
[TD]Project name[/TD]
[TD]Project type[/TD]
[TD]Project lead[/TD]
[TD]Project description[/TD]
[TD]Project url[/TD]
[TD]Priority[/TD]
[TD]Resolution[/TD]
[TD]Assignee[/TD]
[TD]Reporter[/TD]
[TD]Creator[/TD]
[TD]Created[/TD]
[TD]Updated[/TD]
[TD]Last Viewed[/TD]
[TD]Resolved[/TD]
[TD]Due Date[/TD]
[TD]Votes[/TD]
[TD]Labels[/TD]
[TD]Labels[/TD]
[TD]Labels[/TD]
[TD]Description[/TD]
[TD]Environment[/TD]
[TD]Watchers[/TD]
[TD]Original Estimate[/TD]
[TD]Remaining Estimate[/TD]
[TD]Time Spent[/TD]
[TD]Work Ratio[/TD]
[TD]Σ Original Estimate[/TD]
[TD]Σ Remaining Estimate[/TD]
[TD]Σ Time Spent[/TD]
[TD]Security Level[/TD]
[TD]Outward issue link (Relates)[/TD]
[TD]Custom field (Change completion date)[/TD]
[TD]Custom field (Change reason)[/TD]
[TD]Custom field (Change risk)[/TD]
[TD]Custom field (Change start date)[/TD]
[TD]Custom field (Change type)[/TD]
[TD]Custom field (Development)[/TD]
[TD]Custom field (Epic Color)[/TD]
[TD]Custom field (Epic Link)[/TD]
[TD]Custom field (Epic Name)[/TD]
[TD]Custom field (Epic Status)[/TD]
[TD]Custom field (Impact)[/TD]
[TD]Custom field (Investigation reason)[/TD]
[TD]Custom field (Operational categorization)[/TD]
[TD]Custom field (Pending reason)[/TD]
[TD]Custom field (Product categorization)[/TD]
[TD]Custom field (Raised during)[/TD]
[TD]Custom field (Rank)[/TD]
[TD]Custom field (Request Type)[/TD]
[TD]Custom field (Request language)[/TD]
[TD]Custom field (Request participants)[/TD]
[TD]Custom field (Root cause)[/TD]
[TD]Satisfaction rating[/TD]
[TD]Custom field (Source)[/TD]
[TD]Custom field (Story Points)[/TD]
[TD]Custom field (Test sessions)[/TD]
[TD]Custom field (Testing status)[/TD]
[TD]Custom field (Urgency)[/TD]
[TD]Custom field (Workaround)[/TD]
[TD]Custom field ([CHART] Date of First Response)[/TD]
[TD]Comment[/TD]
[TD]Comment[/TD]
[TD]Comment[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 12754"]
<tbody>[TR]
[TD="class: xl65, width: 296"]CUSTOMER_PNL_StandupEmbedder_Input[/TD]
[TD="class: xl65, width: 72"]CUS-102[/TD]
[TD="class: xl65, width: 64, align: right"]12226[/TD]
[TD="class: xl65, width: 81"]Task[/TD]
[TD="class: xl65, width: 84"]In Progress[/TD]
[TD="class: xl65, width: 80"]CUS[/TD]
[TD="class: xl65, width: 96"]Customer[/TD]
[TD="class: xl65, width: 86"]software[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 137"][/TD]
[TD="class: xl65, width: 76"][/TD]
[TD="class: xl65, width: 61"]Highest[/TD]
[TD="class: xl65, width: 84"][/TD]
[TD="class: xl65, width: 89"][/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl66, width: 120, align: right"]26/10/2017 18:32[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 12:37[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 17:07[/TD]
[TD="class: xl65, width: 120"][/TD]
[TD="class: xl65, width: 72"][/TD]
[TD="class: xl65, width: 47, align: right"]0[/TD]
[TD="class: xl65, width: 54"]PNL[/TD]
[TD="class: xl65, width: 54"][/TD]
[TD="class: xl65, width: 54"][/TD]
[TD="class: xl65, width: 87"][/TD]
[TD="class: xl65, width: 94"][/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 129"][/TD]
[TD="class: xl65, width: 149"][/TD]
[TD="class: xl65, width: 85"][/TD]
[TD="class: xl65, width: 84"][/TD]
[TD="class: xl65, width: 146"][/TD]
[TD="class: xl65, width: 166"][/TD]
[TD="class: xl65, width: 102"][/TD]
[TD="class: xl65, width: 101"][/TD]
[TD="class: xl65, width: 209"][/TD]
[TD="class: xl65, width: 282"][/TD]
[TD="class: xl65, width: 215"][/TD]
[TD="class: xl65, width: 191"][/TD]
[TD="class: xl65, width: 235"][/TD]
[TD="class: xl65, width: 196"][/TD]
[TD="class: xl65, width: 205"]{}[/TD]
[TD="class: xl65, width: 179"][/TD]
[TD="class: xl65, width: 171"][/TD]
[TD="class: xl65, width: 184"][/TD]
[TD="class: xl65, width: 186"][/TD]
[TD="class: xl65, width: 157"][/TD]
[TD="class: xl65, width: 257"][/TD]
[TD="class: xl65, width: 301"][/TD]
[TD="class: xl65, width: 220"][/TD]
[TD="class: xl65, width: 267"][/TD]
[TD="class: xl65, width: 209"][/TD]
[TD="class: xl65, width: 145"]0|i001v3:[/TD]
[TD="class: xl65, width: 205"][/TD]
[TD="class: xl65, width: 239"][/TD]
[TD="class: xl65, width: 259"][/TD]
[TD="class: xl65, width: 187"][/TD]
[TD="class: xl65, width: 136"][/TD]
[TD="class: xl65, width: 156"][/TD]
[TD="class: xl65, width: 191"][/TD]
[TD="class: xl65, width: 207"][/TD]
[TD="class: xl65, width: 210"][/TD]
[TD="class: xl65, width: 165"][/TD]
[TD="class: xl65, width: 197"][/TD]
[TD="class: xl67, width: 330, align: right"]00:00.0[/TD]
[TD="class: xl68, width: 250"]26/Jan/17 6:00 PM;usename;[19/10] - In progress

[25/10] - Suggest remove NY_OutgoingLines from the deliverables title. PNL ref is 159 Standup Embedder Inputs (changed from PNL_165)[/TD]
[TD="class: xl65, width: 857"]31/Oct/17 12:37 PM;first.second;[26/10] - Panel partly deployed, further embedder control needs configuration and calrec[/TD]
[TD="class: xl65, width: 1790"][/TD]
[/TR]
</tbody>[/TABLE]

<tbody>
[TD="class: xl65"]CUSTOMER_PNL_Packager_XYGrid[/TD]
[TD="class: xl65"]CUS-97[/TD]
[TD="class: xl65, width: 64, align: right"]12221[/TD]
[TD="class: xl65, width: 81"]Task[/TD]
[TD="class: xl65, width: 84"]Done[/TD]
[TD="class: xl65, width: 80"]CUS[/TD]
[TD="class: xl65, width: 96"]Customer[/TD]
[TD="class: xl65, width: 86"]software[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 137"][/TD]
[TD="class: xl65, width: 76"][/TD]
[TD="class: xl65, width: 61"]Highest[/TD]
[TD="class: xl65, width: 84"]Done[/TD]
[TD="class: xl65, width: 89"][/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl66, width: 120, align: right"]26/10/2017 18:32[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 12:11[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 12:11[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 12:11[/TD]
[TD="class: xl65, width: 72"][/TD]
[TD="class: xl65, width: 47, align: right"]0[/TD]
[TD="class: xl65, width: 54"]PNL[/TD]
[TD="class: xl65, width: 54"][/TD]
[TD="class: xl65, width: 54"][/TD]
[TD="class: xl65, width: 87"][/TD]
[TD="class: xl65, width: 94"][/TD]
[TD="class: xl65, width: 89"][/TD]
[TD="class: xl65, width: 129"][/TD]
[TD="class: xl65, width: 149"][/TD]
[TD="class: xl65, width: 85"][/TD]
[TD="class: xl65, width: 84"][/TD]
[TD="class: xl65, width: 146"][/TD]
[TD="class: xl65, width: 166"][/TD]
[TD="class: xl65, width: 102"][/TD]
[TD="class: xl65, width: 101"][/TD]
[TD="class: xl65, width: 209"][/TD]
[TD="class: xl65, width: 282"][/TD]
[TD="class: xl65, width: 215"][/TD]
[TD="class: xl65, width: 191"][/TD]
[TD="class: xl65, width: 235"][/TD]
[TD="class: xl65, width: 196"][/TD]
[TD="class: xl65, width: 205"]{}[/TD]
[TD="class: xl65, width: 179"][/TD]
[TD="class: xl65, width: 171"][/TD]
[TD="class: xl65, width: 184"][/TD]
[TD="class: xl65, width: 186"][/TD]
[TD="class: xl65, width: 157"][/TD]
[TD="class: xl65, width: 257"][/TD]
[TD="class: xl65, width: 301"][/TD]
[TD="class: xl65, width: 220"][/TD]
[TD="class: xl65, width: 267"][/TD]
[TD="class: xl65, width: 209"][/TD]
[TD="class: xl65, width: 145"]0|i001vz:[/TD]
[TD="class: xl65, width: 205"][/TD]
[TD="class: xl65, width: 239"][/TD]
[TD="class: xl65, width: 259"][/TD]
[TD="class: xl65, width: 187"][/TD]
[TD="class: xl65, width: 136"][/TD]
[TD="class: xl65, width: 156"][/TD]
[TD="class: xl65, width: 191"][/TD]
[TD="class: xl65, width: 207"][/TD]
[TD="class: xl65, width: 210"][/TD]
[TD="class: xl65, width: 165"][/TD]
[TD="class: xl65, width: 197"][/TD]
[TD="class: xl67, width: 330, align: right"]00:00.0[/TD]
[TD="class: xl68, width: 250"]26/Jan/17 6:00 PM;usename;[18/08] - Deployed and ready for testing

[19/10] - Complete

[20/10] - Grid in use[/TD]
[TD="class: xl65, width: 857"][/TD]
[TD="class: xl65, width: 1790"][/TD]

</tbody>

[TABLE="width: 12754"]
<tbody>[TR]
[TD="class: xl65, width: 296"]CUSTOMER_PNL_MonitorStack_MCR[/TD]
[TD="class: xl65, width: 72"]CUS-167[/TD]
[TD="class: xl65, width: 64, align: right"]12291[/TD]
[TD="class: xl65, width: 81"]Task[/TD]
[TD="class: xl65, width: 84"]In Progress[/TD]
[TD="class: xl65, width: 80"]BB[/TD]
[TD="class: xl65, width: 96"]Customer[/TD]
[TD="class: xl65, width: 86"]software[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 137"][/TD]
[TD="class: xl65, width: 76"][/TD]
[TD="class: xl65, width: 61"]Low[/TD]
[TD="class: xl65, width: 84"][/TD]
[TD="class: xl65, width: 89"][/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl66, width: 120, align: right"]26/10/2017 18:33[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 12:08[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 17:09[/TD]
[TD="class: xl65, width: 120"][/TD]
[TD="class: xl65, width: 72"][/TD]
[TD="class: xl65, width: 47, align: right"]0[/TD]
[TD="class: xl65, width: 54"]PNL[/TD]
[TD="class: xl65, width: 54"][/TD]
[TD="class: xl65, width: 54"][/TD]
[TD="class: xl65, width: 87"][/TD]
[TD="class: xl65, width: 94"][/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 129"][/TD]
[TD="class: xl65, width: 149"][/TD]
[TD="class: xl65, width: 85"][/TD]
[TD="class: xl65, width: 84"][/TD]
[TD="class: xl65, width: 146"][/TD]
[TD="class: xl65, width: 166"][/TD]
[TD="class: xl65, width: 102"][/TD]
[TD="class: xl65, width: 101"][/TD]
[TD="class: xl65, width: 209"][/TD]
[TD="class: xl65, width: 282"][/TD]
[TD="class: xl65, width: 215"][/TD]
[TD="class: xl65, width: 191"][/TD]
[TD="class: xl65, width: 235"][/TD]
[TD="class: xl65, width: 196"][/TD]
[TD="class: xl65, width: 205"]{}[/TD]
[TD="class: xl65, width: 179"][/TD]
[TD="class: xl65, width: 171"][/TD]
[TD="class: xl65, width: 184"][/TD]
[TD="class: xl65, width: 186"][/TD]
[TD="class: xl65, width: 157"][/TD]
[TD="class: xl65, width: 257"][/TD]
[TD="class: xl65, width: 301"][/TD]
[TD="class: xl65, width: 220"][/TD]
[TD="class: xl65, width: 267"][/TD]
[TD="class: xl65, width: 209"][/TD]
[TD="class: xl65, width: 145"]0|i0016f:[/TD]
[TD="class: xl65, width: 205"][/TD]
[TD="class: xl65, width: 239"][/TD]
[TD="class: xl65, width: 259"][/TD]
[TD="class: xl65, width: 187"][/TD]
[TD="class: xl65, width: 136"][/TD]
[TD="class: xl65, width: 156"][/TD]
[TD="class: xl65, width: 191"][/TD]
[TD="class: xl65, width: 207"][/TD]
[TD="class: xl65, width: 210"][/TD]
[TD="class: xl65, width: 165"][/TD]
[TD="class: xl65, width: 197"][/TD]
[TD="class: xl67, width: 330, align: right"]00:00.0[/TD]
[TD="class: xl68, width: 250"]26/Jan/17 6:00 PM;username;[11/08] - Simple version for commissioning UMDs. Needs roll and geometry finalised.

[12/10] - Geometry will be updated after site visit

[19/10] - Added geometry and routes which are working. Layout recall still needs roll driver to be setup configured.[/TD]
[TD="class: xl65, width: 857"]31/Oct/17 12:08 PM;first.second;[25/10] - Priority set to low[/TD]
[TD="class: xl65, width: 1790"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 12754"]
<tbody>[TR]
[TD="class: xl65, width: 296"]CUSTOMER_DVR_SAM_Kah[/TD]
[TD="class: xl65, width: 72"]CUS-45[/TD]
[TD="class: xl65, width: 64, align: right"]12169[/TD]
[TD="class: xl65, width: 81"]Task[/TD]
[TD="class: xl65, width: 84"]In Progress[/TD]
[TD="class: xl65, width: 80"]BB[/TD]
[TD="class: xl65, width: 96"]Customer[/TD]
[TD="class: xl65, width: 86"]software[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 137"][/TD]
[TD="class: xl65, width: 76"][/TD]
[TD="class: xl65, width: 61"]Low[/TD]
[TD="class: xl65, width: 84"][/TD]
[TD="class: xl65, width: 89"]first second[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl66, width: 120, align: right"]26/10/2017 18:32[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 08:49[/TD]
[TD="class: xl66, width: 120, align: right"]31/10/2017 17:09[/TD]
[TD="class: xl65, width: 120"][/TD]
[TD="class: xl65, width: 72"][/TD]
[TD="class: xl65, width: 47, align: right"]0[/TD]
[TD="class: xl65, width: 54"]DVR[/TD]
[TD="class: xl65, width: 54"][/TD]
[TD="class: xl65, width: 54"][/TD]
[TD="class: xl65, width: 87"][/TD]
[TD="class: xl65, width: 94"][/TD]
[TD="class: xl65, width: 89"]first.second[/TD]
[TD="class: xl65, width: 129, align: right"]201600[/TD]
[TD="class: xl65, width: 149, align: right"]201600[/TD]
[TD="class: xl65, width: 85"][/TD]
[TD="class: xl69, width: 84, align: right"]0%[/TD]
[TD="class: xl65, width: 146, align: right"]201600[/TD]
[TD="class: xl65, width: 166, align: right"]201600[/TD]
[TD="class: xl65, width: 102"][/TD]
[TD="class: xl65, width: 101"][/TD]
[TD="class: xl65, width: 209"][/TD]
[TD="class: xl65, width: 282"][/TD]
[TD="class: xl65, width: 215"][/TD]
[TD="class: xl65, width: 191"][/TD]
[TD="class: xl65, width: 235"][/TD]
[TD="class: xl65, width: 196"][/TD]
[TD="class: xl65, width: 205"]{}[/TD]
[TD="class: xl65, width: 179"][/TD]
[TD="class: xl65, width: 171"][/TD]
[TD="class: xl65, width: 184"][/TD]
[TD="class: xl65, width: 186"][/TD]
[TD="class: xl65, width: 157"][/TD]
[TD="class: xl65, width: 257"][/TD]
[TD="class: xl65, width: 301"][/TD]
[TD="class: xl65, width: 220"][/TD]
[TD="class: xl65, width: 267"][/TD]
[TD="class: xl65, width: 209"][/TD]
[TD="class: xl65, width: 145"]0|i001c7:[/TD]
[TD="class: xl65, width: 205"][/TD]
[TD="class: xl65, width: 239"][/TD]
[TD="class: xl65, width: 259"][/TD]
[TD="class: xl65, width: 187"][/TD]
[TD="class: xl65, width: 136"][/TD]
[TD="class: xl65, width: 156"][/TD]
[TD="class: xl65, width: 191"][/TD]
[TD="class: xl65, width: 207"][/TD]
[TD="class: xl65, width: 210"][/TD]
[TD="class: xl65, width: 165"][/TD]
[TD="class: xl65, width: 197"][/TD]
[TD="class: xl67, width: 330, align: right"]00:00.0[/TD]
[TD="class: xl68, width: 250"]26/Jan/17 6:00 PM;user;[11/08] - Requested visit to the manufacture- still outstanding. Simple version was tested on site to make routes on the SAM mixer was unsuccessful as mixer was not correctly set up with the correct protocol. Assisted client in installing the protocol but still unable to control via software or the SAM application (COM).
[30/08 ] - Issued raised with the manufacturer through client. There are 2 drivers as part of this deliverable- control and tally.
[14/09] - client advised Company that SAM have provided another protocol for Software team to investigate.
[12/10] - Software investigate the latest changes by SAM; mixer refuses connection. Further investigation is recommended
[19/10] - Work in progress however a visit to site required with SAM and Company. client to organise this as soon as possible.[/TD]
[TD="class: xl68, width: 857"]30/Oct/17 5:18 PM;first.second;[26/10] - Company added protocols andfurther investigation intended 26th October

[27/10] - Investigation provided greater clarity with the interface. Some concerns remaining with SAM.[/TD]
[TD="class: xl65, width: 1790"]31/Oct/17 8:48 AM;first second;[31/10] Further investigations have shown that Software can and does obtain data from the mixer desks. Logs gathered provide clarity on the messaging process. A single driver will be developed to handle both mixer tallies and aux bus routing. Work ongoing.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]