Need an array type of formula that concatenates non blank cells?

[chipgiii]

I have a spread sheet. Across the top row are the column headings. In each row (column BL) is were I want the formula. If you view cells in each row below the heading many are blank and some have text and some a percent value. In Cell BL2 for instance I want a formula that looks across cells BS2:DN2 and ignores the blanks ones, but kind of concatenates the non blank ones with the column Heading.

I was hoping for a concise array formula that maybe uses the join capability ("|"), but not sure if that's the right avenue.

Any helpful ideas are welcomed!

Chip

 

[Comfy]

Take a look at this:

http://www.contextures.com/rickrothsteinexcelvbatext.html

Concatenating a range cannot be done with standard functions.

 

[chipgiii]

This is the right idea, but this brings back the column header of every cell. It does not bring back the Header and Cell value of the non empty cells. In other words I want it to bring back on the header when there is a value in the cell below it. I am not even a decent novice in VBA so that's a second problem.

 

[Comfy]

Could you post some sample data and what you expected outcomes might be.

 

[chipgiii]

How do I do attach a piece of the file?

 

[chipgiii]

https://www.dropbox.com/s/9tdgqy6qqcgvthd/test02.xlsm?dl=0

 

[Comfy]

This will do what you ask:

Function ConCat2(Delimiter As Variant, CellRange As Range, HeadRange As Range) As String
Dim i As Long


If IsMissing(Delimiter) Then Delimiter = ""


For i = 1 To CellRange.Cells.Count
If CellRange.Cells(1, i) <> "" Then
    ConCat2 = ConCat2 & Delimiter & HeadRange.Cells(1, i) & " - " & CellRange.Cells(1, i).Text
End If
Next i
ConCat2 = Mid(ConCat2, Len(Delimiter) + 1)


End Function

=ConCat2(", ",BM5:BP5,BM1:BP1)

 

[chipgiii]

Comfy,

I apologize for being so slow to respond, I had a family emergency and was away from home for the past week. I did want to get back to you and say thanks! This works perfectly. You have no idea how much time this is saving me.

Again thank you!
Chip

 

[Comfy]

Comfy,

I apologize for being so slow to respond, I had a family emergency and was away from home for the past week. I did want to get back to you and say thanks! This works perfectly. You have no idea how much time this is saving me.

Again thank you!
Chip

Sorry to hear that.

Just glad that I could help.