Combining cell values

[Barefoot]

Dear All

Would like some help on putting together some code please.

How can I join a set of values in cells in a column into 1 long string in a cell?
For example, my problem is - starting at H3 (H2 being a heading) I have a list of outstanding invoices (i.e. H3 = 70k H4 = 228k etc down to H?) and I would like to join these together separated by a comma into 1 string say in H1 (i.e. 70k, 228k, etc, etc, etc) to put into a report.

I have very basic knowledge of using arrays and examples I have found do not do what I need.

Thanks as ever.
Julian

 

[MarkAndrews]

Hi - You could use the Concatenate function (help topic available in Excel)

=concatenate(a1,a2,a3,a4,a5) etc

HTH

 

[patrickmuldoon99]

in a cell, simply put:

=A1 & "," & B1 & "," & C1 & "," & D1

where there are values in cells A1 through to D1

That should do it...

HTH

 

[Barefoot]

Hi Mark/Patrick

Thanks for that, I do know about concatenate but due to the number of invoices and that it changes each period I was looking to use VBA to automate.

Cheers
Julian

 

[Peter_SSs]

Hi Mark/Patrick

Thanks for that, I do know about concatenate but due to the number of invoices and that it changes each period I was looking to use VBA to automate.

Cheers
Julian

Something like this?<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ConcatenateInvoices()
    <SPAN style="color:#00007F">Dim</SPAN> Result<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
    
    LastRow = Range("H65536").End(xlUp).Row
    <SPAN style="color:#00007F">If</SPAN> LastRow< 3<SPAN style="color:#00007F">Then</SPAN>
        Range("H1").ClearContents
        <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    Result = Cells(3, 8).Value
    <SPAN style="color:#00007F">If</SPAN> LastRow > 3<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> r = 4<SPAN style="color:#00007F">To</SPAN> LastRow
            <SPAN style="color:#00007F">If</SPAN> Cells(r, 8).Value<> ""<SPAN style="color:#00007F">Then</SPAN>
                Result = Result & ", " & Cells(r, 8).Value
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> r
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    Range("H1").Value = Result<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Here is the result for my data:

Mr Excel.xls
	F	G	H	I
1			70k, 256b, 45r, 345fg, 12b
2			Invoices
3			70k
4			256b
5			45r
6			345fg
7
8
9			12b
10
Concatenate

 

[Barefoot]

Hi Peter

Thank you very much works like a dream.

Cheers
Julian

 

[Barefoot]

Hi Peter

Only 1 small thing.

Is it possible to have the last comma as a & instead?
ie 12k, 156k, 128k & 220k

Cheers
Julian

 

[Peter_SSs]

Julian

Not sure this is the 'neatest' way, but it is what I have come up with. Give it a try:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ConcatenateInvoices()
    <SPAN style="color:#00007F">Dim</SPAN> Result <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastComma <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    LastRow = Range("H65536").End(xlUp).Row
    <SPAN style="color:#00007F">If</SPAN> LastRow < 3 <SPAN style="color:#00007F">Then</SPAN>
        Range("H1").ClearContents
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Result = Cells(3, 8).Value
    <SPAN style="color:#00007F">If</SPAN> LastRow > 3 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> r = 4 <SPAN style="color:#00007F">To</SPAN> LastRow
            <SPAN style="color:#00007F">If</SPAN> Cells(r, 8).Value <> "" <SPAN style="color:#00007F">Then</SPAN>
                Result = Result & ", " & Cells(r, 8).Value
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> r
       LastComma = InStrRev(Result, ",")
       Result = Left(Result, LastComma - 1) & " &" & Right(Result, Len(Result) - LastComma)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Range("H1").Value = Result
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Barefoot]

Hi Peter

Yes had a quick go and all looks good.
I have been playing and have customised it but a big thanks for getting me started.

Thanks a million for all your help.

Best regards
Julian