Hi

I am trying to check a cell in excel against a column and see if it is present, if it is then I want a yes or no answer.

All the values are in txt and they are not words, they are codes.

I want to see if a code from cell x appears in column y - then have an answer yes or no.

I need to then copy this formula through 7000 rows checking the next cell (below the one above) with the SAME column fields

Thanks

EggHeadCafe - Software Developer Portal of Choice
Table and Stored Procedure Profile Providers for ASP.NET
http://www.eggheadcafe.com/tutorials...-procedur.aspx

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

--
Biff
Microsoft Excel MVP


<Kevin Rudd wrote in message
om.au...
Hi

I am trying to check a cell in excel against a column and see if it is
present, if it is then I want a yes or no answer.

All the values are in txt and they are not words, they are codes.

I want to see if a code from cell x appears in column y - then have an
answer yes or no.

I need to then copy this formula through 7000 rows checking the next cell
(below the one above) with the SAME column fields

Thanks

EggHeadCafe - Software Developer Portal of Choice
Table and Stored Procedure Profile Providers for ASP.NET
http://www.eggheadcafe.com/tutorials...-procedur.aspx

Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting it and not dragging it down, because although it changes the cell reference automatically (required) it also moves the reverence column down one cell each time, (not required) the column reference is the same set of cells for the whole 7000 reference cells.

thanks





T. Valko wrote:

One way...
13-Nov-09

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

--
Biff
Microsoft Excel MVP


<Kevin Rudd wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Professional ASP.NET Web Services [Wrox]
http://www.eggheadcafe.com/tutorials...spnet-web.aspx

Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting it and not dragging it down, because although it changes the cell reference automatically (required) it also moves the reverence column down one cell each time, (not required) the column reference is the same set of cells for the whole 7000 reference cells.

thanks





T. Valko wrote:

One way...
13-Nov-09

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

--
Biff
Microsoft Excel MVP


<Kevin Rudd wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
EBCDIC Encoding with .NET
http://www.eggheadcafe.com/tutorials...-with-net.aspx

Enter the formula in A1 or whatever column is appropriate.

In Namebox type A1:A7000 and Enter

CTRL + D to copy down.

The row reference will increment but Y:Y will stay constant.


Gord Dibben MS Excel MVP

On Sun, 15 Nov 2009 17:49:36 -0800, Kevin Rudd wrote:

Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting it and not dragging it down, because although it changes the cell reference automatically (required) it also moves the reverence column down one cell each time, (not required) the column reference is the same set of cells for the whole 7000 reference cells.

thanks





T. Valko wrote:

One way...
13-Nov-09

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting it and not dragging it down, because although it changes the cell reference automatically (required) it also moves the reverence column down one cell each time, (not required) the column reference is the same set of cells for the whole 7000 reference cells.

thanks



T. Valko wrote:

One way...
13-Nov-09

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

--
Biff
Microsoft Excel MVP


<Kevin Rudd wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET TraceListener for Debugging
http://www.eggheadcafe.com/tutorials...tener-for.aspx

I don't understand what you mean.

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

I assume you want to check cells starting in X1. If you copy the formula
down the column then X1 will increment to X2, X3, X4 etc. The column
references will not change.


--
Biff
Microsoft Excel MVP


<Kevin Rudd wrote in message
om.au...
Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting it
and not dragging it down, because although it changes the cell reference
automatically (required) it also moves the reverence column down one cell
each time, (not required) the column reference is the same set of cells
for the whole 7000 reference cells.

thanks



T. Valko wrote:

One way...
13-Nov-09

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

--
Biff
Microsoft Excel MVP


<Kevin Rudd wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET TraceListener for Debugging
http://www.eggheadcafe.com/tutorials...tener-for.aspx

IF(COUNTIF(A1:A15,B1)0,"yes","no")


"T. Valko" .gbl...
I don't understand what you mean.

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

I assume you want to check cells starting in X1. If you copy the formula
down the column then X1 will increment to X2, X3, X4 etc. The column
references will not change.


--
Biff
Microsoft Excel MVP


<Kevin Rudd wrote in message
om.au...
Thanks Biff

However how to I copy it through 7000 lines without cutting and pasting
it and not dragging it down, because although it changes the cell
reference automatically (required) it also moves the reverence column
down one cell each time, (not required) the column reference is the same
set of cells for the whole 7000 reference cells.

thanks



T. Valko wrote:

One way...
13-Nov-09

One way...

=IF(COUNT(MATCH(X1,Y:Y,0)),"Yes","No")

Copy down as needed

--
Biff
Microsoft Excel MVP


<Kevin Rudd wrote in message

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET TraceListener for Debugging
http://www.eggheadcafe.com/tutorials...tener-for.aspx

Use this formula in Z1 cell€¦

=IF(ISNA(VLOOKUP(X1,Y:Y,1)=X1),"",IF(VLOOKUP(X1,Y: Y,1)=X1,"YES","NO"))

Now copy the Z1 cell formula and paste it for the remaining cells. The
above formula will check the X1 cell value in Y Column.

If you want to check the X1 and Y1 cell values then use this formula

=IF(ISBLANK(X1),"",IF(X1=Y1,"YES","NO"))

Change the cell reference X1 to your desired cell, if required.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Kevin Rudd" wrote:

Hi

I am trying to check a cell in excel against a column and see if it is present, if it is then I want a yes or no answer.

All the values are in txt and they are not words, they are codes.

I want to see if a code from cell x appears in column y - then have an answer yes or no.

I need to then copy this formula through 7000 rows checking the next cell (below the one above) with the SAME column fields

Thanks

EggHeadCafe - Software Developer Portal of Choice
Table and Stored Procedure Profile Providers for ASP.NET
http://www.eggheadcafe.com/tutorials...-procedur.aspx
.

A small correction in my previous formula

=IF(X1="","",IF(ISNA(VLOOKUP(X1,Y:Y,1)=X1),"NO",IF (VLOOKUP(X1,Y:Y,1)=X1,"YES","NO")))

--------------------
(Ms-Exl-Learner)
--------------------



"Ms-Exl-Learner" wrote:

Use this formula in Z1 cell€¦

=IF(ISNA(VLOOKUP(X1,Y:Y,1)=X1),"",IF(VLOOKUP(X1,Y: Y,1)=X1,"YES","NO"))

Now copy the Z1 cell formula and paste it for the remaining cells. The
above formula will check the X1 cell value in Y Column.

If you want to check the X1 and Y1 cell values then use this formula

=IF(ISBLANK(X1),"",IF(X1=Y1,"YES","NO"))

Change the cell reference X1 to your desired cell, if required.

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Kevin Rudd" wrote:

Hi

I am trying to check a cell in excel against a column and see if it is present, if it is then I want a yes or no answer.

All the values are in txt and they are not words, they are codes.

I want to see if a code from cell x appears in column y - then have an answer yes or no.

I need to then copy this formula through 7000 rows checking the next cell (below the one above) with the SAME column fields

Thanks

EggHeadCafe - Software Developer Portal of Choice
Table and Stored Procedure Profile Providers for ASP.NET
http://www.eggheadcafe.com/tutorials...-procedur.aspx
.