Filling of table based on horizontal & vertical data

hsandeep

Well-known Member
I have data as follows being generated in 2 columns, column A & column B as below:
 Age Premium 20 5000 56 4000 32 3000 45 2000 29 1000 35 2000 37 1000 48 5000 54 4000 78 3000 90 2000 1 1000 15 3000 33 2000 43 1000 75 5000

<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.

 Premium Age 0 20 21 40 41 60 61 80 81 100 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?

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 20 5000 56 4000 32 3000 45 2000 29 1000 35 2000 37 1000 48 5000 54 4000 78 3000 90 2000 1 1000 15 3000 33 2000 43 1000 75 5000

<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.

 Premium Age 0 20 21 40 41 60 61 80 81 100 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?

The easiest way is to make a pivot table.

Data -> Pivot Table

If this doesn't work for you, let us know.

Istead of Pivot Table, I want to do it with Excel functions & formulas. I am using Excel2007.

Excel Workbook
ABCDEFGHIJKLMNO
356400002021404160618081100
43230001000121
54520002000211
62910003000111
735200040002
83710005000111
9485000
10544000
11783000
12902000
1311000
14153000
15332000
Sheet1

Thanks. It works.

You are welcome.

A little change requested.

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=2></COLGROUP>
Now to make the table with ageband 1-20, 21-40, 41-60, 61-80, 81-100.
How to do? I am using Excel2007.

Robert Mika,

How does your outcome table should look like?

 Effective Ageband Premium 0 20 21 40 41 60 61 80 81 100 1000 2 2 2000 3000 4000 1 1 2 5000 2 3

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

Replies
3
Views
417
Replies
5
Views
232
Replies
0
Views
125
Replies
1
Views
185
Replies
1
Views
496

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.

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

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