vba to calculate record summary

[saravanan1981]

Hi,
I have a data in specific Format as per Sheet “Data”. In column H there is relation against all records in column F there is age bracket against all records. In sheet “Template” I have template in which data to be summarised as per age band. Example data given in Sheet “Data As per Template”.


Now I need macro for below task.

1) All records in data to be checked as per age in band (in column F) and as per Sum insured (in column K) and put the count in respective column in template.
2) In Template sheet there is Column “Total Self Count” (column M). In column M, only check the count of self in sheet “Data” and put the count of only self in Column M as per sum inured (in column K of sheet “Data”)

Please refer sheet “Data As per Template” for example.

[sintek]

Hi saravanan1981

Wanna give this a go....

Please Login or Register  to view this content.

[brokenbiscuits]

Hi

Is there a reason this needs to be a macro solution? A formula solution is fairly easy to achieve:

Columns B:J:
=COUNTIFS(Data!$K:$K,'Data As per Template'!$A2,Data!$E:$E,"<="&INT(RIGHT('Data As per Template'!B$1,2)),Data!$E:$E,">"&INT(LEFT('Data As per Template'!B$1,FIND("-",B$1)-1)))

Col K:
=COUNTIFS(Data!$K:$K,'Data As per Template'!$A2,Data!$E:$E,">"&INT(RIGHT('Data As per Template'!K$1,2)))

Col M:
=COUNTIFS(Data!$K:$K,'Data As per Template'!$A2,Data!$H:$H,"Self")

[saravanan1981]

Dear Sintek,
The in template sheet only first row (Age Band) is constant row.

The number in First column (SI) is not constant. The number will change as per the data. Accordingly SI rows to be adjusted.

For example I have added another SI slab of Rs.3000000. Hence rows will change accordingly. Please refer attachment.

[jindon]

Please Login or Register  to view this content.

[saravanan1981]

Dear Jindo,
Thank you very much for your reply.
I wants to create number of rows equal to number of Sum insured in template automatically.

e.g. If SI is only 50000, 75000, 100000, 150000 then create only 4 rows.

IF SI as below, then create 12 rows, because there 12 SI below. which is not happening in attachment.

50000
75000
100000
150000
250000
275000
350000
400000
600000
2500000
3000000
6000000

for e.g in attachment I ave crated one more SI of Rs.3500000 and that particular row is not created.

In short SI will be variable from data to data

[sintek]

Please Login or Register  to view this content.

[saravanan1981]

it is working correct.

But creates two total row. Please refer attachment.

Also Borders are not coming till last row and last column.

[sintek]

See attached...

[saravanan1981]

perfect.... perfect..... perfect.... thanks a ton.

[sintek]

This incorporates all shading and borders...
Thanks for rep +

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[saravanan1981]

Dear Sintek,
One Small Help again. I have changed the template little bit.

Change is only in age slab. Can you please change the code accordingly.

Please refer attachment.

also can you please guide me which line of code to be changed if I want to manipulate in age band.
Suppose if I want to change the age band as per my requirement.

[sintek]

Try this...
With the change in age band...What do you mean...Adding more/less columns?
If so, the red pieces need to be edited.

Please Login or Register  to view this content.

[jindon]

.......................

[saravanan1981]

Dear Jindon,
Can you please provide me the code as it is protected. I wants to copy the same in some other sheet

[sintek]

This is greek to me....I just so wish Master Jindon could teach or perhaps refer to explanations of his code...

Please Login or Register  to view this content.

[jindon]

Some idio* seem to know the way to crack the password.

Please Login or Register  to view this content.

[sintek]

@ jindon

Please be so kind and point me to a link so that I may understand this amazing code....Pretty please.