|
| 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
November 13th, 2009, 04:56 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula help
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 
|
|
#2
November 13th, 2009, 05:47 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula help
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
|
|
#3
November 13th, 2009, 06:30 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula help
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 .
|
|
#4
November 13th, 2009, 06:40 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel formula help
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 .
|
|
#5
November 16th, 2009, 01:49 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas through multiple cells
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
|
|
#6
November 16th, 2009, 01:50 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas through multiple cells
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
|
|
#7
November 16th, 2009, 01:50 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cell copying
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
|
|
#8
November 16th, 2009, 02:41 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cell copying
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
|
|
#9
November 16th, 2009, 04:08 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel cell copying
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
|
|
#10
November 16th, 2009, 06:02 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas through multiple cells
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
