Return multiple column headings

[TonyN]

I have a table that contains various values

<table border="0" cellpadding="0" cellspacing="0" width="500"><colgroup><col style="mso-width-source:userset;mso-width-alt:1828; width:38pt" span="10" width="50"> </colgroup><tbody><tr style="mso-height-source:userset;height:29.25pt" height="39"> <td class="xl63" style="height:29.25pt;width:38pt" height="39" width="50">Mark 1</td> <td class="xl63" style="width:38pt" width="50">Mark 2</td> <td class="xl63" style="width:38pt" width="50">Mark 3</td> <td class="xl63" style="width:38pt" width="50">Mark 4</td> <td class="xl63" style="width:38pt" width="50">Mark 5</td> <td class="xl63" style="width:38pt" width="50">Mark 6</td> <td class="xl63" style="width:38pt" width="50">Mark 7</td> <td class="xl63" style="width:38pt" width="50">Mark 8</td> <td class="xl63" style="width:38pt" width="50">Mark 9</td> <td class="xl63" style="width:38pt" width="50">Mark 10</td> </tr> <tr style="height:75.0pt" height="100"> <td class="xl63" style="height:75.0pt" height="100">1</td> <td class="xl63">2</td> <td class="xl63">x</td> <td class="xl63">x</td> <td class="xl63">4</td> <td class="xl63">5</td> <td class="xl63">10</td> <td class="xl63">10</td> <td class="xl63">9</td> <td class="xl63">1</td> </tr> <tr style="height:75.0pt" height="100"> <td class="xl63" style="height:75.0pt" height="100">5</td> <td class="xl63">x</td> <td class="xl63">6</td> <td class="xl63">8</td> <td class="xl63">6</td> <td class="xl63">4</td> <td class="xl63">9</td> <td class="xl63">8</td> <td class="xl63">5</td> <td class="xl63">x</td> </tr> </tbody></table>I want to return the column headings for each x in a record (row) I can find the first using index and match but not subsequent column headings. Thankyou - Tony

 

[Aladin Akyurek]

I have a table that contains various values

<TABLE border=0 cellSpacing=0 cellPadding=0 width=500><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" span=10 width=50></COLGROUP><TBODY><TR style="HEIGHT: 29.25pt; mso-height-source: userset" height=39><TD style="WIDTH: 38pt; HEIGHT: 29.25pt" class=xl63 height=39 width=50>Mark 1</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 2</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 3</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 4</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 5</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 6</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 7</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 8</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 9</TD><TD style="WIDTH: 38pt" class=xl63 width=50>Mark 10</TD></TR><TR style="HEIGHT: 75pt" height=100><TD style="HEIGHT: 75pt" class=xl63 height=100>1</TD><TD class=xl63>2</TD><TD class=xl63>x</TD><TD class=xl63>x</TD><TD class=xl63>4</TD><TD class=xl63>5</TD><TD class=xl63>10</TD><TD class=xl63>10</TD><TD class=xl63>9</TD><TD class=xl63>1</TD></TR><TR style="HEIGHT: 75pt" height=100><TD style="HEIGHT: 75pt" class=xl63 height=100>5</TD><TD class=xl63>x</TD><TD class=xl63>6</TD><TD class=xl63>8</TD><TD class=xl63>6</TD><TD class=xl63>4</TD><TD class=xl63>9</TD><TD class=xl63>8</TD><TD class=xl63>5</TD><TD class=xl63>x</TD></TR></TBODY></TABLE>I want to return the column headings for each x in a record (row) I can find the first using index and match but not subsequent column headings. Thankyou - Tony

K2, control+shift+enter, not just enter, copy across as far as needed, and copy down:

=IF(COLUMNS($K2:K2)<=COUNTIF($A2:$J2,"X"),INDEX($A$1:$J$1,
   SMALL(IF($A2:$J2="X",COLUMN($A2:$J2)-COLUMN($A2)+1),
     COLUMNS($K2:K2))),"")

 

[T. Valko]

I have a table that contains various values

<TABLE cellSpacing=0 cellPadding=0 width=500 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" span=10 width=50></COLGROUP><TBODY><TR style="HEIGHT: 29.25pt; mso-height-source: userset" height=39><TD class=xl63 style="WIDTH: 38pt; HEIGHT: 29.25pt" width=50 height=39>Mark 1</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 2</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 3</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 4</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 5</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 6</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 7</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 8</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 9</TD><TD class=xl63 style="WIDTH: 38pt" width=50>Mark 10</TD></TR><TR style="HEIGHT: 75pt" height=100><TD class=xl63 style="HEIGHT: 75pt" height=100>1</TD><TD class=xl63>2</TD><TD class=xl63>x</TD><TD class=xl63>x</TD><TD class=xl63>4</TD><TD class=xl63>5</TD><TD class=xl63>10</TD><TD class=xl63>10</TD><TD class=xl63>9</TD><TD class=xl63>1</TD></TR><TR style="HEIGHT: 75pt" height=100><TD class=xl63 style="HEIGHT: 75pt" height=100>5</TD><TD class=xl63>x</TD><TD class=xl63>6</TD><TD class=xl63>8</TD><TD class=xl63>6</TD><TD class=xl63>4</TD><TD class=xl63>9</TD><TD class=xl63>8</TD><TD class=xl63>5</TD><TD class=xl63>x</TD></TR></TBODY></TABLE>I want to return the column headings for each x in a record (row) I can find the first using index and match but not subsequent column headings. Thankyou - Tony

Try this...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	_	_	_	_
2	1	2	x	x	4	5	10	10	9	1	_	M3	M4	_
3	5	x	6	8	6	4	9	8	5	x	_	M2	M10	_
Sheet1

This array formula** entered in L2:

=IF(COLUMNS($L2:L2)>COUNTIF($A2:$J2,"x"),"",INDEX($1:$1,SMALL(IF($A2:$J2="x",COLUMN($A2:$J2)),COLUMNS($L2:L2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed then across until you get a column full of blanks.

 

[TonyN]

Thank you for your effort. Something doesn't seem to work (I used ctrl+shift+enter. Nothing happens. Tony

 

[T. Valko]

Try this...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 23px"><COL style="WIDTH: 33px"><COL style="WIDTH: 36px"><COL style="WIDTH: 29px"><COL style="WIDTH: 32px"><COL style="WIDTH: 29px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M5</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M6</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M7</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M8</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M9</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M10</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">x</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">x</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">x</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">x</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">M10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR></TBODY></TABLE>


This array formula** entered in L2:

=IF(COLUMNS($L2:L2)>COUNTIF($A2:$J2,"x"),"",INDEX($1:$1,SMALL(IF($A2:$J2="x",COLUMN($A2:$J2)),COLUMNS($L2:L2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed then across until you get a column full of blanks.

Here's a small sample file that demonstrates this.

zzzTonyN.xls 16kb

http://cjoint.com/?BFbahm4PECt

 

[TonyN]

Thanks for your efforts, all of you. I made a mistake altering the ranges!
Tony