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

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136
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

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
Could you post some sample data and what you expected outcomes might be.
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
136

ADVERTISEMENT

How do I do attach a piece of the file?
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386

ADVERTISEMENT

This will do what you ask:

Code:
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

Board Regular
Joined
Nov 24, 2008
Messages
136
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

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,571
Members
430,556
Latest member
Peachforyou

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top