Macro to Filter and Sum

**saravanan1981** · 07-20-2019, 01:20 PM

Hi,

I have a records in Sheet “data”. The number of records may differ data to data. Hence final row of the data to be considered the last number mentioned in column “A”. Here last record is row number 1618.

I need macro to prepare two reports as mentioned in the attachment i.e. Report -2(Including Parents) and Report -3(Excluding Parents). At present I am making these reports manually

In Sheet “Report -1(Only Self)”
Task -1:- I need to have list of all locations with number of location and total amount with serial number. As mentioned in column A to D. Total in last row. This list is after removing the “Rejected” from the Column Q(Status) and “Spouse”, “Children”, “Parents” from the Column M(Relationship)in sheet “Data”. i.e. We need to filter only “Self” in relationship and “Approved” and “Under Process” in Status.

Task -2:- From the list, I have to create 1st table next to the list which is to be amount wise top 5 locations list. As per the table Column F to I and Row 1 to 8.

Task -3:- From the list, I have to create 2nd table below the above table, which is to be break of number of Relation-wise number of location and total Amount. As per the table Column F to I and Row 10 to 12.

In Sheet “Report -2(Including Parents)”
Task -1:- I need to have list of all locations with number of location and total amount with serial number. As mentioned in column A to D. Total in last row. This list is after removing the “Rejected” from the Column Q(Status) in sheet “Data”.

Task -2:- From the list, I have to create 1st table next to the list which is to be amount wise top 5 locations list. As per the table Column F to I and Row 1 to 8.

Task -3:- From the list, I have to create 2nd table below the above table, which is to be break of number of Relation-wise number of location and total Amount. As per the table Column F to I and Row 10 to 16.

Task -4:- I have to create 3rd table below the above table, which is to be break of Relation-wise number and Amount (but only from the top 5 accumulated locations.). As per the table Column F to I and Row 18 to 24. Total number of table 1st and 3rd should match

In Sheet “Report -3(Excluding Parents)”
Task -1:- I need to have list of all locations with number of location and total amount with serial number. As mentioned in column A to D. Total in last row. This list is after removing the “Rejected” from the Column Q(Status) and “Parents” from the Column M(Relationship) in sheet “Data”.

Task -2:- From the list, I have to create 1st table next to the list which is to be amount wise top 5 locations list. As per the table Column F to I and Row 1 to 8.

Task -3:- From the list, I have to create 2nd table below the above table, which is to be break of number of Relation-wise number of location and total Amount. As per the table Column F to I and Row 10 to 15.

Task -4:- I have to create 3rd table below the above table, which is to be break of Relation-wise number and Amount (but only from the top 5 locations records.). As per the table Column F to I and Row 17 to 22. Total number of table 1st and 3rd should match.

Request you to provide comment against each code so that i can try to learn VBA coding.