Concatenate the header if cells are not blank in a range

[Narendra Kumar]

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 :

INV/CHG-NAME <colgroup><col width="106"></colgroup><tbody> </tbody>	ABC <colgroup><col width="64"></colgroup><tbody> </tbody>	DFG <colgroup><col width="64"></colgroup><tbody> </tbody>	IHTY <colgroup><col width="64"></colgroup><tbody> </tbody>	UUUU <colgroup><col width="64"></colgroup><tbody> </tbody>	BBBB <colgroup><col width="64"></colgroup><tbody> </tbody>	TOTAL <colgroup><col width="64"></colgroup><tbody> </tbody>	RESULT <colgroup><col width="136"></colgroup><tbody> </tbody>
ATR1234 <colgroup><col width="106"></colgroup><tbody> </tbody>	568.00 <colgroup><col></colgroup><tbody> </tbody>		15.00 <colgroup><col></colgroup><tbody> </tbody>		155.00 <colgroup><col></colgroup><tbody> </tbody>	738.00 <colgroup><col></colgroup><tbody> </tbody>	ABC/IHTY/BBBB <colgroup><col width="136"></colgroup><tbody> </tbody>
CRT5678 <colgroup><col width="106"></colgroup><tbody> </tbody>		1,536.00 <colgroup><col></colgroup><tbody> </tbody>		852.00 <colgroup><col></colgroup><tbody> </tbody>	2.00 <colgroup><col></colgroup><tbody> </tbody>	2,390.00 <colgroup><col></colgroup><tbody> </tbody>	DFG/UUUU/BBBB <colgroup><col width="136"></colgroup><tbody> </tbody>
FHD2341 <colgroup><col width="106"></colgroup><tbody> </tbody>	852.00 <colgroup><col></colgroup><tbody> </tbody>		862.00 <colgroup><col></colgroup><tbody> </tbody>	850.00 <colgroup><col></colgroup><tbody> </tbody>		2,564.00 <colgroup><col></colgroup><tbody> </tbody>	ABC/IHTY/UUUU/ <colgroup><col width="136"></colgroup><tbody> </tbody>
JKD6785 <colgroup><col width="106"></colgroup><tbody> </tbody>	86.00 <colgroup><col></colgroup><tbody> </tbody>		952.00 <colgroup><col></colgroup><tbody> </tbody>		758.00 <colgroup><col></colgroup><tbody> </tbody>	1,796.00 <colgroup><col></colgroup><tbody> </tbody>	ABC/IHTY/BBBB/ <colgroup><col width="136"></colgroup><tbody> </tbody>
MNB5876 <colgroup><col width="106"></colgroup><tbody> </tbody>	85.00 <colgroup><col></colgroup><tbody> </tbody>	85.00 <colgroup><col></colgroup><tbody> </tbody>	12.00 <colgroup><col></colgroup><tbody> </tbody>		852.00 <colgroup><col></colgroup><tbody> </tbody>	1,034.00 <colgroup><col></colgroup><tbody> </tbody>	ABC/DFG/IHTY/BBBB <colgroup><col width="136"></colgroup><tbody> </tbody>

<tbody>
</tbody>

Thanks in advance for your help.

 

[iggydarsa]

=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]

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]

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]

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

 

[Rick Rothstein]

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]

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)

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	85	12		852	1,034.00	ABC/DFG/IHTY/BBBB
Sheet1

 

[Rick Rothstein]

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...

[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]

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?

[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]

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.