Creating a field to search and enter non duplicate data

Hello all. 

I have a team of 15 pople that all work with reference numbers, on occasion 
a number has to be sent to a different work group. I've created a spreadsheet 
for them to enter the numbers into, but we have been duplicating numbers. I 
want to create a field to enter th number into, have the field cross 
reference the exsting numbers, and if it does not find a match, enter the 
number and sort the list. 

I know how to create a custom macro that will accomplish what I want to do, 
but I'd prefer a static formula if it's possible. 
0
Utf
12/1/2009 9:08:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1222 Views

Similar Articles

[PageSpeed] 43

A formula can not do waht you ask, it can however show you if your entry
is a duplicate, to simply have a formula look at the cell you make an
entry in you could use this =IF(COUNTIF(A1:A100,B1)>=1,"Duplicate",B1)
enter your number in B1 and the formula should say Duplicate or show
your B1 value, you can use it in conjunction with conditional
formatting, Chip Pearson explains it very well here 'Duplicates In Data
In Excel' (http://www.cpearson.com/excel/Duplicates.aspx)

Bryan;574074 Wrote: 
> Hello all.
> 
> I have a team of 15 pople that all work with reference numbers, on
> occasion
> a number has to be sent to a different work group. I've created a
> spreadsheet
> for them to enter the numbers into, but we have been duplicating
> numbers. I
> want to create a field to enter th number into, have the field cross
> reference the exsting numbers, and if it does not find a match, enter
> the
> number and sort the list.
> 
> I know how to create a custom macro that will accomplish what I want to
> do,
> but I'd prefer a static formula if it's possible.


-- 
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=158623

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

0
Simon
12/2/2009 12:22:26 AM
Reply:

Similar Artilces:

Edit or Create a Partylist Lookup field
I have an issue - we have Cytrack running between our AVAYA phones and CRM 4.0 and I have enable the option that when a call comes through it opens a Phone Call activity and it automatically connects to the contact for the person calling in. Great except while we have all cleitns in as contacts - we also have clients either in as a Lead or an Opportunity when we are doign current business and I want to change the phone call activity recipient. I can do this but it comes up a "Look Up Records" screen and you havea drop down list to pick what entity you wish to locate the cl...

How to create a task view where completed tasks are omitted
Hello out there! I'm trying to create a task view where my hundreds of 'completed tasks' are omitted - leaving all other tasks that have yet to be completed. I've searched help and rung the customer support line but to no avail. Anyone know how? Would be much appreciated. Paul Create a custom view of your Tasks folder with a filter that says "Completed equals no" (you will find this on the Advanced tab when you click the Filter button). -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the...

Why does Letter Merge not work with custom fields in template ?
Hi , when I do a letter merge with my own template(which has its own fields), Letter Merge doesn't work properly. I did some investigation and found that I need to go to Data Fields and select the fields that I have added in my template (apart from the ones selected by default). If I do that it works properly. Ideally, when we share a template, it should work for others without having to select data fields. Users can't be expected to open the template everytime, see the fields there and select it in Data Fields. Can anyone tell me why it behaves like this? Is there an alternative? Th...

Instant Search, Advanced Find, Search Folders, and Windows Desktop
Instant search stopped working for me. Now if I search for even the letter "a", I get the following message: No matches found for: "a". When I ask "advanced find" to find any emails with categories contains TNE01, it returns no results. However, advanced find works somewhat. For example when I look for items in my inbox with "a" in the subject line, it returns 78 matches. When I use "search folders", to find emails with categories = TNE01, 14 items are returned. When I use "search folders" to find items with "WC" in freq...

Create Template
Hi i am new with Outlook How can i crate a Template so that when i send mail it should go my Co. logo and address evey time to other. pls help. Shital In news:13072DF7-B1A1-4ED3-8E94-4128335F3992@microsoft.com, shital shah <shitalshah@discussions.microsoft.com> typed: > Hi i am new with Outlook > How can i crate a Template so that when i send mail it should go my > Co. logo and address evey time to other. > > pls help. > > Shital I don't understand. Are you saying you want an e-mail signature automatically put at the bottom of your messages? Please provi...

Outlook 2007 Search Problem
I work at the Help Desk for a University. A client has called complaining that recently Outlook 2007's search bar started returning "bad results." For instance, if she searches for Smith, she'll get e- mails that were sent to Smith, or have Smith in the body, but not e- mails that are FROM Smith. She used to get better results. I pointed her to the advanced search feature (available in a sub-menu, or available right there when Instant Search is installed), and showed her how to search specific fields. She's still not satisfied, though. It used to work, she claims, and sh...

Formula to Autofill Info based on Other Data
Using: Excel 2000, on Windows 98 Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set. My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY. On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific inform...

Creating CRM Users 02-22-05
HI, Thanks for your response. No there are no strange characters, the two names are John Phillips and Steve McDonagh. These two users have been added to our pilot CRM system, which is independent of this live version, with out any problems. Cheers Ben can you try add a user through the web interface to see if that works? -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Ben Smallwood" <bsmallwo@softhome.net> wrote in message news:de61721b.0502220242.48137f63@posting.google.com... > HI, > Thanks for your response. No there are no strange characters...

Adding multiple items in unordered data to match a target amount
I have 3 columns of data - invoiced amounts (A), banked amounts (B) and commission charged (C). A = B + C For each B, there is one C. However, there can be multiple items in A for one item of B + C. Further, the rules for how commission is charged varies by amounts sold; I cannot calculate C by reference to A or B (individually) with any level of accuracy. I'm therefore looking for a way of selecting 1 or multiple items in column B, adding them to 1 or multiple items in column C and tracing this to 1 or multiple items in column A. Help would be gratefully received. ...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

Importing Data From Access Problem
I'm trying to import some query results from MS Access into Excel using the Import External Data function. Some of the queries are coming across OK, others are only bringing the column headers across and some are bringing partial data. Does anyone have any ideas why this may be happening? Thanks Helena ...

Preventing writing in a combo box field
Ok this is my problem: I've a combo box with several employee names. Of course that when the employee is chosing it's name it writes the first letter so that the combo box filters the names to the closest one. Access then higlightes the employee name execpt the letter he wrote. Employee then hits enter and Access displays its full name, has in the combo box options, neverthless I have a hidden form that copies employee name to feed other forms. The problem is that if employee chooses is name has discribed, Access will only select all the other letters of his name execpt the one ...

Duplicating reocords from main form and subform receiving Runtime Error 3022
I am trying to develop a command button that will duplicate the main form and the subform information and save it to another record within the same tables. I used Allen Browne's program and modified it to fit my tables. At first it would copy the main form and not the subform data so I made some changes and now I am getting the Runtime error 3022. I have worked on this off and on for about 2 weeks. Any help on getting this to run will be greatly appreciated. Below is my code: Private Sub Command78_Click() 'On Error GoTo Err_Handler 'Purpose: Duplicate the main ...

selecting rows of data
I have an excel worksheet with 4 columbs. I want to automatically select multiple rows of data according to search which will partly match one cell in the row and put the full ro as results in a new worksheet. for example 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete 22/03/03,Texas,90.78,incomplete 23/04/03,Oregon,467.99,complete If the search string is "New" how can I produce a new worksheet whic will contain 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete Any suggestions would be appriciated -- Message posted from http://w...

Apostrophe in search string
I have a simple SQL string that is run from a form when a field is updated: strSQL = "UPDATE tblDesign " & _ "SET " & strFieldNameForUpdate & " = '" & strDM & "' " & _ "WHERE DesignName = '" & strName & "' ;" It works fine except for where there is (rarely) an apostrophe in the strName field data. I cannot work out how many extra quotes to put into the sql build string to get round this. A debug.print of the string that causes a syntax problem is: UPDATE tblDesign S...

Backup Data
Blanket broadcast to anyone. Just got a new machine that has MS Money 03. Old machine had same. Tried to download backup from older machine to new machine but couldn't get a restore. Anyone have any ideas? In microsoft.public.money, cbauer wrote: >Blanket broadcast to anyone. Just got a new machine that >has MS Money 03. Old machine had same. Tried to >download backup from older machine to new machine but >couldn't get a restore. Anyone have any ideas? What did you try, and what happened as a result? Try simply copying the backup file from the old machine...

pattern of data
I have a table in the below format- For example Customer A and Dzip 678 is being served from ozip 099 twice and 011 once ozip dzip customer 099 678 A 011 678 A 099 678 A 121 875 B 122 875 B 122 875 B I need the below format for the above table. the data in the brackets indicate the no of times ozip appears in the table for a particular set of dzip,customer ozip dzip customer 099(2),011(1) 678 A 121(1),122(2) 875 B Please let me know the query which will give the data in the above format. Thanks in...

Serial Attributes, Lot Category Data
There is no ability to add attribute data to serial numbers in GP. Many industries, especialy Med Devices and Hi-Tech serialize thier product. Each serial number may have individual "attributes", incuding Rev, operator, date ,time, color etc. I would like to see the ability to define a serial category for each item, which in reality could be "Lot Category", as in effect, a serial number is a "lot" of 1. Consideration should be given to allow the look up of an attribute value during orderfulfillment or GI to a higher level MO Additionaly I would like t...

external data #3
I would like to be able to access external data from more than one source. Any help on how to do this would be fantastic. I would like excel to reach out to a Access database that I specify at the time it reaches out to get some external data. Usually I get external data through the "get external data", but that only connects to whatever datasource you initially choose. i'd like to be able to choose which Access database I am pulling data from at the time I bring it into Excel. hope that helps, Thanks, Lou Sanderson. "Ron Coderre" wrote: > I think we'l...

data links on subsequent worksheets
I have main data entered on worksheet 1 that I have linked to subsequent worksheets, however when there is no data on worksheet 1 I get 0's printed in the data space. How doI eliminate the 0? If you want to hide all zero values on the sheet, Tools --> Options --> [View] tab and clear the checkbox next to "Zero Values" In Excel 2007, that's "Office Button" --> [Excel Options] --> then [Advanced] in left pane and scroll down to the "Display Options for this Worksheet" and clear the box next to "Show a zero in cells that have a...

Data validation for 2 valid values
I want a cell to allow only 2 valid values but not have a drop-down list appear. How do I do that with Data->Validation? Data>validation>allow>list, put the 2 values separated by a comma in the source box, uncheck in cell dropdown -- Regards, Peo Sjoblom "Hall" <hall@garp.org> wrote in message news:ujuB%23w52DHA.2000@TK2MSFTNGP11.phx.gbl... > I want a cell to allow only 2 valid values but not have a drop-down list > appear. > > How do I do that with Data->Validation? > > Using a list, as Peo suggested, will force users to match the ...

Importing data from Maximizer
Hi: What experience has anyone had with importing Maximizer data into MSCRM using either the Data Migration tool, Scribe, or a combination of both? Scribe don't handle this seamlessly, you apparently need to export to a .csv file first. Is this still quicker using this process with Scribe, than with the DMF? Many Thanks Cathy Allington Hi, We are also looking at migrating Maximizer into CRM. If you would not mind, could you please let me know if you find anything more on this. I would truly appreciate it. Shauna skoppang@shaw.ca "Cathy Allington" wrote: > Hi: > ...

Delete duplicate rows from a list in Excel
Hi, Below are instructions on how to "delete duplicate rows from a list in Excel". I learned about this tip from the Microsoft Office Assistant website. However, each time I try step 4, my list is not filtered and no records are hidden. Is there a secret I am missing to make this work? Thanks for your help! -Greg http://office.microsoft.com/en-us/assistance/HA010346261033.aspx Applies to Microsoft Office Excel 2003 Microsoft Excel 2002 A duplicate row (also called a record) in a list is one where all values in the row are an exact match of all the values in another row. To delet...

Question about ho to insert data in SQL
Hello I have a adodb recordset which always have 2000 records The code is like this 'fisrt i'm opening the database in which i will insert the data conn.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=MyDate;Integrated Security=True" cmd = New SqlCommand("select * from table", conn) conn.Open() 'Then i'm opening a ADODB recordset rst.Open("http://www.domain.gr/data.asp") 'the rst filled with 2000 records 'now i want to insert them to the sql database 'i'm using While Not rst.EOF dim Ins as...

Pasting Onto Filtered Data
Excel should develop a paste special function that allows you to paste data onto filtered data without effecting hidden cells. At the very minimum, a warning that data is being pasted on hidden cells during a paste onto filtered data would be helpful. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I ...