how to remove numbers in a column that include numbers in another column?

[FZ1]

I have 2 columns of numbers, I'll call them column A&B. Some numbers are in both columns. I want to remove any numbers in column B that are also in Column A. It seems easy enough but I can't figure it out. I don't care if it's in excel or a macro, probably will end up as part of a macro that i'll put in a loop because the length of the columns varies

using the numbers below, the final result should be column B except with 1,7 & 21 removed

Column A:
1
4
7
9
13
16
19
20
21

Column B
1
2
5
7
21
24
29
34
36
38
45

[alansidman]

A power query solution

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"Column A:"}, T2, {"Column B"}, "Table2", JoinKind.RightAnti),
    #"Expanded Table2" = Table.ExpandTableColumn(MQ, "Table2", {"Column B"}, {"Column B"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Column A:"})

in
    #"Removed Columns"

Excel 2016 (Windows) 64 bit

	A	B	C	D
1	Column A:	Column B		Column B
2	1	1		2
3	4	2		5
4	7	5		24
5	9	7		29
6	13	21		34
7	16	24		36
8	19	29		38
9	20	34		45
10	21	36
11		38
12		45

Sheet: Sheet1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[FZ1]

Thank you, I have never heard of power query, I'll do some research and try to figure this out. Wish there was a simpler way

[alansidman]

It is rather simple if you give yourself a chance to spend time with it. It can all be achieved with point and click in the User Interface. No coding is required to get started.

[daboho]

Sub perhab()
Dim a, b, c, i As Long, dic As Object
a = Range("A1:A" & Cells(Rows.Count, 1).End(3).Row).Value
b = Range("B1:B" & Cells(Rows.Count, 2).End(3).Row).Value
ReDim c(1 To 2)
c(1) = a: c(2) = b
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To 2
 For j = 1 To UBound(c(i))
   If Not dic.Exists(c(i)(j, 1)) Then
      dic.Add c(i)(j, 1), Array(IIf(i = 2, i, ""), c(i)(j, 1))
   Else
      dic.Remove c(i)(j, 1)
   End If
 Next j
Next i
With Range("C1")
  .Resize(dic.Count, 2).Value = Application.Index(dic.Items, 0, 0)
  .EntireColumn.SpecialCells(2, 1).Resize(, 2).ClearContents
End With
End Sub

ANOTHER WAY

Sub anotherWay()
Dim a, i As Long
With Range("A1:A" & Cells(Rows.Count, 1).End(3).Row)
  a = .Value
    For i = 1 To UBound(a)
     If Application.CountIf(Range("B:B"), a(i, 1)) > 0 Then
       a(i, 1) = ""
     End If
    Next i
  .Value = a
  On Error Resume Next:   .SpecialCells(4).Delete Shift:=xlUp: On Error GoTo 0
End With
End Sub

[FZ1]

Sub anotherWay()
Dim a, i As Long
With Range("A1:A" & Cells(Rows.Count, 1).End(3).Row)
  a = .Value
    For i = 1 To UBound(a)
     If Application.CountIf(Range("B:B"), a(i, 1)) > 0 Then
       a(i, 1) = ""
     End If
    Next i
  .Value = a
  On Error Resume Next:   .SpecialCells(4).Delete Shift:=xlUp: On Error GoTo 0
End With
End Sub

This worked, thank you!! people on this board are good

[jindon]

Try

Sub test()
    With [a1].CurrentRegion
        .Columns(2).Value = Evaluate("if(isnumber(match(" & .Columns(2).Address & "," & _
        .Columns(1).Address & ",0)),""#n/a""," & .Columns(2).Address & ")")
        On Error Resume Next
        .Columns(2).SpecialCells(2, 16).Delete xlShiftUp
        On Error GoTo 0
    End With
End Sub