Need help with an excel formula to highlight differences.

Occasional Visitor

Hello, I am trying to do a compare on two sheets. I am downloading them from https://www.otcmarkets.com/research/stock-screener it is the stock screener for the day. Every day I have a script that goes out and grabs this file. I then manually need to compare them. I only work with a few different states so the first thing I do is I will grab all the Nevada tickers for example for 2 days. I take the 2 sheets and first I need to compare them because there is constantly tickers being added, and removed. 

 

Next I sort them by name. I then use this formula to ensure the ticker in A2 sheet 1 matches A2 in sheet 2 with this =EXACT('sheet1'!A2,A2). 

 

The problem with this is when it hits a false match I have to remove the difference manually. Then I have to take the cell formula above the one I just deleted then I copy the formula again to find the next non match. I do this over and over until they match. 

 

This is too time consuming. I am looking for an easier way to do this. The reason I do this is because once the cells match I then can use formulas like these to see what's what (=D13-'09012020'!D13, ='08182020'!E13-E13, ='08182020'!F13-F13). Basically just comparing numbers. Any help would be appreciated. If you know a easier way to do this even if its another program let me know. Thanks everyone,

1 Reply

@quarinteen 

 

you're scenario is somewhat complex and without sharing the data set no one can begin to help you with that scenario. From what i can gather from your narrative your scenario is the classical Ven Diagram math problem which can have a range of excel function solutions.  In excel its about triangulation. So if the solution provider cannot see the lay of your dataset, their guess is less that good compared to yours.

 

cheers