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

chipgiii

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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
137
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,377
Could you post some sample data and what you expected outcomes might be.
 

chipgiii

Board Regular
Joined
Nov 24, 2008
Messages
137

ADVERTISEMENT

How do I do attach a piece of the file?
 

Comfy

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

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
137
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,377
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,222
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top