Conditional Concatenate?

  • Thread starter Thread starter Dan B.
  • Start date Start date
D

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.
 
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()&"!"&"A1:D1"))

Copy down to A4


Gord Dibben MS Excel MVP
 
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)," ",", ")
 
Back
Top