Filling of table based on horizontal & vertical data

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,226
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:
[TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Age [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Premium[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]5000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent, align: right"]4000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]32[/TD]
[TD="bgcolor: transparent, align: right"]3000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]45[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]35[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]37[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]48[/TD]
[TD="bgcolor: transparent, align: right"]5000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]54[/TD]
[TD="bgcolor: transparent, align: right"]4000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78[/TD]
[TD="bgcolor: transparent, align: right"]3000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]90[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]3000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]33[/TD]
[TD="bgcolor: transparent, align: right"]2000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]43[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]75[/TD]
[TD="bgcolor: transparent, align: right"]5000[/TD]
[/TR]
</TBODY>[/TABLE]
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.

[TABLE="width: 528"]
<COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY>[TR]
[TD="class: xl70, width: 64, bgcolor: transparent"]Premium[/TD]
[TD="class: xl66, width: 640, bgcolor: transparent, colspan: 10"]Age[/TD]
[/TR]
[TR]
[TD="class: xl71, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]60[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]61[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]80[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]81[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5000[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

How to accomplish?
Thanks in advance.
 
Sort by date Sort by votes
Excel 2010
BCDEFGHIJKLMNOPQ
RELATIONSHIP AGE Premium Effective Premium
SELF Effective Ageband
SPOUSE Premium
SON
SON
SELF
SPOUSE
DAUGHTER
DAUGHTER
SELF
SPOUSE
SON
SON
SON

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]47[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]38[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"]=IF(COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&H$3,$C$2:$C$14,"<="&$I$3)=0,"",COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&H$3,$C$2:$C$14,"<="&$I$3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]K4[/TH]
[TD="align: left"]=IF(COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&J$3,$C$2:$C$14,"<="&$K$3)=0,"",COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&J$3,$C$2:$C$14,"<="&$K$3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]M4[/TH]
[TD="align: left"]=IF(COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&L$3,$C$2:$C$14,"<="&$M$3)=0,"",COUNTIFS($E$2:$E$14,$G4,$C$2:$C$14,">="&L$3,$C$2:$C$14,"<="&$M$3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
prevent message box from copying from userform to sheet based on matching missed month.
Replies
0
Views
74
Ali M
A
V
  • Solved
Suitable formula needed
2
Replies
16
Views
478
Joe4
Joe4
K
  • Solved
Sum data with multiple criteria
Replies
4
Views
81
Fluff
Fluff
O
  • Solved
show data on userform vertically based on combobox for multiple ranges
Replies
2
Views
161
Omran Y
O
A
  • Solved
add formulas in last column after sorting for each duplicates ID
Replies
9
Views
195
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,226,812
Messages
6,193,116
Members
453,777
Latest member
Miceal Powell

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