0

The tables will have multiple entries for amount debited and credited details. It also has 5% debit and credit and 1% debit and credit entries as well. Data includes unique flag for districts, Date which amount debited or credited, description where it is 2% debit or 2% credit, transaction (debit or credit)amount, district.

First debit entry will be made and when amount received credit entry will be made. Now, I need to get only non-matching rows for each flag say when T1 flag has two entries for 2% debit and one entry for 2% credit, then I need to get only one entry for 2% debit matching the flag and amount to get outstanding amount for that flag so I only get to know the outstanding debit amount which needs to be credited. If there is no credit entry for any debit flag, it needs to list all debit entries.

Main source Table:

Flag Date Description Transaction Amount District
T2 24/04/2020 5% Debit Debit 100000 Balaghat
T2 16/03/2021 2% Debit Debit 26000 Balaghat
T2 16/03/2021 2% Credit Credit 26000 Balaghat
T3 14/11/2020 2% Debit Debit 18600 Mandla
T10 22/03/2021 2% Debit Debit 50000 Mandsaur
T10 22/03/2021 2% Debit Debit 50000 Mandsaur
T10 22/03/2021 2% Debit Debit 50000 Mandsaur
T10 15/04/2021 2% Credit Credit 50000 Mandsaur

Expected results:

Flag Date Description Transaction Amount District
T2 24/04/2020 5% Debit Debit 100000 Balaghat
T3 14/11/2020 2% Debit Debit 18600 Mandla
T10 22/03/2021 2% Debit Debit 50000 Mandsaur
T10 22/03/2021 2% Debit Debit 50000 Mandsaur

Please refer "Copy of Transactions" tab, I31 row table for "2% Debit Status". "Outstanding 2% Debit" should present only when there is value else not row needed. Zeros from M32 to M52 is not needed as we have received the amount. But I am expecting each entry for outstanding debit amount.

Sample spreadsheet: https://docs.google.com/spreadsheets/d/162guBnG2JBL3uuWuaSN_4xE-NAsGWGwV9ckyKDmmJn4/edit?usp=sharing

Also Flag order needs to be T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11 instead of T1, T10,T11,T2,T3

enter image description here

2
  • Your question requires that the reader must open your spreadsheet in order to consider the data and understand your question. This is inappropriate - your question should be capable of standing alone and describe your scenario and the criteria by which transactions are matched. It should provide an example of your transactions, and include an example of an unmatched transaction. The information described on the "Copy of Transactions" tab should be included in the body of your question. Would you please edit your question to provide this information. – Tedinoz May 12 at 4:46
  • Thanks, I have updated my question with more details. Please advise. – RamKumar Rangasamy May 12 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.