Get table headings using Formula

[somnath_it2006]

Dear All


I have data like:


Headings-----------Col1-----Col2------Col3------Col4
Col1, Col4-----------5000--------0----------0------2000
Col3-------------------0---------0-----70000----------0
Col1, Col2, Col4----78000----60000----------0-----45000


If there is any non-zero amount found then I wanted to capture that column head in first column of the table.

I wanted to done that using Formula only.

Is it possible?

Appreciate all your help.

 

[taurean]

Do you need commas in between badly? Or the following approach is OK?


<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 103px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 95px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Headings</td> <td style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Col1</td> <td style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Col2</td> <td style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Col3</td> <td style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Col4</td> <td style="BACKGROUND-COLOR: #c0c0c0; FONT-WEIGHT: bold">Formula</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td>Col1, Col4</td> <td style="TEXT-ALIGN: right">5000</td> <td style="TEXT-ALIGN: right">0</td> <td style="TEXT-ALIGN: right">0</td> <td style="TEXT-ALIGN: right">2000</td> <td style="BACKGROUND-COLOR: #99cc00">Col1 Col4</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td>Col3</td> <td style="TEXT-ALIGN: right">0</td> <td style="TEXT-ALIGN: right">0</td> <td style="TEXT-ALIGN: right">70000</td> <td style="TEXT-ALIGN: right">0</td> <td style="BACKGROUND-COLOR: #99cc00">Col3</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td>Col1, Col2, Col4</td> <td style="TEXT-ALIGN: right">78000</td> <td style="TEXT-ALIGN: right">60000</td> <td style="TEXT-ALIGN: right">0</td> <td style="TEXT-ALIGN: right">45000</td> <td style="BACKGROUND-COLOR: #99cc00">Col1 Col2 Col4</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>F2</td> <td>=TRIM(CONCATENATE(IF(B2<>0,$B$1&" ",""),IF(C2<>0,$C$1&" ",""),IF(D2<>0,$D$1&" ",""),IF(E2<>0,$E$1,"")))</td></tr> <tr> <td>F3</td> <td>=TRIM(CONCATENATE(IF(B3<>0,$B$1&" ",""),IF(C3<>0,$C$1&" ",""),IF(D3<>0,$D$1&" ",""),IF(E3<>0,$E$1,"")))</td></tr> <tr> <td>F4</td> <td>=TRIM(CONCATENATE(IF(B4<>0,$B$1&" ",""),IF(C4<>0,$C$1&" ",""),IF(D4<>0,$D$1&" ",""),IF(E4<>0,$E$1,"")))</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Dear All


I have data like:


Headings-----------Col1-----Col2------Col3------Col4
Col1, Col4-----------5000--------0----------0------2000
Col3-------------------0---------0-----70000----------0
Col1, Col2, Col4----78000----60000----------0-----45000


If there is any non-zero amount found then I wanted to capture that column head in first column of the table.

I wanted to done that using Formula only.

Is it possible?

Appreciate all your help.

<TABLE style="WIDTH: 322pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=429><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4636" width=130><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2531" width=71><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2787" width=78><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2560" width=72><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 98pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=130>Headings</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=78>Col1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=71>Col2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=78>Col3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=72>Col4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Col1, Col4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2000</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Col3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>70000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19>Col1, Col2, Col4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>78000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>60000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>45000</TD></TR></TBODY></TABLE>

Add the following function code to your workbook as a module:
________________________________________________________
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
________________________________________________________

Then invoke...

A2, control+shift+enter, not just enter, and copy down:

=SUBSTITUTE(aconcat(IF(ISNUMBER(B2:E2),IF(1-(B2:E2=0),", "&$B$1:$E$1,""),"")),", ","",1)