Forum Discussion

maafaa _1's avatar
maafaa _1
Copper Contributor
May 04, 2018

creating a column from comparing two columns

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!

excel
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    May 04, 2018

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 04, 2018

    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.

Resources