Compare list of numbers in two columns

[ksgoodwin]

Hi,

I have two columns of numbers. I want to find the numbers in one
column that are not in the other and vis versa. Ideas?

 

[Gary''s Student]

Use Conditional Formatting

Say we have numbers in column A & B. Click on A1 and:
Format > Conditional formatting... > FormulaIs > =countif($B$1:$B$100,a1)=0
and pick a nice background color.

Copy A1 and pastespecial/formats down the column.

Click on B1 and:
Format > Conditional formatting... > FormulaIs > =countif($a$1:$a$100,b1)=0
and pick a nice background color.

Copy B1 and pastespecial/formats down the column.

 

[Harlan Grove]

Use Conditional Formatting

....

OK if all OP wants to do is look at the non-common values. OTOH, if
the OP wants to use the non-common values, better to use filters. If
one list were in AA3:AA1000 and the other in BB3:BB2000 and columns AB
and BC were blank, enter these formulas:

AB3: =ISNA(MATCH(AA3,$BB$3:$BB$2000,0)) and fill AB3 down into
AB4:AB1000

BC3: =ISNA(MATCH(BB3,$AA$3:$AA$1000,0)) and fill BC3 down into
BB4:BB2000

Then filter AA2:AB1000 on column AB: FALSE filters entries in column
AA which aren't column BB. Similarly, filter BB2:BC2000 on column BC.

 

[helene and gabor]

The macro that does what you asked for:

Sub numbers_in_A_not_in_B()

FirstRow = "1"
FirstCol = "A"
SecndCol = "B"
FirstSheet = "Sheet1"
Dim NONO(100)
Dim ABC(100)
' Present setup answers: All the numbers in Column A not present in column B
' reverse computation needs FirstCol = B and SecndCol = A (B and A with
double quotes)
'program is set up for column length of 18. For K = 1 to 18 and for l=1 to
18 and if atable=18 statements
' below ABC(1)= NONO(1) the loops run to 30, the number of numbers in col.A
not in B
Worksheets(FirstSheet).Activate
Kounter = 0
For k = 1 To 18
atable = 0
MyNumberA = Cells(k, FirstCol).Value
For l = 1 To 18
MyNumberB = Cells(l, SecndCol).Value
If MyNumberA = MyNumberB Then
Exit For
Else
atable = atable + 1
End If
Next l
' MsgBox "k l atable" & k & l & atable
If atable = 18 Then
Kounter = Kounter + 1
NONO(Kounter) = MyNumberA
' MsgBox "atable Kounter MyNumberA " & atable & Kounter &
MyNumberA
End If
Next k

ABC(1) = NONO(1)
m = 2
For j = 2 To 30
If NONO(j) <> NONO(j - 1) Then
ABC(m) = NONO(j)
m = m + 1
End If
Next j

For j = 1 To 30
Cells(j, "D") = ABC(j)
Next j






End Sub

Best Regards,

Gabor Sebo

 

[helene and gabor]

Hi,

I have two columns of numbers. I want to find the numbers in one
column that are not in the other and vis versa. Ideas?

Hello

Given Cols A and B.
What are the numbers that are in A and not B
and in B and not in A?
My steps:
Sort A and B in ascending order
Eliminate duplicates
Sort and Eliminate Duplicates are commands on
Excel 2007 screen.
copy A in a new column, followed by B.
Eliminate duplicates
What is left after the A column are all the numbers in B not in A.

Does that seem logical?


Best wishes


Gabor Sebo