gather text from all in row, into one cell

[mole999]

I have a set of changing data, it publishes only one item in a set and I need to bring that value to a single cell (from a survey to a report set)

12 questions and I'm looking for a formula or function (not in 2016) that would pull AB6 to CQ6 in a fashion that doesn't require =AB6&AC6&AD6...CQ6

I have seen the question asked many times but not seen any answers.

Any thoughts

 

[Fluff]

You could use a UDF

Function MyConcat2(Cl As Range)
   MyConcat2 = Join(Application.Index(Cl.Resize(, 68).Value, 1, 0), ", ")
End Function

with
=MyConcat2(AB6)

 

[mikerickson]

Here's another UDF

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

=ConcatIf(AB6:CQ6,"<>",AB6:CQ6,",")

 

[mole999]

Thanks Gents, will try both out

 

[mole999]

I found this in between requesting and your updates

Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function

Fluffs seemed easiest but I was getting over selection issues which i resolved by changing the 68 to 3 or 4 as necessary to deal with different return results. Mike I haven't tried yours yet, want to get the work finished before I experiment more