Allocating / Apportioning Challan Amounts to Different Receipts
The attached file contains three Tables
1. Challan : This table contains the data for the Tax deposited via Challan.
2. Receipts : This table contains the data for the Amounts received against which the tax has been deposited.
3. ChallanAllocation : This table contains the mapping (from Challan Table) of challan number and date to all the amounts (in the Receipts Table)
My Request involves creation of ChallanAllocation like Information in an automated way
Here one challan amount might be sufficient to cover multiple receipts
or
One receipt might be sufficient to cover multiple challans
Considering the Receipts Table as base We need to Map (allocate and / or apportion) the Challan amount to all the receipts on FIFO basis, without leaving any balance in challan, and create a new table (Structured Data Table or simple data range). We might need to split the single challan across various receipts OR split the single receipts across several challans and might need to ADD more rows to accommodate such splits.
Notes:
1. Challan date and Receipt Date need to be in ascending order, but Challan Date may be before or after the Receipt Date. I will enter the data in ascending order but appreciate if the proposed solution can sort the data based on ID and CIN Date / Receipt Date.
2. I have given data for only Two IDs (i.e., A01, A02) but there are multiple IDs for which Data is maintained in the same table.
3. Total Challan Amount and Total Receipt Amount is matching in case of A01 but might be different in other cases (like in A02). In case Total Challan Amount is MORE than the Total Receipt Amount, the balance can be ignored. But if the Challan Amount is LESS than the Receipt Amount, the additional row should appear with “Short” in CIN Column in ChallanAllocation Table (like in Cell Q49)
Any solution (VBA, non VBA) to solve this and create the desired data is EQUALLY appreciated.
As I am not very good in VBA, I am trying my best to find a Non VBA solution using Dynamic Array Functions, but I am struggling to add new rows automatically to the existing table.
In real scenario, I am going to use three different sheets (Challan, Receipts, ChallanAllocation) and will be copying the new data on the Challan and Receipts sheet, hoping that the solution will create the desired data in Sheet ChallanAllocation. Here for the sake of easy matching, I have kept the data on one sheet.
and to all the Esteemed Experts
The attached file contains three Tables1. Challan : This table contains the data for the Tax deposited via Challan.2. Receipts : This table contains the data for the Amounts received against which the tax has been deposited.3. ChallanAllocation : This table contains the mapping (from Challan Table) of challan number and date to all the amounts (in the Receipts Table) My Request involves creation of ChallanAllocation like Information in an automated way Here one challan amount might be sufficient to cover multiple receiptsorOne receipt might be sufficient to cover multiple challans Considering the Receipts Table as base We need to Map (allocate and / or apportion) the Challan amount to all the receipts on FIFO basis, without leaving any balance in challan, and create a new table (Structured Data Table or simple data range). We might need to split the single challan across various receipts OR split the single receipts across several challans and might need to ADD more rows to accommodate such splits. Notes:1. Challan date and Receipt Date need to be in ascending order, but Challan Date may be before or after the Receipt Date. I will enter the data in ascending order but appreciate if the proposed solution can sort the data based on ID and CIN Date / Receipt Date.2. I have given data for only Two IDs (i.e., A01, A02) but there are multiple IDs for which Data is maintained in the same table.3. Total Challan Amount and Total Receipt Amount is matching in case of A01 but might be different in other cases (like in A02). In case Total Challan Amount is MORE than the Total Receipt Amount, the balance can be ignored. But if the Challan Amount is LESS than the Receipt Amount, the additional row should appear with “Short” in CIN Column in ChallanAllocation Table (like in Cell Q49) Any solution (VBA, non VBA) to solve this and create the desired data is EQUALLY appreciated. As I am not very good in VBA, I am trying my best to find a Non VBA solution using Dynamic Array Functions, but I am struggling to add new rows automatically to the existing table. In real scenario, I am going to use three different sheets (Challan, Receipts, ChallanAllocation) and will be copying the new data on the Challan and Receipts sheet, hoping that the solution will create the desired data in Sheet ChallanAllocation. Here for the sake of easy matching, I have kept the data on one sheet.@Sergei Baklan@Peter Bartholomew@lori_m@Chris_Grossand to all the Esteemed Experts Read More
