Concatenate the header if cells are not blank in a range

N

Narendra Kumar

New Member
Joined
Nov 25, 2014
Messages
21
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
=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)
 
Upvote 0
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.
 
Upvote 0
Rows are not an issue but you cant really do 52 nested IFs.
If you are interested it can be done via macro tho.
 
Upvote 0
Narendra Kumar said:
its working perfectly, but i have the data in 52 column and 1800 rows.
looking for another solution with great expectations.
Click to expand...

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).
 
Upvote 0
Narendra Kumar said:
Good morning Mr iggydarsa,
yes, please if thru macro no issue.
thanks
Click to expand...
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
ABCDEFGH
1INV/CHG-NAMEABCDFGIHTYUUUUBBBBTOTALRESULT
2ATR123456815155738ABC/IHTY/BBBB
3CRT56781,536.0085222,390.00DFG/UUUU/BBBB
4FHD23418528628502,564.00ABC/IHTY/UUUU
5JKD6785869527581,796.00ABC/IHTY/BBBB
6MNB58768585128521,034.00ABC/DFG/IHTY/BBBB
Sheet1
 
Last edited:
Upvote 0
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).
 
Upvote 0
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
Click to expand...
@Peter,

You had to know I could not resist this (especially when it's a weekend and I'm bored), right? :diablo:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Look up to the last non-blank cell in column 1 to concatenate with a value in column 2
Replies
3
Views
2K
linhdinh90
L

Forum statistics

Threads
1,224,829
Messages
6,181,218
Members
453,024
Latest member
Wingit77

Share this page

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
Back
Top