True or False if already in column

I have a list of unique phone numbers in column B. I want to enter a
phone number in cell C2 with an if statement in cell D2 that states
"Already in List" or "Not in List"
Any suggestions?
Thanks
Tim

0
10/18/2005 11:26:23 PM
excel 39879 articles. 2 followers. Follow

4 Replies
387 Views

Similar Articles

[PageSpeed] 31

Lookup()



"Tim" <timh2ofall@comcast.net> wrote in message 
news:1129677983.692566.201670@g14g2000cwa.googlegroups.com...
>I have a list of unique phone numbers in column B. I want to enter a
> phone number in cell C2 with an if statement in cell D2 that states
> "Already in List" or "Not in List"
> Any suggestions?
> Thanks
> Tim
> 


0
warcrimes (66)
10/18/2005 11:45:20 PM
=if(isnumber(match(c2,b:b,0)),"already in list","not in list")



Tim wrote:
> 
> I have a list of unique phone numbers in column B. I want to enter a
> phone number in cell C2 with an if statement in cell D2 that states
> "Already in List" or "Not in List"
> Any suggestions?
> Thanks
> Tim

-- 

Dave Peterson
0
petersod (12004)
10/19/2005 12:28:02 AM
Dave Peterson wrote:
> =if(isnumber(match(c2,b:b,0)),"already in list","not in list")
>
>
>
> Tim wrote:
> >
> > I have a list of unique phone numbers in column B. I want to enter a
> > phone number in cell C2 with an if statement in cell D2 that states
> > "Already in List" or "Not in List"
> > Any suggestions?
> > Thanks
> > Tim
> 
> -- 
> 
> Dave Peterson

Thanks Dave!

0
10/19/2005 12:36:13 AM
On 18 Oct 2005 16:26:23 -0700, "Tim" <timh2ofall@comcast.net> wrote:

>I have a list of unique phone numbers in column B. I want to enter a
>phone number in cell C2 with an if statement in cell D2 that states
>"Already in List" or "Not in List"
>Any suggestions?
>Thanks
>Tim

Something like:

=IF(COUNTIF(B:B,C2)>0,"Already in List","Not in List")


--ron
0
ronrosenfeld (3122)
10/19/2005 12:40:31 AM
Reply:

Similar Artilces:

Negative Number column
I am trying to make a column product negative numbers when I manually enter them in a spreadsheet. I am using a simple sum formula to add across rows (10 columns) and the first column should always be a negative number i.e credit and the rest positive. I am real new to excel so I must be doing something wrong. Assuming the 10 cols are A to J, maybe a simpler way would be to use something like .. In K1: =SUM(B1:J1)-A1 Or, in K1: =SUM(B1:J1,-A1) with K1 copied down Then you could just enter the credit numbers in the first column "as-is", w/o worrying about making these nega...

Assign a Value to a Duplicate Entry Within the Same Column
Within a single worksheet I have thousand-plus rows of data and one of the columns within that worksheet I have a series of numbers. After I've identified the duplicates within that column I want to assign a value based on the first time that number shows up (in this case by I'v sorted by date), so the first duplicate would have a value like "1." I then want to assign the next duplicate(s) sequentially (2, 3, 4, etc.) When finished I would want a new column of data that would have far more "1s" than "2s," more "2s" than "3s,"...

Column and line information in Word
I have my cursor in a Word document and want to see which column it's in and which line it's on. How do I view those details? In 2007, right-click over the Status Bar at the bottom of the Word window, and choose the options you wish to see, including Line Number and Column. Hope this helps DeanH "HS" wrote: > I have my cursor in a Word document and want to see which column it's in and > which line it's on. How do I view those details? ...

This email already exist in this organisation. ID: c10312e7
Hello, We are using SBS 2003 in a small organisation (1 server, 4 workstations) I am trying to add an email address (xxx@yy.nl) to a public folder. This email address previously was assigned to a (the only) distribution group, but was removed there. The public folder xxx gets an email addres of xxx1@yy.nl and if I change it to xxx@yy.nl the error message appears. I can't assign this address back to the group either. If I send an email to this address the notification states that it's refused by the public folder store. Searched all the discussions concerning c10312e7. I have tried th...

Hiding Columns In Excel
When I try to hide too many columns in Excell I get the following message and it won't let me hide the columns, "Cannot Shift Objects off Sheet.". Is there a workaround? What am I doing wrong? The following MSKB article may help: XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?id=211769 Also, Dave Peterson recently posted this advice: '================ Someone with the same problem posted back that their comments were getting resized causing the trouble. Maybe enabling: Tools|Options|View|Comment and ...

hiding columns
Hi, I have a report that has 17 fields. Therefore, it has two pages when I print it off. Is there a way that I can squeeze the 17 fields in one page report? --------------- In Excel, we can hide or show columns when we need them or not. Can I hide/show the fields on report? Thanks Chi Reports in Access are based on queries (ideally), or on tables. If your report is based on a table with 17 fields, you are still not required to include all the fields/controls on your report. A preferable approach would be to create a query that only returns the fields/columns of data you need in you...

Name ranges and columns
I'm using MS Office Excel 2003. I'd like to be able to refer to a column by a name so that I could use 'Price'23 instead of C23 and so forth. I thought that I'd be able to do that by highlighting the column and then using Insert->Name->Define or similar (I can't figure out the difference between the Name->Create, Name->Define and Name->Label variations. In any case, none of them seem to allow the kind of construction I'd like. Do I have the syntax wrong in Price23 (for example ... how to separate column and row parts) or is the whole thing impo...

How do I rearrange (switch) the information in my columns to rows
Copy the data out of Publisher and into Excel. You can swap it out there with the Paste Special (transpose) command. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "larrey" <larrey@discussions.microsoft.com> wrote in message news:35EA8C76-ACA3-44A8-9876-78AE787885B6@microsoft.com... > The dirty way in Publisher, high-light the table, format menu, table, cell properties tab, check *rotate text within AutoShape by 90 degrees* Rotate the entire table 90 degrees. -- Mary Sauer MSFT MVP http://office.m...

Column Graphs #2
I am having trouble with the scaling of my X axis (catagory axis) for a column chart that I have created. It is a series of numbers from 0 to 55. I need the graph to have a linear scale at the bottom and the columns to be distributed accordingly. Any suggestions? Thanks What data are you using for your X values? A column chart does not have a numerical axis. The axis is comprised of a set of labels, or placeholders. Do you need columns, or would another chart type do? Information on chart types and axis types: http://peltiertech.com/Excel/ChartsHowTo/CatVsValueAxis.html http://pu...

How do I install Excel 2000 on my machine with 2003 already there?
I have an old Accounting software package that will not integrate with Excell 2003. I would like to install the old 2000 edition which does work. I have the disks but lack the key code Hi Don It should be possible to get the data into 2003. Can you export as a CSV file? If so, use that and Excel 2003 will read it straight in. Regards Roger Govier Uncle Don wrote: > I have an old Accounting software package that will not integrate with Excell > 2003. I would like to install the old 2000 edition which does work. I have > the disks but lack the key code ...

Marginal Abatement Cost
I want to create a marginal abatement cost (MAC) column chart in Excel 2003. The data I am using is sorted for cost ($/tonne/year). Then I have a marginal abatement (tonnes of CO2-e) and cumulative marginal abatement (also tonnes of CO2-e). On the x axis I will have cost. The y axis shall be marginal abatement, the width of the columns shall represent the marginal abatement, and their location on the axis will be determined by the cumulative marginal abatement. Since the data is sorted for cost, the chart shall show increasing cost with an increasing cumulative marginal abatement. ...

Reassigning tasks to resources not already part of project plan
I am creating projects and assigning the majority of the tasks to team leaders, who are then reassigning to various resources on their teams. Although these resources are members of the team and can assign the tasks to themselves, they don't appear on the drop-down menu for the Reassign Work feature, I"m assuming because I don't have them assigned specifically to a task. Is there a way around this, other than creating a dummy task with ALL the potential resources on it? This is a multi-part message in MIME format. ------=_NextPart_000_00F5_01CAAF4B.B2486F6...

Renaming columns #2
I'd like to rename the A B C D at the top to the name of my columns, o at least leave the column headings at the top of my screen. Does anyone know how -- Message posted from http://www.ExcelForum.com Tools>Options>View, uncheck "Row and column headers". You can insert a row and a column with your own names, but they will not be recognized in formulas the same way as "A1". But you can define row and column names and use the intersection as an address in a formula. The intersection operator is a space. So if you defined the name "Material" for column ...

calculating subtotals for sequential blank cells in 1 column
if i had a column of data and i need to group/subtotal any cells containing zeros or isolated zeros, wat formula do i put in eg. 0 ) 1 ) 0 ) 0 ) subtotal = 4 (so i know it was a group of four cells) 4 5 1 6 5 0 ) 1 ) 0 ) subtotal = 3 6 7 8 0 ) subtotal = 1 basically the data shows steps and rests, and we need to know how long the rest periods are. single steps surrounded by rest (0) need to be counted as rest intervals. Here is one way. Assuming your data starts in A1... In B1 enter =--(A1=0) In B2 enter =IF(A2=0,B1+1,IF(AND(A1=0,A3=...

Macro to find the first cell in a column with different data
Hello, I really could use some of your expert and excellent help with a problem I am having in creating a macro to perform a search in "Column A" to locate a change in the data in that column and then move one column to the right and one row down and type "1" in that cell. I really appreciate your help, and Have a Blessed Holiday Season. Perhaps this might also do it here .. Assuming data is in A1 down, of the structure as below: 1 1 1 2 2 3 3 etc Put in B2: =IF(A2<>A1,1,"") Copy down Kill the formulas in col B with an in-place: Copy > Paste spe...

hiding columns, error message says cannot shift objects off sheet
Hiding columns on a spreadsheet ( which we do frequently). Error message says: 'cannot shift objects off spreadsheet'. WHat does this mean and how do I work around it? Thanks! hi, excel thinks that there is data in column IV. excel by default cannot shift data off the sheet ie beyound column IV or below row 65536. solution. highlight all the column to the right of your data and hit the delete key. that should fix it. >-----Original Message----- >Hiding columns on a spreadsheet ( which we do frequently). Error message >says: 'cannot shift objects off spreadsheet&...

What is the limit size for columns in Excel???
Hi, I am trying to paste some information on a sheet, but I see that columns have a limit size....Is there any "plug in" or some kind of feature to increase the column number??? No. On Thu, 27 Jan 2005 10:49:02 -0800, "Melissa" <Melissa@discussions.microsoft.com> wrote: >Hi, I am trying to paste some information on a sheet, but I see that columns >have a limit size....Is there any "plug in" or some kind of feature to >increase the column number??? Melissa, A sheet is limited to 256 columns. As far as I know there is no way to get around ...

How do I change FALSE values to 0?
How do change values of TRUE or FALSE to 0? what is the formula you are using? "Redleg40" wrote: > How do change values of TRUE or FALSE to 0? =IF(AD7<0,AD7*-1) & =IF(AD13>=0,AD13) "Jambruins" wrote: > what is the formula you are using? > > > "Redleg40" wrote: > > > How do change values of TRUE or FALSE to 0? Put a 0 in for the value_if_false argument. Standard IF syntax =IF(condition,value_if_true,value_if_false), so for your first formula =IF(AD7<0,AD7*-1,0 -- MrShort ----------------------------------------...

How do you get a TRUE refresh of a Pivot table
Oh Pivot tables are so good and boy, do they save on calc time and size of file! BUT.........I notice that if some of the data it relies on is changed, by some magic, it still remembers the old data and, that old data will also be shown in the table unless you physically delete those references using the drop downs. I have found that sometimes you can clear the old references by re-running the wizard backwards as it seems to refresh those references. But that doesn't work in all situations. Is there a way to remove old references automatically AND how is it possible that they still exist?...

sort column by last digit
Lets say I have a column like: 12435G 19995 188884G 188465 277745 etc etc etc ...and I want to sort it so that the ones that end with the letter G are isolated. Is there a way to do this? -- Kepf ------------------------------------------------------------------------ Kepf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30611 View this thread: http://www.excelforum.com/showthread.php?threadid=502969 In a helper column, add =IF(RIGHT(A1)="G","G","") then sort by the helper column. -- HTH Bob Phillips (remove nothere from...

Printing the last column on every page printed.
I want to be able to print the last column of my spreadsheet (Total column) on every page that is printed from this workbook. How can I do this? I'm stumped... Thanks for your help! Are all the last columns in the same location (column X for example)? If yes, you could group all the sheets (click on the first, ctrl-click on subsequent). then select column X File|print|Select (bottom left corner) Do a preview first. But this prints each worksheet (column) on a different sheet. ====== You could have a macro that built a new worksheet and just copy|pastes values| that last column ...

CListCtrl question: dock columns to right
Hi, I have a CListCtrl which is a child of a resizable dialog. (The list control changes its size when the dialog is resizing). I need that the columns of the list will always be aligned to the right. All the columns (accept the first) have a fixed size. In order to achieve this behaviour I catch the WM_SIZE event of the list control and call to SetColumnWidth of the first column. The width that I set to the first column is the width of the list control minus the sum of the width of all other columns. (see code at the end of the question). Generally it works fine, but I get the most strange be...

Move Partial Data from One Column to Another
I am looking for a way to take 5000 lines of data in Excel and take th last 6 digits of the data and move it into another column. For Example: Take the last 6 digits and move to next column Data 1 Data 2 435490870985430984 | 430984 234083209842309820 | 309820 432958739287193872 | 193872 542389034098430945 | 430945 With over 5000 lines to move I need an automated process so that i does not need to be done manually, any help or ideas would be greatl appreciated -- Message posted from http://www.ExcelForum.com Are the numbers of the same length (...

Name a Column
Is it possible to put a name in place of the "A" at the top of the column? How do I do it? Not possible.. -- Regards, Peo Sjoblom "DaveB" <anonymous@discussions.microsoft.com> wrote in message news:2B0655AC-F85B-4C1A-9DAC-F97491F7837F@microsoft.com... > Is it possible to put a name in place of the "A" at the top of the column? How do I do it? Dave. No, is not possible. Alternative.......... Place names/titles in Row 1 then select A2 and Window>Freeze Panes to keep these in view as yoi scroll down. Tools>Options>View. Uncheck "R...

Auto-adjust column width
Hi all Is there a way to set a column width so that it auto-adjusts to the widest entry in the column? And what about the widest numerical entry, excluding word-wrapped cells containing text? I realise that I can double-click on the edge of the column header to achieve most of the desired result passively, but this does not work if the worksheet is protected. Thanks -- Return email address is not as DEEP as it appears Hi depending on your Excel version you can allow formating columns in the protection dialog. (I think starting with Excel 2002). In all other cases no chance but to remo...