I have two sheets
1 Ad-hoc - which tells the lines to be added
2 RDR - which tells what lines are already present
I want to check if the lines present in Ad-hoc are existing in RDR. The RDR has configurations in range sometimes
example
in line 3 of RDR product low is 0000 and product high is ZZZZ
in line 5 of RDR Acct low is HB000 and Acct high is HBZZZ
in line 7 of RDR project low is 1AUD and project high is 1EUR
When we compare lines in Ad-hoc sheet with RDR if the values are exact match or fall in range between low and highs then we have to assign them "Present" if values do not match exactly or fall in range we assign them "Absent"
Explanation
We first match the lines on Ledger name, Company, Type and then find if rest of the column are exact match or in range
Line 2 in Ad-hoc sheet matches exactly with line 1 in RDR so we say "Present"
Line 3 in Ad-hoc falls in range. project, product =0001 they falls in the range 0000(project low, product low) and ZZZZ(project high, product high) so we say "Present"
Line 4 in Ad-hoc falls in range. Acct low =10122 falls in range 10122 to 10179 in RDR and even project = 12AB falls in 0000 to ZZZZ in RDR so we say "Present"
Line 5 in Ad-hoc falls in range. Acct low =10123 falls in range 10122 to 10179 in RDR and even project = 12AB falls in 0000 to ZZZZ in RDR so we say "Present"
Line 6 in Ad-hoc falls in range. Acct low =HB012 falls in range HB000 to HBZZZ in RDR so we say "Present"
Line 7 in Ad-hoc though Ledger name, Company, Type match rest of columns do not match with any of the lines so we say "Absent"
Line 8 in Ad-hoc falls in range. project = 1AUE falls in range 1AUD to 1EUR in RDR so we say "Present"
Line 9 in Ad-hoc though Ledger name, Company, Type match rest of columns do not match with any of the lines so we say "Absent"
Line 10 in Ad-hoc falls in range, Acct low = 10123 falls in range 10122 to 10179 in RDR so we say present
