I have two columns that are in two different worksheets. I have to create a formula that lists the loan numbers of column A in sheet1 that are not in column A in sheet2. Every loan number that is not found should be listed in rows below SQ1. No blank spaces.
Data:
Example Output:
Below is a sketch of the type of solution that you might try to create. In line with the ethos of SuperUser (not a free problem-solving or code-writing service) I have, like your question, not included any of the formulae I have used. Instead, I have listed the worksheet functions that were involved in creating this solution. There are just 4 of them, so not a great deal of research or learning required on your part in getting to understand them. To this list of research/learning items must be added absolute/relative addressing (the $ sign in cell references) as this forms an important part of the solution sketched.
Analysing your problem, there are really two elements to this. The first is simply to identify something in one column (which I have called Col2 in my solution sketch) that does not appear or is unmatched in another column (called Col1). The second is then to list in sequential rows the unmatched items.
The solution sketch uses several helper columns. The column called Match does the bulk of the first element (identifying unmatched items in Col2). The remaining helper columns related to Col2 (0/1 and Cum'tive - short for Cumulative) are part of dealing with the second element. It is possible to combine all three columns into a single helper column (or even dispense with helper columns altogether) but that would defeat the purpose of outlining all the steps involved in the solution sketch.
Creation of the output list, also involves a couple of helper columns on the output list side. These are Num and Col2Pos. Again, it is possible to dispense with one of these (Col2Pos) though again, I have not done this, for the sake of clarity.
The solution sketch is shown below.
If you get stuck following this then please ask a specific question about this showing what you have tried and it is highly likely that you will get some assistance.
Whilst your question makes the point that your versions of Col1 and Col2 are on different worksheets (whereas mine or on the same worksheet) this is a very minor problem - any formula can reference cells on another worksheet just as easily as on the same worksheet. Once you have figured out how the solution sketch might work, it is just a question of moving Col2 (and its helper columns) to another worksheet and adjusting your formulae accordingly. It is even possible to have Col1 , Col2 and the output in three separate Excel workbooks, but probably better to keep things simple for the moment.
