Thank you Xor,
I tried implementing it into my spreadsheets however it caused problems, I may have messed up the equation while doing it
Also on your equation, it does not eliminate the second Apple but created a duplicate that was located in CA.


Your output was

ApplesCA - 8
ApplesNY - 3
BananasCA - 4
ApplesCA - 8
PearsCA - 2