Compare data in 2 columns, extract unique values and paste into 3rd column...

Hello all, first post on this forum.

I've been scouring various Excel forums including Ozgrid to find a solution.
I've tried various solutions that have been close to what I want to accomplish, but have not been able to successfully alter them to fit my needs.
Therefore, I'm posting here.

Here's how my daily report is currently set up:

External Internal Differences
123000 123000
234564 234564
345456 345456
456567 378987
565456 456567
654547 524564
788879 565456
865478 654547
745654
788879
865478

The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column.
I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up.
The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:

External Internal Differences
123000 123000 378987
234564 234564 524564
345456 345456 745654
456567 456567
565456 565456
654547 654547
788879 788879
865478 865478

While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average.
I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.

I am open to suggestions on the most efficient and effective way to achieve this, including VBA, macros, formulae and advanced sort, keeping in mind the end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).

Thank you in advance for any and all assistance.

A sample file would be useful.

Assuming that the information you've provided are values and not amounts, you can simply retain them in a single column and create a new column called "Type", which would be either Internal or External. You can then Pivot the data, Value as a row and Type as a column. You can can also bring Value in the Amount section, which would show count by default. All matches would show a "1" under both Types. Where there there isn't a "1" under External, but "1" under Internal, that's your Difference.

External vs Internal.xlsxExternal vs Internal Final Result.xlsx

mcmuney,

Thank you for your response. As I interpret your solution, the resulting Difference would only yield a count of how many values are not matches. What I require is to have the values that are not matches be extracted from the matches, yielding a set of paired values and a set of unmatched values. The purpose of extracting and segregating the unmatched unique values from the matched paired values is to allow the matched values to move forward in the process, while the unmatched unique values to be further analyzed and a reason for the exception to be determined.

I've attached two workbooks as examples. External vs Internal shows how the data is arranged when I receive it. External vs Internal Final Result shows the values unique to the Internal data extracted from the Internal data column and inserted into the Differences column. The Internal data column has become "paired off" with the External data column, which can itself then be extracted and moved along the production pipeline, leaving the Differences data column to be further analyzed.

As previously stated I am open to any solution, although the ideal solution would not require much more than pasting the two data columns into A and B columns in a template workbook and having either a formula, macros or VBA code handle the work.

Originally Posted by Fotis1991

In your attached sample which numbers you want to see in column C?

Thank you Fotis1991,

There are 2 samples attached above. External vs Internal.xlsx shows "before" and External vs Internal Final Result.xlsx shows "after". Column C would contain all the values removed from column B that do not match values in column A.