[Solved] Detect missing transactions in lists

[abrogard]

I want to see if all the values in one list are present in the other.
bank reconciliation style.
the totals are not the same.
so put up two lists in a spreadsheet: one the bank's list of transactions, the other your own record.
then look for transactions in one that are not in the other.
with something like this maybe which searchs for date and amount:

Code: Select all

=IF(AND(SUMPRODUCT((A:A=G8)*1) > 0, SUMPRODUCT((D:D=F8)*1) > 0), "Match", "No Match")

run that for one list and it will tell if all in that list find a match in the other.
run it amended to suit for the other list and it will tell you if that is all matched.
sounds great and looks great.
but I have a list of 1300 items and a list of 1296.
the list of 1300 claims to be totally matched in the smaller list.
obviously impossible.
it must be doing some 'double matching', counting the same thing twice or something.
so i think to have it remove from the lists the matching pairs once found. steadily reducing the list so it cannot use the same values twice.
but that's where I am stopped. I cannot find a way to do that.
I cannot find a matching function that tells me where it found the match.
I cannot code it up, unfortunately. lost my coding skills some years ago.
but if necessary i will try if someone could perhaps give me a leg up to get me started?
Or are there better ways?

[Villeroy]

Professionals use a database for tasks like this. Spreadsheets are poor database surrogates.

[MrProgrammer]

… so put up two lists in a spreadsheet: one the bank's list of transactions, the other your own record, then look for transactions in one that are not in the other.

I would use Format → Conditional formatting → Formula is to highlight cells in each list which are not in the other. This is presumably a small set of rows. The conditional format style can set the background color for those few cells, making visual identification of unmatched items easy. The MATCH function will probably be useful for this task. Read about conditional formatting in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum. There are many.

=IF(AND(SUMPRODUCT((A:A=G8)*1) > 0, SUMPRODUCT((D:D=F8)*1) > 0), "Match", "No Match")

MATCH gives a position. SUMPRODUCT gives a count. Your formula looks like one for Excel. If you want to use SUMPRODUCT instead of MATCH you will be able to use a simpler formula in Calc. Excel suffers from the bad decision to make logical values a separate data type, forcing users to employ conversion workarounds like multiplying by one (convert boolean to numeric) and comparing with zero (convert numeric to boolean). This is the reason many bogus Excel formulas use the silly double negation operator and extra parentheses (convert boolean to numeric). To count how many of B5:B9 have a length larger than two:
• Excel requires clumsy =SUMPRODUCT(--(LEN(B5:B9)>2))
• Calc can use simple =SUMPRODUCT(LEN(B5:B9)>2)

If you need any additional assistance attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet document.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[keme]

Your signature indicates that you are using OpenOffice v.4.0.1. OpenOffice Calc does not support the use of neither comma for parameter separation nor the "A:A" full column reference notation. I am assuming that you are using LibreOffice Calc.

Code: Select all

=IF(AND(SUMPRODUCT((A:A=G8)*1) > 0, SUMPRODUCT((D:D=F8)*1) > 0), "Match", "No Match")

Your formula checks whether the G8 value exists somewhere in column A, and the F8 somewhere in column D. It does not ensure that the find is in the same row for both columns.

but I have a list of 1300 items and a list of 1296.
the list of 1300 claims to be totally matched in the smaller list.
obviously impossible.
it must be doing some 'double matching', counting the same thing twice or something.

Yes. Most likely partial duplicates (since your formula performs partial matching, as detailed above) but possibly also empty rows may cause this. The exact reason may be apparent if you are able to supply a sample (as per MrProgrammer's comment).

I assume that you need to match column A and D values within the same record (aka. row) instead of "independently". The simplest way to do this in a spreadsheet is to concatenate the key values with a rarely used character in between (assuming both key values are text; this will often work with numeric values also).

Try:

Code: Select all

=IF(SUMPRODUCT(TRIM(A:A&";"&D:D)=TRIM(G8&";"&F8))=1;"Match";"Mismatch")

"Mismatch" will be returned if there are no matches or if there are multiple matches. "Match" if there is exactly one matching record.

Note that with the number of dependencies and search operations involved with this spreadsheet-for-a-database strategy, each edit may cause a long apparent freeze, while the entire model recalculates. If you have multiple edits, you may want to temporarily disable automatic recalculation while editing. See menu Data - Calculate.

For added robustness I like to use the TRIM function to cut excess whitespace. With manual user input, some people will press the space bar before (to "clear the field") or after entry (to "finish the word/sentence/line"). The use of TRIM() in this formula seeks to remove such entry error.

If leading/trailing/double spacing is a valid distinguishing element in your case (as rarely happens), the TRIM() step is of course not desirable.