Concatenate the header if cells are not blank in a range

Narendra Kumar · Jul 15, 2016

Dear Sirs,

Good evening,
Need your help to get desired answer.. with formula like

If cell is non blank in 2nd row(B3:G3) than CONCATENATE the header of non blank cells in range (B3:G3) into H column as result.
Example :
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 106"]
<colgroup><col width="106"></colgroup><tbody>[TR]
[TD="class: xl65, width: 106"][/TD]
[TD="class: xl65, width: 106"][/TD]
[TD="class: xl65, width: 106"]INV/CHG-NAME
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]ABC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]DFG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]IHTY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]UUUU[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]BBBB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]TOTAL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 136"]
<colgroup><col width="136"></colgroup><tbody>[TR]
[TD="class: xl65, width: 136"]RESULT
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<colgroup><col width="106"></colgroup><tbody>[TR]
[TD="class: xl65, width: 106"]ATR1234
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 568.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 15.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 155.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 738.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 136"]
<colgroup><col width="136"></colgroup><tbody>[TR]
[TD="class: xl65, width: 136"]ABC/IHTY/BBBB
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<colgroup><col width="106"></colgroup><tbody>[TR]
[TD="class: xl65, width: 106"]CRT5678
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 1,536.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 852.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 2.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 2,390.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 136"]
<colgroup><col width="136"></colgroup><tbody>[TR]
[TD="class: xl65, width: 136"]DFG/UUUU/BBBB
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<colgroup><col width="106"></colgroup><tbody>[TR]
[TD="class: xl65, width: 106"]FHD2341
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 852.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 862.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 850.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 2,564.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 136"]
<colgroup><col width="136"></colgroup><tbody>[TR]
[TD="class: xl65, width: 136"]ABC/IHTY/UUUU/
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<colgroup><col width="106"></colgroup><tbody>[TR]
[TD="class: xl65, width: 106"]JKD6785
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 86.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 952.00
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 758.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 1,796.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 136"]
<colgroup><col width="136"></colgroup><tbody>[TR]
[TD="class: xl65, width: 136"]ABC/IHTY/BBBB/
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 106"]
<colgroup><col width="106"></colgroup><tbody>[TR]
[TD="class: xl65, width: 106"]MNB5876
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 85.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 85.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 12.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 852.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] 1,034.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 136"]
<colgroup><col width="136"></colgroup><tbody>[TR]
[TD="class: xl65, width: 136"]ABC/DFG/IHTY/BBBB
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance for your help.

iggydarsa · Jul 15, 2016

=left(if(trim(c3)<>"",$c$2&"/", "") & if(trim(d3)<>"",$d$2 & "/", "") & if(trim(e3)<>"",$e$2 & "/","") & if(trim(f3)<>"",$f$2 & "/","") & if(trim(g3)<>"",$g$2 & "/",""), len(if(trim(c3)<>"",$c$2&"/", "") & if(trim(d3)<>"",$d$2 & "/", "") & if(trim(e3)<>"",$e$2 & "/","") & if(trim(f3)<>"",$f$2 & "/","") & if(trim(g3)<>"",$g$2 & "/",""))-1)

Narendra Kumar · Jul 15, 2016

Mr iggydarsa,

Thank you very much for your quick response.
its working perfectly, but i have the data in 52 column and 1800 rows.
looking for another solution with great expectations.

iggydarsa · Jul 15, 2016

Rows are not an issue but you cant really do 52 nested IFs.
If you are interested it can be done via macro tho.

Narendra Kumar · Jul 15, 2016

Good morning Mr iggydarsa,
yes, please if thru macro no issue.
thanks

Rick Rothstein · Jul 15, 2016

Narendra Kumar said:
its working perfectly, but i have the data in 52 column and 1800 rows.
looking for another solution with great expectations.

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your the data, its layout and the overall objective for it).

Peter_SSs · Jul 15, 2016

Narendra Kumar said:
Good morning Mr iggydarsa,
yes, please if thru macro no issue.
thanks

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Code:

Function Headings(rHdrs As Range, rData As Range) As String
  Dim aHdrs As Variant, aData As Variant
  Dim i As Long
  
  aHdrs = rHdrs.Value
  aData = rData.Value
  For i = 1 To UBound(aHdrs, 2)
    If Len(aData(1, i)) Then Headings = Headings & "/" & aHdrs(1, i)
  Next i
  Headings = Mid(Headings, 2)
End Function

Excel Workbook

A

B

C

D

E

F

G

H

1

INV/CHG-NAME

ABC

DFG

IHTY

UUUU

BBBB

TOTAL

RESULT

2

ATR1234

568

15

155

738

ABC/IHTY/BBBB

3

CRT5678

1,536.00

852

2

2,390.00

DFG/UUUU/BBBB

4

FHD2341

852

862

850

2,564.00

ABC/IHTY/UUUU

5

JKD6785

86

952

758

1,796.00

ABC/IHTY/BBBB

6

MNB5876

85

12

852

1,034.00

ABC/DFG/IHTY/BBBB

Sheet1

Rick Rothstein · Jul 15, 2016

Peter gave you a UDF to use. If you don't need your result column to be "live" (that is, immediately reactive to changes in the data itself), then you can save placing 1800 formulas in your worksheet and use this macro instead...

Code:

[B]Sub ConcatenateHeadersForDataCells()
  Dim R As Long, X As Long
  Dim Headers As Variant, Data As Variant, Result As Variant
  Const LastDataColumn As String = "BA"
  Const FirstResultCell As String = "BC2"
  Headers = Range("B1:" & LastDataColumn & "1")
  Data = Range("B2:" & LastDataColumn & Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim Result(1 To UBound(Data), 1 To 1)
  For R = 1 To UBound(Data, 1)
    For X = 1 To UBound(Data, 2)
      If Len(Data(R, X)) Then Result(R, 1) = Result(R, 1) & "/" & Headers(1, X)
    Next
    Result(R, 1) = Mid(Result(R, 1), 2)
  Next
  Range(FirstResultCell).Resize(UBound(Result)) = Result
End Sub[/B]

Note: I was not 100% sure of where your data ended or where your result column was located at, so I provided two constants (the Const statements at the beginning of the code) where you can set them (change my guesses if they are wrong).

Rick Rothstein · Jul 15, 2016

Peter_SSs said:

Code:

Function Headings(rHdrs As Range, rData As Range) As String
  Dim aHdrs As Variant, aData As Variant
  Dim i As Long
  
  aHdrs = rHdrs.Value
  aData = rData.Value
  For i = 1 To UBound(aHdrs, 2)
    If Len(aData(1, i)) Then Headings = Headings & "/" & aHdrs(1, i)
  Next i
  Headings = Mid(Headings, 2)
End Function

@Peter,

You had to know I could not resist this (especially when it's a weekend and I'm bored), right?

Code:

[table="width: 500"]
[tr]
	[td]Function Headings(rHdrs As Range, rData As Range) As String
  Headings = Replace(Replace(Application.Trim(Join(Evaluate("IF(LEN(" & rData.Address & "),substitute(" & rHdrs.Address & ","" "",CHAR(1)),"""")"), " ")), " ", "/"), Chr(1), " ")
End Function
[/td]
[/tr]
[/table]

@Narendra Kumar,

If you choose to go with a UDF instead of the macro I posted in Message #8, use the code Peter posted in Message #7 and not the code above.

Narendra Kumar · Jul 16, 2016

Mr Rick Rothstein,

Till yesterday night i don't have any idea how to deal with it but, Today i have two perfect solutions from You and Mr Peter SSs.
thank you very much for your help.

Concatenate the header if cells are not blank in a range

Narendra Kumar

New Member

Excel Facts

iggydarsa

Well-known Member

Narendra Kumar

New Member

iggydarsa

Well-known Member

Narendra Kumar

New Member

Rick Rothstein

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

Narendra Kumar

New Member

Similar threads

Forum statistics

Share this page

Concatenate the header if cells are not blank in a range

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock