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.

use edit>FIND
record a macro while doing and then clean it up.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Stig LA" <Stig (E-Mail Removed)> wrote in message
news:66EF5F12-BFC6-4A1F-9713-(E-Mail Removed)...
> 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.
>
>

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.
>
>

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.
>
>

On Feb 12, 7:35*am, Stig LA <Stig L...@discussions.microsoft.com>
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