If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Compare numbers in to raws
I'm using Excel 2003
Can anybody help with programming this: I have a column of numbers in F20:F33 I would like to check against an other colums of numbers in A20:A23. All numbers are phone numbers - no parentheses. If a number in F match a number in A, it should return a "Y" (for yes) in the adressfield to the right for this number (say G28). I tried to modify the IF COUNT IF in the thread shown in "Programming" on the 10.02.2010 without succes. The above ranges are arbitrary. The real ones are much much longer. Thank you in advance. |
#2
|
|||
|
|||
Compare numbers in to raws
use editFIND
record a macro while doing and then clean it up. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stig LA" Stig wrote in message ... I'm using Excel 2003 Can anybody help with programming this: I have a column of numbers in F20:F33 I would like to check against an other colums of numbers in A20:A23. All numbers are phone numbers - no parentheses. If a number in F match a number in A, it should return a "Y" (for yes) in the adressfield to the right for this number (say G28). I tried to modify the IF COUNT IF in the thread shown in "Programming" on the 10.02.2010 without succes. The above ranges are arbitrary. The real ones are much much longer. Thank you in advance. |
#3
|
|||
|
|||
Compare numbers in to raws
To just check if one in F is also in A, formula (for cell G20) would be:
=IF(COUNTIF(A$20:A$33)0,"YES","NO") but if there is already some text in G20 and you want to add the "YES/NO" to that text then something like this: ="original text here like street address" & " Phone: " & IF(COUNTIF(A$20:A$33)0,"YES","NO") "Stig LA" wrote: I'm using Excel 2003 Can anybody help with programming this: I have a column of numbers in F20:F33 I would like to check against an other colums of numbers in A20:A23. All numbers are phone numbers - no parentheses. If a number in F match a number in A, it should return a "Y" (for yes) in the adressfield to the right for this number (say G28). I tried to modify the IF COUNT IF in the thread shown in "Programming" on the 10.02.2010 without succes. The above ranges are arbitrary. The real ones are much much longer. Thank you in advance. |
#4
|
|||
|
|||
Compare numbers in to raws
To explain the formula a bit: The heart of it is the COUNTIF() portion.
COUNTIF() needs 2 things; a list of cells to look through, and an indication of what to look for in that list. By writing the address for column A as A$20:A$33, it keeps the "cells to look through" constantly pointing to the entire list no matter where we move the formula to. And by writing the "what to look for" part as F2 (notice no $ in it), it allows the 2 to increment as you fill the formula down the sheet. So in cell G2, the formula would look like =IF(COUNTIF(A$20:A$33,F2)1,"YES","NO") and after you copy/fill it down to F3, it looks like =IF(COUNTIF(A$20:A$33,F3)1,"YES","NO") Of course you can change the row numbers in column A as required; for that matter, if your list may get longer, you can even write the formula as =IF(COUNTIF(A:A,F3)1,"YES","NO") and never have to worry about which rows in column A are involved at all. "Stig LA" wrote: I'm using Excel 2003 Can anybody help with programming this: I have a column of numbers in F20:F33 I would like to check against an other colums of numbers in A20:A23. All numbers are phone numbers - no parentheses. If a number in F match a number in A, it should return a "Y" (for yes) in the adressfield to the right for this number (say G28). I tried to modify the IF COUNT IF in the thread shown in "Programming" on the 10.02.2010 without succes. The above ranges are arbitrary. The real ones are much much longer. Thank you in advance. |
#5
|
|||
|
|||
Compare numbers in to raws
On Feb 12, 7:35*am, Stig LA Stig
wrote: I'm using Excel 2003 Can anybody help with programming this: I have a column of numbers in F20:F33 I would like to check against an other colums of numbers in A20:A23. All numbers are phone numbers - no parentheses. If a number in F match a number in A, it should return a "Y" (for yes) in the adressfield to the right for this number (say G28). I tried to modify the IF COUNT IF in the thread shown in "Programming" on the 10.02.2010 without succes. The above ranges are arbitrary. The real ones are much much longer. Thank you in advance. Another approach =IF(ISNUMBER(MATCH(F20,$A$20:$A$33,0)),"yes","no") adjust ranges accordingly. regards |
Thread Tools | |
Display Modes | |
|
|