Excel – Find items in one Excel column that are not in another column and put them in a new column

microsoft-excel-2007

What I want to do

Find Items in one column (ColA) that are not in another column (ColB). What to do when I want the result not being highlighted, but have it in another column (ColC) without blank rows.

Example

ColA - ColB - ColC    
1 - 1 - 4
3 - 2 - 8
10 - 3 - 10
4 - 5 - "" 
5 - 7 - ""    
8 - 6 - ""
9 - 9 - ""

What did I try yet

Until now I succeeded to have the following result. I do this with the following formula in column C:

=IF(IFERROR(MATCH(A2;B$2:F$300;0);"")<>"";"";A2)

Result:

ColA - ColB - ColC
1 - 1 - ""
3 - 2 - ""
10 - 3 - 10
4 - 5 - 4     
5 - 7 - "" 
8 - 6 - 8
9 - 9 - ""

But I want to avoid the blank cells in Col C.

I tried the formula I found in one of the answers on this site:

IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")

… but this does not work. Maybe I adapt this formula in a wrong way…?

Best Answer

You will have to use an array formula. Assuming your data in ColA and ColB is in rows 1 to 10, select rows 1 to 10 of ColC, then input the following formula in the formula bar:

=IFERROR(SMALL(IF(ISNA(MATCH(A1:A10,B1:B10,0)),A1:A10,""),ROW()),"")

Store it as an array formula by pressing Ctrl+Shift+Enter (on Windows) / Cmd+Shift+Enter (on OS X) – if you did it right, the formula will display enclosed in braces ({}) and look identical in all cells. As applied to your example data, this is what you get:

screenshot of the formula used on the posted example

Note there are a few gotchas when working with array formulas (they can only be edited by editing the whole range, cannot be shrunk, can only be expanded by manually editing the formula … see the article I linked to for some details, section “Rules for entering and changing array formulas”).

Related Solutions

Excel – How to Find Items in One Column Not in Another

Select the list in column A
Right-Click and select Name a Range...
Enter "ColumnToSearch"
Click cell C1
Enter this formula: =MATCH(B1,ColumnToSearch,0)
Drag the formula down for all items in B

If the formula fails to find a match, it will be marked #N/A, otherwise it will be a number.

If you'd like it to be TRUE for match and FALSE for no match, use this formula instead:

=ISNUMBER(MATCH(B1,ColumnToSearch,0))

If you'd like to return the unfound value and return empty string for found values

=IF(ISNUMBER(MATCH(B1,ColumnToSearch,0)),"",B1)

Excel – In Excel I need to find data matches between two arrays – one horizontal and one vertical

YMMV, but View -> Macros, add a macro. Try this (change the cell references as necessary):

Dim data, reference As Range

Set reference = Worksheets("Sheet2").Range("A1", "A42")
Set data = Worksheets("Sheet1").Range("B2", "F6")

For Each dataCell In data
    For Each referenceCell In reference
        If dataCell.Value = referenceCell.Value Then
                Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
        End If
    Next
Next

[Edit: If this works, you can speed it up a bit by stopping searching a row when you've found a match. (Assuming only one possible match per row). e.g.:

Sub newtest()
    Dim data, reference As Range
    Dim skipsome As Boolean
    skipsome = False

    Set reference = Worksheets("Sheet2").Range("A1", "A7")

    Set data = Worksheets("Sheet1").Range("B2", "F6")
    For Each dataCell In data
        For Each referenceCell In reference
            If dataCell.Value = referenceCell.Value Then
                    Worksheets("Sheet1").Cells(dataCell.Row, 1).Value = dataCell.Value
                    skipsome = True
                    Exit For
            End If

            If skipsome = True Then
                skipsome = False
                Exit For
            End If
        Next
    Next
End Sub

Just on your 5 row test data this knocks the cell comparison tests down from 175 to 132.] [Edit2: Make the code work]

Best Answer

Related Solutions

Excel – How to Find Items in One Column Not in Another

Excel – In Excel I need to find data matches between two arrays – one horizontal and one vertical

Related Question