Vlookup for segment income statement(duplicate lookup values)

[excelsishya]

Hi all,

Many times i came across divisional income statement which has duplicate lookup value where normal vlookup doesnt work.

here is data sample type

Sheet2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 159px;"> <col style="width: 132px;"> <col style="width: 119px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>CONSOLIDATED</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-weight: bold;">Revenue</td> <td>NORMAL VLOOKUP</td> <td style="font-weight: bold;">OUTPUT</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>A1</td> <td style="text-align: right;">3</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>A2</td> <td style="text-align: right;">3</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>A3</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>Total</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>Cost</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>A1</td> <td style="text-align: right;">3</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>A2</td> <td style="text-align: right;">3</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>A3</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>Gross profit</td> <td style="text-align: right;">0</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>A1</td> <td style="text-align: right;">3</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>A2</td> <td style="text-align: right;">3</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td>A3</td> <td style="text-align: right;">2</td> <td style="text-align: right;">-2</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B3</td> <td>=VLOOKUP(A3,$H$8:$I$17,2,0)</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>H</td> <td>I</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>LOOK UP TABLE</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>Revenue</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>A1</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>A2</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>Cost</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>A1</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>A2</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>A3</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td>Gross profit</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td>A1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td>A2</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td>A3</td> <td style="text-align: right;">-2</td></tr></tbody></table>

is it possible to get correct answers as shown in output using any lookup function variants in excel.

I am using excel 2007.

Thanks

 

[pedie]

Hi...I tried this way and works for me....
maynot be the best way but try..

Formula from G1 to G8 here is

=INDEX(($B$2:$B$3,$B$5:$B$7,$B$9:$B$11),MATCH($F1,$K$1:$K$3,0),0,MATCH($E1,$J$1:$J$3,0))

PROVIDED you arrange your out table table this way....


<TABLE style="WIDTH: 258pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=345><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" width=19><COL style="WIDTH: 5pt; mso-width-source: userset; mso-width-alt: 256" width=7><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 15pt; mso-width-source: userset; mso-width-alt: 731" width=20><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" width=19><COL style="WIDTH: 7pt; mso-width-source: userset; mso-width-alt: 329" width=9><COL style="WIDTH: 13pt; mso-width-source: userset; mso-width-alt: 621" width=17><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 15pt; mso-width-source: userset; mso-width-alt: 731" width=20><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; WIDTH: 53pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18 width=71>

Revenue​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; WIDTH: 14pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=19>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=7>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; WIDTH: 16pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=21>

D1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=71>

Revenue​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; WIDTH: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=20>

A1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; WIDTH: 14pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=19>

5​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; WIDTH: 7pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=9>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; WIDTH: 13pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=17>

I1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=71>

Revenue​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; WIDTH: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=20>

A1​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

A1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

5​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

D2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

Revenue​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

A2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>

I2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>

Cost​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>

A2​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

A2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

D3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

Cost​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

A1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

12​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>

I3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>

Gross profit​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #8db4e3; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>

A3​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

Cost​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

D4​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

Cost​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

A2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

4​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

A1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

12​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

D5​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

Cost​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

A3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

A2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

4​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

D6​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

Gross profit​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

A1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

A3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

D7​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

Gross profit​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

A2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

Gross profit​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

D8​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

Gross profit​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

A3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #17375d; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

A1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

A2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

A3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

1

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2792066 class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: gray; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=18>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: gray; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>

​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #948b54; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD></TR></TBODY></TABLE>​

 

[excelsishya]

Hi pedie,

Thanks for showing interest in post.

i cant rearrange table . those are detailed income statement of various division (each division has 150-250 rows).

My best bet is to have some kind of look which will give value based on order of occurrence.

lets say

in revenue
a1
a2
a3
there

cost a1
a2
a3

it looks up in lookup table based on order it gives value match,

though it may not 100% correct , eg when revenue misses a3.

this would give gud base and manual glance from there would help.

Thanks

 

[Peter_SSs]

I certainly think you need to somehow identify your headings/groups and what needs to be looked up in the table and what does not. Here is another way that hopefully gives you something to go on.

I have added a new columnA and marked the beginning of each group with a number.
In the lookup table I have used column H to identify which group each section belongs to.

Then formula in C3 is copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		CONSOLIDATED
2	1	Revenue
3		A1	3
4		A2	3
5		A3
6		Total							LOOK UP TABLE
7	2	Cost							Revenue
8		A1	2					1	A1	3
9		A2	2					1	A2	3
10		A3	2						Cost
11	3	Gross profit						2	A1	2
12		A1	1					2	A2	2
13		A2	1					2	A3	2
14		A3	-2						Gross profit
15								3	A1	1
16								3	A2	1
17								3	A3	-2
Lookup

 

[pedie]

excelsishya, i'll be happy to help...
I am not asking to re-arrange your table, just the output table so it looks up the right table & labels for right answer...as you have duplicates in labels except for the table headings...
that works perfect for me....

Hi pedie,

Thanks for showing interest in post.

i cant rearrange table . those are detailed income statement of various division (each division has 150-250 rows).

My best bet is to have some kind of look which will give value based on order of occurrence.

lets say

in revenue
a1
a2
a3
there

cost a1
a2
a3

it looks up in lookup table based on order it gives value match,

though it may not 100% correct , eg when revenue misses a3.

this would give gud base and manual glance from there would help.

Thanks

 

[excelsishya]

pedie and Peter thanks for reply.

pedie i didnt get in beginning what you done to my original table. but at 2nd look i had partially understanding of it . could you please put screen shot of it using Excel Jeanie HTML 4. i would get better understanding of it.


Peter your method of identifying groups by numbering involves manupulating both lookup value ie consolidated table and look table.

its not time consuming to number consolidated template but i will run into trouble changing lookup tables.

in real situation these lookup tables are divisions income statement in separate work books often even in seperate tabs for separate periods.
so when consolidating monthly data this method involves considerable time.apologies not providing this info .

Based on what you and pedie are trying to do , i got idea

if you see header/group would have no data right to it.

is it possible in VBA When run on tab insert1 column left to active selected column and copy 1st heading data like shown below.


eg in above when run VBA select

Sheet1

<table style="background-color: rgb(255, 255, 255); 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: 95px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>revenue</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>a1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>a2</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>a3</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>total</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>cogs</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>a1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>a2</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td>a3</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td>total</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td>Gross profit</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td>a1</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>a2</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>a3</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>total</td> <td style="text-align: right;">0</td></tr></tbody></table>after vba

<table border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>LOOK UP TABLES</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>
</td> <td>revenue</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>revenue</td> <td>a1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>revenue</td> <td>a2</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>revenue</td> <td>a3</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>revenue</td> <td>total</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>
</td> <td>cogs</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>cogs</td> <td>a1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td>cogs</td> <td>a2</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td>cogs</td> <td>a3</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td>cogs</td> <td>total</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td>
</td> <td>Gross profit</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td>Gross profit</td> <td>a1</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td>Gross profit</td> <td>a2</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td>Gross profit</td> <td>a3</td> <td style="text-align: right;">0</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>Gross profit</td> <td>total</td> <td style="text-align: right;">0</td></tr></tbody></table>

i guess both your's and pedie bottom line concept is to make this kind

from there on concentated vlooup would do job.

if above is possible in VBA in can run on each lookup table.

Thanks

 

[excelsishya]

Refresh

Any ideas on above problem VBA method or any lookup variants.

Thanks

 

[shawnhet]

I am a bit confused by your opening screen shot here. I am thinking that a consolidated statement is one constructed by adding together the statements from different divisions (ie A1,A2 and A3). Normally, I would think the process would go from a collection of segmented income statements are used to build a consolidated statement. Are you trying to split out segment income statements from a consolidated statement?

If, OTOH, you are trying to construct a consolidated statement from a bunch of segments, can you show me what your source data looks like for one division and then what the output should look like on your consolidated page(assuming you only had the one division)?

I am getting the impression that something is being lost in translation here.

Cheers,

 

[excelsishya]

shawnhet i think screen shot was too much sanitized.

I have master template ie consolidated entity.There are various subsidaries using consolidated entity lookup value i need to construct divisional income statement for each enties.

TEMPLATE

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 376px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>INCOME STATEMENT-CONSOLIDATED</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-weight: bold;">Revenue</td> <td style="font-weight: bold;">Jan</td> <td style="font-weight: bold;">Feb</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Beer</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Whisky</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>Soft drinks</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>Mineral water</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-weight: bold;">Total Revenue</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="font-weight: bold;">cost</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>Beer</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>Whisky</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>Soft drinks</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>Mineral water</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="font-weight: bold;">Total cost</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="font-weight: bold;">GP</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td>Beer</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td>Whisky</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td>Soft drinks</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td>Mineral water</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td style="font-weight: bold;">Total GP</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td> <td style="font-weight: bold;">MARKETING</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td> <td>BEER EXP</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td> <td>Whisky EXP</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td> <td style="font-weight: bold;">OTHER EXP</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">24</td> <td>BEER EXP</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">25</td> <td>Whisky EXP</td> <td>
</td> <td>
</td></tr></tbody></table>

DIV1 LOOKUP TABLE

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 100px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>DIV1</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-weight: bold;">Revenue</td> <td style="font-weight: bold;">Jan</td> <td style="font-weight: bold;">Feb</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Beer</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Whisky</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>Mineral water</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-weight: bold;">Total Revenue</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-weight: bold;">cost</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>Beer</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>Whisky</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>Mineral water</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="font-weight: bold;">Total cost</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="font-weight: bold;">GP</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>Beer</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td>Whisky</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td>Mineral water</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td style="font-weight: bold;">Total GP</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td style="font-weight: bold;">MARKETING</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td>BEER EXP</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td>Whisky EXP</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td> <td style="font-weight: bold;">OTHER EXP</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td> <td>BEER EXP</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td> <td>Whisky EXP</td> <td style="text-align: right;">1</td> <td style="text-align: right;">1</td></tr></tbody></table>
DIV2

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 100px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>DIV2</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-weight: bold;">Revenue</td> <td style="font-weight: bold;">Jan</td> <td style="font-weight: bold;">Feb</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Beer</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Mineral water</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="font-weight: bold;">Total Revenue</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-weight: bold;">cost</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>Beer</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>Mineral water</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="font-weight: bold;">Total cost</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="font-weight: bold;">GP</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>Whisky</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>Mineral water</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="font-weight: bold;">Total GP</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="font-weight: bold;">MARKETING</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td>BEER EXP</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td style="font-weight: bold;">OTHER EXP</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td>BEER EXP</td> <td style="text-align: right;">2</td> <td style="text-align: right;">2</td> <td>
</td></tr></tbody></table>
i think now it might be clearer.Please let me know if any additional information needed.

Thanks
Excel tables to the web >> Excel Jeanie HTML 4

 

[shawnhet]

Hi excelsishya,

Let me see if I am following you here at all -

#1. You have a consolidated financial statement that you want to use to construct segment or division financial statements.

If this is true, then there are a variety of ways one can approach this issue, Peter suggests one above - here is a similar thread (with a statement consolidated by product lines not divisions).

http://www.mrexcel.com/forum/showthread.php?t=565082

#2. You have more than one consolidated financial statement that you want to do this with and each of these will have slightly different formats, column titles and products, correct?

If you have more than one financial statement, you need to look at the ways your statements are all the same to have a hope of answering this. Off the top of my head, I find it unlikely that they will be similar enough to allow one framework to deal with this. Potentially I am wrong, but I will wait to hear back, if I am even close to what you need.

Cheers,