Help requested to find VBA solution to build text string from two columns of data

[shstrating]

Win7 Pro, SP1; Excel 2016

I am trying to find a VBA solution to allow me to build a single text string from two columns of data.

The number of entries in the two columns is the same, but the number of rows of entries will vary.

The two columns of data will be manually copied as a range from a source worksheet to a destination worksheet in the same workbook.

The upper left cell into which the range is copied on the destination worksheet will be a constant (B3).

The resulting single text string should be “B3 – C3, B4 – C4, B5 – C5, and so on” until the end of the data is reached.

Here's a sample showing the results for the first 3 rows:

	B	C	D
Jet Black	111 - Jet Black, 114 - Folkstone Grey, 115 - Medium Grey
Folkstone Grey
Medium Grey
Soft Grey
Bright White
Pumice
Micro Grey
Fog
Micro Sand

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: center"]No. of rows[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]111[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]114[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]115[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]117[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]118[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]119[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]121[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]128[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]129[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

If I do this formulaically and manually, it looks like this:
=TEXTJOIN(", ",TRUE,TEXTJOIN(" - ",TRUE,B3:C3),TEXTJOIN(" - ",TRUE,B4:C4),TEXTJOIN(" - ",TRUE,B5:C5))

Problem with that is that the end user has to either first TEXTJOIN the contents of Cols B & C one row at a time in a helper column and then TEXTJOIN the resulting helper column into a blank cell, or they have to construct a formula similar to mine above to do it all in a blank cell. Either way it won’t get used if they have to jump through these hoops. Most of the end users are not skilled or patient enough in Excel for this.

If possible I want to automate it so that the only thing the end user has to do is Copy/Paste the range of data starting in cell B3 and then click a Command Button to have the text string built in cell D3.

I have almost no VBA knowledge/experience, so I have been scouring this group and the web for bits of code that I might be able to cobble together into a solution, but because I don’t know the VBA language (and I guess my search strings are crappy) I am hitting a brick wall.

The one thing I’ve been able to do is to find code that will find the last row of data in the range, and then use that value to determine the total number of rows of data, under the assumption that I will need this as part of a LOOP or FOR/NEXT or other counter of some kind.

Sub findLastCell()


    Dim rowcount As Long
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
        
    rowcount = lastRow - 2
    
    Range("C2") = rowcount
    
End Sub

Any help anyone can give me would be much appreciated.

Steve

 

[sstiebinger]

Use a table, put your data in columns 1 and 2, in column 3 use helper formula

=TEXTJOIN("-",TRUE,[@Column1],[@Column2])

Then for your output cell, use formula

=TEXTJOIN(",",TRUE,Table1[Column3])

Then users can just copy/paste into the table.
no code or macros required.

 

[sstiebinger]

https://dl.dropboxusercontent.com/u/5611192/Concat Example.xlsx

There's an example

 

[shstrating]

sstiebinger,
Thank you for that straightforward solution.
I will try it out on one or two end users and see what kind of feedback I get.


For the Forum at large,
I would still like to find a VBA solution for two reasons:
1) I'm not allowed to protect this workbook and I'm afraid that what will regularly happen is that end users will accidentally overwrite/erase cell formulas, and
2) I want to use this as a learning experience to increase my VBA knowledge.

Any VBA solutions to be had?

Thanks,
Steve