Hi all, I'm hoping someone can help with my problem.
I need a formula which can concatenate the values in a row along with its corresponding column header and sub header descriptions, I assume it might some combination of INDIRECT with Match and Concatenate but am struggling to thing of how it work.
<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<link rel=File-List href="Formula_example_files/filelist.xml">
<style id="Formula_example_24575_Styles">
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
.xl1524575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6324575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6424575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:#DBDBDB;
mso-pattern:black none;
white-space:nowrap;}
.xl6524575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:#FFE699;
mso-pattern:black none;
white-space:nowrap;}
.xl6624575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:#8EA9DB;
mso-pattern:black none;
white-space:nowrap;}
.xl6724575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:yellow;
mso-pattern:black none;
white-space:nowrap;}
-->
</style>
</head>
******>
<!--[if !excel]> <![endif]-->
<!--The following information was generated by Microsoft Excel's Publish as Web
Page wizard.-->
<!--If the same item is republished from Excel, all information between the DIV
tags will be replaced.-->
<!----------------------------->
<!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD -->
<!----------------------------->
<div id="Formula_example_24575" align=center xublishsource="Excel">
<table border=0 cellpadding=0 cellspacing=0 width=706 style='border-collapse:
collapse;table-layout:fixed;width:530pt'>
<col width=64 span=2 style='width:48pt'>
<col width=258 style='mso-width-source:userset;mso-width-alt:9435;width:194pt'>
<col width=64 span=5 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 width=64 style='height:15.0pt;width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=258 style='width:194pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6324575> </td>
<td class=xl6324575 style='border-left:none'> </td>
<td class=xl6424575 style='border-left:none'>Sale</td>
<td class=xl6424575 style='border-left:none'>Sale</td>
<td class=xl6424575 style='border-left:none'>Return</td>
<td class=xl6424575 style='border-left:none'>Sale</td>
<td class=xl6424575 style='border-left:none'>Return</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6524575 style='border-top:none'>Market</td>
<td class=xl6524575 style='border-top:none;border-left:none'>Formula</td>
<td class=xl6524575 style='border-top:none;border-left:none'>TV</td>
<td class=xl6524575 style='border-top:none;border-left:none'>Bike</td>
<td class=xl6524575 style='border-top:none;border-left:none'>PC</td>
<td class=xl6524575 style='border-top:none;border-left:none'>Fridge</td>
<td class=xl6524575 style='border-top:none;border-left:none'>Cooker</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6624575 style='border-top:none'>Market A</td>
<td class=xl6724575 style='border-top:none;border-left:none'>Sale -TV(2),
Return-PC(5),Sael-Fridge(3)</td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>2</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>5</td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>3</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6624575 style='border-top:none'>Market B</td>
<td class=xl6724575 style='border-top:none;border-left:none'>Sale-Bike(1),Sale-Fridge(2)</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>1</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>2</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6624575 style='border-top:none'>Market C</td>
<td class=xl6724575 style='border-top:none;border-left:none'>Sale-TV(2),Sale-Bike(4)</td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>2</td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>4</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=258 style='width:194pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>
</div>
<!----------------------------->
<!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD-->
<!----------------------------->
</body>
</html>
Many thanks for your help in advance!
I need a formula which can concatenate the values in a row along with its corresponding column header and sub header descriptions, I assume it might some combination of INDIRECT with Match and Concatenate but am struggling to thing of how it work.
<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<link rel=File-List href="Formula_example_files/filelist.xml">
<style id="Formula_example_24575_Styles">
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
.xl1524575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6324575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
mso-background-source:auto;
mso-pattern:auto;
white-space:nowrap;}
.xl6424575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:#DBDBDB;
mso-pattern:black none;
white-space:nowrap;}
.xl6524575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:#FFE699;
mso-pattern:black none;
white-space:nowrap;}
.xl6624575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:#8EA9DB;
mso-pattern:black none;
white-space:nowrap;}
.xl6724575
{padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:black;
font-size:11.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Calibri, sans-serif;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:.5pt solid windowtext;
background:yellow;
mso-pattern:black none;
white-space:nowrap;}
-->
</style>
</head>
******>
<!--[if !excel]> <![endif]-->
<!--The following information was generated by Microsoft Excel's Publish as Web
Page wizard.-->
<!--If the same item is republished from Excel, all information between the DIV
tags will be replaced.-->
<!----------------------------->
<!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD -->
<!----------------------------->
<div id="Formula_example_24575" align=center xublishsource="Excel">
<table border=0 cellpadding=0 cellspacing=0 width=706 style='border-collapse:
collapse;table-layout:fixed;width:530pt'>
<col width=64 span=2 style='width:48pt'>
<col width=258 style='mso-width-source:userset;mso-width-alt:9435;width:194pt'>
<col width=64 span=5 style='width:48pt'>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 width=64 style='height:15.0pt;width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=258 style='width:194pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
<td class=xl1524575 width=64 style='width:48pt'></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6324575> </td>
<td class=xl6324575 style='border-left:none'> </td>
<td class=xl6424575 style='border-left:none'>Sale</td>
<td class=xl6424575 style='border-left:none'>Sale</td>
<td class=xl6424575 style='border-left:none'>Return</td>
<td class=xl6424575 style='border-left:none'>Sale</td>
<td class=xl6424575 style='border-left:none'>Return</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6524575 style='border-top:none'>Market</td>
<td class=xl6524575 style='border-top:none;border-left:none'>Formula</td>
<td class=xl6524575 style='border-top:none;border-left:none'>TV</td>
<td class=xl6524575 style='border-top:none;border-left:none'>Bike</td>
<td class=xl6524575 style='border-top:none;border-left:none'>PC</td>
<td class=xl6524575 style='border-top:none;border-left:none'>Fridge</td>
<td class=xl6524575 style='border-top:none;border-left:none'>Cooker</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6624575 style='border-top:none'>Market A</td>
<td class=xl6724575 style='border-top:none;border-left:none'>Sale -TV(2),
Return-PC(5),Sael-Fridge(3)</td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>2</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>5</td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>3</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6624575 style='border-top:none'>Market B</td>
<td class=xl6724575 style='border-top:none;border-left:none'>Sale-Bike(1),Sale-Fridge(2)</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>1</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>2</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl1524575 style='height:15.0pt'></td>
<td class=xl6624575 style='border-top:none'>Market C</td>
<td class=xl6724575 style='border-top:none;border-left:none'>Sale-TV(2),Sale-Bike(4)</td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>2</td>
<td class=xl6324575 align=right style='border-top:none;border-left:none'>4</td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
<td class=xl6324575 style='border-top:none;border-left:none'> </td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=258 style='width:194pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>
</div>
<!----------------------------->
<!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD-->
<!----------------------------->
</body>
</html>
Many thanks for your help in advance!
Last edited: