Filling of table based on horizontal & vertical data

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,211
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
I have data as follows being generated in 2 columns, column A & column B as below:
Age Premium
205000
564000
323000
452000
291000
352000
371000
485000
544000
783000
902000
11000
153000
332000
431000
755000

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>
In column E4:E10, premium is to generated in increasing order.
Based on Age (as 0 to 20, 0 & 20 punched in different cells), Total number of premium has to be filled as below.
For no filling, null ("") is required.

PremiumAge
02021404160618081100
1000 1 2 1
2000 2 1 1
3000 1 1 1
4000 2
5000 1 1 1

<COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY>
</TBODY>

How to accomplish?
Thanks in advance.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I have data as follows being generated in 2 columns, column A & column B as below:
Age Premium
205000
564000
323000
452000
291000
352000
371000
485000
544000
783000
902000
11000
153000
332000
431000
755000

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>
In column E4:E10, premium is to generated in increasing order.
Based on Age (as 0 to 20, 0 & 20 punched in different cells), Total number of premium has to be filled as below.
For no filling, null ("") is required.

PremiumAge
02021404160618081100
1000 1 2 1
2000 2 1 1
3000 1 1 1
4000 2
5000 1 1 1

<COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY>
</TBODY>

How to accomplish?
Thanks in advance.

The easiest way is to make a pivot table.

Data -> Pivot Table

Set Premium as your rows, and Age as your columns.

If this doesn't work for you, let us know.
 
Upvote 0
Istead of Pivot Table, I want to do it with Excel functions & formulas. I am using Excel2007.
 
Upvote 0
Excel Workbook
ABCDEFGHIJKLMNO
1AgePremium
2205000PremiumAge
356400002021404160618081100
43230001000121  
54520002000211
62910003000111
735200040002
83710005000111
9485000
10544000
11783000
12902000
1311000
14153000
15332000
Sheet1
 
Upvote 0
A little change requested.
EMPLOYEE NAME</SPAN>RELATIONSHIP</SPAN>AGE</SPAN>Premium</SPAN>Effective Premium</SPAN>
ANANAD NARAYAN KADAM</SPAN>SELF</SPAN>47</SPAN>5000</SPAN>5000</SPAN>
ANANAD NARAYAN KADAM</SPAN>SPOUSE</SPAN>43</SPAN> 5000</SPAN>
ANANAD NARAYAN KADAM</SPAN>SON</SPAN>19</SPAN> 5000</SPAN>
ANANAD NARAYAN KADAM</SPAN>SON</SPAN>17</SPAN> 5000</SPAN>
SUNIL R BOBDE</SPAN>SELF</SPAN>50</SPAN>4000</SPAN>4000</SPAN>
SUNIL R BOBDE</SPAN>SPOUSE</SPAN>46</SPAN> 4000</SPAN>
SUNIL R BOBDE</SPAN>DAUGHTER</SPAN>21</SPAN> 4000</SPAN>
SUNIL R BOBDE</SPAN>DAUGHTER</SPAN>15</SPAN> 4000</SPAN>
ARVIND S. VITEKAR</SPAN>SELF</SPAN>38</SPAN>1000</SPAN>1000</SPAN>
ARVIND S. VITEKAR</SPAN>SPOUSE</SPAN>31</SPAN> 1000</SPAN>
ARVIND S. VITEKAR</SPAN>SON</SPAN>11</SPAN> 1000</SPAN>
ARVIND S. VITEKAR</SPAN>SON</SPAN>7</SPAN> 1000</SPAN>
ANANAD NARAYAN KADAM</SPAN>SON</SPAN>46</SPAN> 5000</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=2></COLGROUP>
Effective premium column=Premium of EMPLOYEE NAME.
Now to make the table with ageband 1-20, 21-40, 41-60, 61-80, 81-100.
How to do? I am using Excel2007.
 
Upvote 0
EffectiveAgeband
Premium02021404160618081100
1000 2 2
2000
3000
4000 1 1 2
5000 2 3

<COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY>
</TBODY>
 
Upvote 0

Forum statistics

Threads
1,211,965
Messages
6,105,122
Members
447,947
Latest member
OX_2005

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