Merge Sheets

rtr1811 · Jul 15, 2024

In the sorted Wardwise Sheet After Each ward (The number in Column D defines Ward)

1. In an Excel file, there are few sheets with their names as numbers and there is also sheet called Wardwise. The rows in the sheets with their names as numbers have to be copied to Wardwise sheet. Beginning of copying has to be from the first row which contains numbers as their value. Hardcoding has to be avoided. End of Copying has to be identified by the row which contains “Net Total”(Case insensitive). In the Wardwise sheet, there is a column called Part No. This has to be read from the sheet name (eg While copying values from sheet 102, this column Part No has to be filled up with 102. Similarly for other Sheets, the Sheet no has to be filled up).

2. The resultant sheet has to be sorted by a Column called Ward No.

3. Any rows in the resultant sheet, which contain the word like “Total” (Case insensitive. May be Sub Total, Grand Total or Net Total) has to be deleted.

4. Insert one row for Summing up the rows above (Merge first three cells and Call this as 1st Ward Sub Total (may be named accodrding to the Ward Number))

5. Below this Main Roll Total, Add a row for Additional Total, if there exists Addition, merge the first three cells of the row ( Name this as 1st Ward Additional Total and sum up the rows just above this row. Hardcoding has to be avoided.)

6. Add a row below this row and call it as Deletion Total if there exists Deletion, merge the first three cells of the row ( Name this as 1st Ward Deletion Total and sum up the rows just above this row. Hardcoding has to be avoided.)

7. Below this Deletion Row Add a row and call it as Ist Ward Grand Total. In this row the formula should be Ist Ward Sub Total + Ist Ward Additional Total - Ist Ward Deletion Total )

Repeat this for each and every ward (Please note that No.of Wards is not fixed and do not hard code it. Also Addition and Deletion may not be available for all the wards. If it is there, a row must be inserted to include in the calculation, otherwise no need to insert a row)

8. After completion of above procedure for all the wards a row at the bottom should be inserted and named it as Net Total. In this row Grand Total of all the wards should be added.

10. Page Break should be set after Grand Total of Each Ward.

11. Any blank rows below the Net Total row have to be deleted.

Chinnakovilankulam 3.08.2021.xlsm