creating a column from comparing two columns

[maafaa _1]

I am trying to compare two columns of data and create a third column that contains the differences.  I have been able to find a way but each column has over 400,000 entries and it is very time consuming.

Basically I am trying to compare column A to column B and populate column C with values that appear in column B but not column A.  Any help would be greatly appreciated!

[#2]

Hi,

Please populate column C with this formula:

=IF(ISNUMBER(MATCH(B1,A:A,0)),"",B1)

This formula will show you the value if it's not in column A, otherwise, it will leave the cell blank.

Hope that helps
Haytham

[#3]

If extract from B into separate list values which are not in A that could be done with

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,1/ISNA(MATCH($B:$B,$A:$A,0))*(ROW($B:$B)-ROW($B$1)+1)/($B:$B<>""),1+ROW($C12)-ROW($C$1))),"")

for data structured like

if drag it down till first empty cell appears (first file attached).

But on four hundred thousand rows it could take years to recalculate.

With Power Query (aka Get&Transform) it will much more effective, If convert range with data in A and B into the table, just Ctrl+T (here left table with 400000 rows is generate by helper query)

and apply query to it like

let
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    MergeWithItself = Table.NestedJoin(Source,{"B"},Source,{"A"},"Source",JoinKind.LeftAnti),
    RemoveUnused = Table.SelectColumns(MergeWithItself,{"B"}),
    RenameHeader = Table.RenameColumns(RemoveUnused,{{"B", "B not in A"}})
in
    RenameHeader

result will be the right table (another file attached).

Query loads the table from Excel sheet and left anti joins (merge) it with itself on column B against column A, rest is cosmetic.