comparing 2 columns

[mariekek5]

Hi,

I see I am not the first one to ask this question, but I see some formula's
which work, but I would like to know why.

I used: =IF(ISNA(MATCH(A:A;B:B;0));"";INDEX(D;MATCH(A:A;B:B;0)))

But why D??

Well, what I would like to do is the following:

I have 2 columns with numbers, column A and column B. Say that B is the new
data/new version of the data. Some numbers will be gone (compared to column
A) and some numbers will be new (compared to column A). Is it possible to
make a fomula that shows me which number is new in column B and which number
is gone in column B, compared to column A?!

Thanks a lot in advance.

Marieke

 

[John Moore]

Try this .... column A relates to Old List, column B relates to New List in
this solution. Seleect the Old List range, click Format, Conditional format,
in the dialog box use the drop down list to choose "Formula Is", enter the
formula, COUNTIF(Newlist,A2)=0, click the format button and choose colour as
required ( say yellow ). Do the same for New List data but choose a different
colour.

 

[mariekek5]

I also read something about giving different colors...to a new and a deleted
number....how can I do that?

 

[mariekek5]

Thanks John for you reply! I dont understand it though, probably I am doing
something wrong...Because nothing happens...although I do exactly what you
said..

 

[John Moore]

Hi Mariekek ,,,, try this ..... name range the old data as "Oldlist" ( say
range A2:A50 ), name range the new data as "New List" ( say B2:B50 ), once
you do this, highlight the Oldlist range, choose format, conditional format,
in the dialog box, choose Formual Is and use this formula
=COUNTIF(Newlist,A2)=0, click format button and specify a colour ( say yellow
), click OK. Now highlight the Newlist range,choose format, conditional
format, in the dialog box, choose Formual Is and use this formula
=COUNTIF(Oldlist,B2)=0, click format button and specify a colour ( say yellow
), click OK. This should work ok.

 

[mariekek5]

Thanks John. It dees work indeed!

Hi Mariekek ,,,, try this ..... name range the old data as "Oldlist" ( say
range A2:A50 ), name range the new data as "New List" ( say B2:B50 ), once
you do this, highlight the Oldlist range, choose format, conditional format,
in the dialog box, choose Formual Is and use this formula
=COUNTIF(Newlist,A2)=0, click format button and specify a colour ( say yellow
), click OK. Now highlight the Newlist range,choose format, conditional
format, in the dialog box, choose Formual Is and use this formula
=COUNTIF(Oldlist,B2)=0, click format button and specify a colour ( say yellow
), click OK. This should work ok.

 

[mariekek5]

Additional question:

I now have the two columns...with the two different colors indicating which
number is not present in the other column.

Every month these numbers (can) change. So every month one need to check
which numbers are added and which are deleted (compared to the OldList).

I am thinking of making a macro. So that when I have the two columns
(OldList and NewList) (in two different documents) the macro will
automatically give the colors.

However, is there a way to lookup the colors as a following step?! So that
in the same macro...I can lookup the colored cells in the newlist, and copy
those cells and add them in de OldList column.

For example:

OldList NewList
1 1
2 3
3 4
4 7
5 8

result:
OldList
1
2
3
4
5
7
8

I hope you can understand what I mean.

Thanks in advance!

Marieke