Filling of table based on horizontal & vertical data

[hsandeep]

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?
Thanks in advance.

 

[Sal Paradise]

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

 

[hsandeep]

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

 

[Robert Mika]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Age	Premium
2	20	5000			Premium	Age
3	56	4000				0	20	21	40	41	60	61	80	81	100
4	32	3000			1000		1		2		1
5	45	2000			2000				2		1				1
6	29	1000			3000		1		1				1
7	35	2000			4000						2
8	37	1000			5000		1				1		1
9	48	5000
10	54	4000
11	78	3000
12	90	2000
13	1	1000
14	15	3000
15	33	2000
Sheet1

 

[hsandeep]

Thanks. It works.

 

[Robert Mika]

You are welcome.

 

[hsandeep]

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.

 

[hsandeep]

Robert Mika,
Please reply above.

 

[Robert Mika]

How does your outcome table should look like?

 

[hsandeep]

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>