Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing 2 columns
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:D;MATCH(A:A; B:B;0))) But why D: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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing 2 columns
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" wrote: 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:D;MATCH(A:A; B:B;0))) But why D: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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing 2 columns
I also read something about giving different colors...to a new and a deleted
number....how can I do that? "mariekek5" wrote: 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:D;MATCH(A:A; B:B;0))) But why D: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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing 2 columns
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" wrote: 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" wrote: 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:D;MATCH(A:A; B:B;0))) But why D: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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing 2 columns
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" wrote: 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" wrote: 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" wrote: 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:D;MATCH(A:A; B:B;0))) But why D: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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing 2 columns
Thanks John. It dees work indeed!
"John Moore" wrote: 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" wrote: 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" wrote: 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" wrote: 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:D;MATCH(A:A; B:B;0))) But why D: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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing 2 columns
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 "John Moore" wrote: 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" wrote: 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" wrote: 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" wrote: 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:D;MATCH(A:A; B:B;0))) But why D: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing to columns | Excel Discussion (Misc queries) | |||
Comparing Two Columns | Excel Discussion (Misc queries) | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
Comparing columns | Excel Discussion (Misc queries) | |||
comparing columns | Excel Discussion (Misc queries) |