Concatenate dynamic Column values with column header

R

rockyboy

New Member
Joined
Apr 19, 2005
Messages
4
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:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:x="urn:schemas-microsoft-com:eek:ffice: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-ignore:padding;
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-ignore:padding;
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-ignore:padding;
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-ignore:padding;
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-ignore:padding;
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-ignore:padding;
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 x:publishsource="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:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Sorry I got the html a bit wrong, the example table is below, also just to mention the number of columns can increase...

<html xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"
xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th></th><th> </th><th>Sale</th><th>Sale</th><th>Return</th><th>Sale</th><th>Return</th></tr></thead><tbody>
<tr><td>Market</td><td>Formula</td><td>TV</td><td>Bike</td><td>PC</td><td>Fridge</td><td>Cooker</td></tr>
<tr><td>Market A</td><td>Sale -TV(2), Return-PC(5),Sael-Fridge(3)</td><td>2</td><td> </td><td>5</td><td>3</td><td> </td></tr>
<tr><td>Market B</td><td>Sale-Bike(1),Sale-Fridge(2)</td><td> </td><td>1</td><td> </td><td>2</td><td> </td></tr>
<tr><td>Market C</td><td>Sale-TV(2),Sale-Bike(4)</td><td>2</td><td>4</td><td> </td><td> </td><td></td></tr>
</tbody></table>
</html>
 
Upvote 0
Hi all, I was just wondering if anyone had any suggestions for?

I'm trying to use a combination of Lookup an dynamic ranges but struggling to get a solution.

Many thanks in advance!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
350
esben
E
E
  • Question
Create mails with table - standard header specific row
Replies
1
Views
947
esben
E
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
B
  • Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,006
Messages
6,122,666
Members
449,091
Latest member
peppernaut

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