SOLVED - Dynamic/spill array formula to show result finite amount of times based on criteria

[Kendethar]

I have a log that I'm comparing to my bank and have solved all the issues so far, except for how to state duplicated logs.

In my list, my formula in column E will state "Mismatch" if the amount of occurrences in column B is not the same amount of occurrences in column D (or if an item doesn't exist), for each item.

In this picture, -59.82 withdrew once from the bank but I logged it twice in my tracker by accident, thus 1 of the 2 "Mismatch"'s are true but not both of them.

I need my formula to show "Mismatch" for the number of times it was duplicated. (E.g. $50 appears twice in my bank statement but appears 5 times in the tracker logs, meaning 3 of 5 are mismatches.)

Formula (column E):

=IF(D3:D1000="","",IF(COUNTIF(B3:B1000,D3:D1000)=COUNTIF(D3:D1000,D3:D1000),"✓","Mismatch"))

Notes:

• All arrays shown are dynamic arrays (or spills).

• "List of Mismatches" lists the value(s) of item(s) from column B/D where "Mismatch" is to the right of it.

• Filter from the Data tab is turned on for columns B:E, there is no table inserted.

I would highly appreciate any solution to this. Thank you!

[#2 · Accepted solution]

Update:

I figured it out.

The idea I had was: everything below/above (below in my case) each cell will see if "Mismatch" with the existing value next to it already exists. So, the first thing I had to do was count the occurrences of the value under Bank and under Tracker then subtract for the difference. Then, the difference was how many times "Mismatch" would appear for the value, in the array. Finally, for it to be a dynamic array (spill), I used the SEQUENCE function to increment 1 to the start of the range reference in the COUNTIF function used to check all below. 

It's not 100% accurate as far as which one(s) weren't logged right (being the order) but in my case, I'm not concerned with the dates as long as they're all from the 1st of the month to the current date - is the match of each value that matters.

Formula (column G):

=IFERROR(IF($E$3:$E$1000="","",IF(COUNTIF($B$3:$B$1000,$E$3:$E$1000)=COUNTIF($E$3:$E$1000,$E$3:$E$1000),"✓",IF(COUNTIF(INDIRECT("E"&3+SEQUENCE(998,1,,1)&":$E$1001"),$E$3:$E$1000)<COUNTIF($E$3:$E$1000,$E$3:$E$1000)-COUNTIF($B$3:$B$1000,$E$3:$E$1000),"Mismatch","✓"))),"")

Formula (for Flash Fill):

=IF(D3="","",IF(COUNTIF($B$3:$B$1000,D3)=COUNTIF($D$3:$D$1000,D3),"✓",IF(COUNTIF(D4:$D$1001,D3)<COUNTIF($D$3:$D$1000,D3)-COUNTIF($B$3:$B$1000,D3),"Mismatch","✓")))

Note:

The "ref_text" in the INDIRECT function is entered as a string thus does not automatically account for column/row reference updates and will need to be updated manually.

Attached is a sample .xlsx workbook I made so y'all can update the formula to your needs. Feel free to share your tweaks or suggestions for better ways to accomplish this.