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:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office: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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office: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

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
519
esben
E
E
  • Question Question
Create mails with table - standard header specific row
Replies
1
Views
1K
esben
E
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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