Conditional Concatenate?

[Dan B.]

I am trying to pull survey information from multiple sheets to one summary
sheet. With the survey, there is an option to make a comment, so each sheet
may or many not have a comment. I used the CONCATENATE function to combine
multiple cells, but is there a way to merge different numbers of cells?
Example:
Sheet1 - Info in A1, B1, C1, D1
Sheet2 - Info in A1, B1, C1
Sheet3 - Info in A1
Sheet4 - Info in A1, B1

I can set up the summary page for this, but the next survey might have
Sheet1 - Info in A1, B1
Sheet2 - Info in A1, B1, C1
Sheet3 - Info in A1, B1, C1, D1
Sheet4 - Info in A1

Is there a way to merge a string of data, but depending on how many cells
are in a given range, the merging will not add extra characters, but will
only merge the information that is there?

Hope this makes sense. Thanks.

 

[Gord Dibben]

This UDF will ignore blank cells.

Gives you comma and space separated strings in each cell.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ", "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

In Summary sheet A1 enter this formula

=concatrange(INDIRECT("Sheet"&ROW()&"!"&"A11"))

Copy down to A4


Gord Dibben MS Excel MVP

 

[T. Valko]

If there aren't too many cells involved *and* the data in those cells
doesn't contain any space characters you use something like this.

All on one line:

=SUBSTITUTE(TRIM(Sheet1!A1&" "&Sheet1!B1&
" "&Sheet1!C1&" "&Sheet1!D1)," ",", ")