Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I am constantly making long lists of car reg numbers. Is there any way I can validate a column so it will not allow me to enter same number twice? If anyone knows the answer it will save me a lot of trouble!
Thanks and regards
Nick
Thanks for your quick reply. I have about 50 car numbers - can I set 50 unique keys in the same database. Sorry, I am a real excel newbie so it may be a silly question!
Thanks again
Nick
0 
Nick,
Create a very simple database in Access. You may need only one table.
Set the reg number as the Primary Key, and to not allow duplicates. Email me if you need details.
0
Whereas you are an Excel newbie and if you don't have Access then depending on what you have in all you may want to try the Advanced Filter.
I will assume that all you have is the 50 reg numbers to enter and that is all that you have on your worksheet.
Or you already have a list of hundreds of reg numbers and you want to add 50 more to the bottom of the list but when you are done entering you want to be sure that there are no duplicate numbers.
Let's also assume that all the numbers are in Column "A" and there is no data in any other columns. Otherwise the below instructions would have to be modified alot.1. Enter your numbers at the bottom of the list. (for this exercise let's say that when you are done entering that there a total of 250 sets of reg numbers and a column heading named "Regs".
2. Click on A1 to select that cell.
3. Hit Ctrl+Shift and the Down arrow to select all of your data in column A.
4. In the Menu at the top click on Data, Filter, Advanced Filter.
5. Select Copy to another location.
6. Copy to = B1
7. Select Unique records only at the bottom.
8. Click Ok.9. Right Click on the column Title "A" (above A1) to select the entire column A.
10. Select Delete.There will be no duplicates.
Let's assume that you do have data in column B such as the make of a vehicle and Model in Column C.
Then your List range needs to include that data.Within the Advanced Filter dialog box to the right of each of those fields is the data selection tool. Click on the first one and the dialog box becomes small so that you can select your range.
Hit Ctrl+Shift+End to select all of your data on the worksheet for your "List range".For the Criteria range Click on A1 and then Ctrl+Shift+Down arrow key because that is the criteria that you want to check for against duplicates.
For the Copy to location type in D1.
Don't forget to select "Unique records only".When done delete the previous columns A-C.
HTH too,
Bryan
0
Nick - Bryan re: The Excel Method
There may be a problem if you have a duplicate entry not caused by entering a duplicate number but by entering a wrong number. If this is a multi-field entry you may accidentally delete a record instead of correcting it.
The database method is more secure in that you would know if it was a duplicate before you enter the remaining fields.
0
Fred, Is this database established within Excel?
If so, can you provide a quick-step guide?
I am interested.
Bryan
P.S. I am not so sure that an invalid record is any better than a missing record in a database.
0
No, it is not established within Excel.
It is the same method as Jennifer's in Access.
I agree, both situations are equally bad. An incorrect record and a missing record. However, at least part of the data is present in the incorrect record. Still a job for proofing the data. Having the correct registration number with the rest pf the data partially wrong is still a possibility.
The initial concern was not entering duplicate numbers. So I feel that catching that as soon as it is entered is the best solution. Catching missing and wrong data is still the job of proofreading. Especially if the data is like names and addresses.
0
U can have it this way :
give a name to ur column A.
Regarding to each row, enter a if(isnumber(lc(-1);vlookup(lc(-1);"name";1;false;""). It'll search for u number entered in column a in its own zone (ur A column). If u've got #N/A, ur number is not duplicate.
Ur named zone must be enough long to allow u to enter a number.
Is my explaination good..?
0
Hoops, i've missed up something: u must use a transition row to test before u enter. I mean :
A is the zone which contains ur existing numbers; B is the temporary row where u enter and C the row where u test. I "B" number already exist in "A", u try another. It's doing the job on mine.
0
Fred, I asked because I know you can save a file in Excel 97 as a database file (DBF2,3 or 4) but have never used it. I don't know how it would be used.
I have not used Access that much. I took a course in it but everything that "I" could do in Access I could do in Excel easier. So, I concentrated on using Excel. I know people 'love' using macros to perform functions and reports in Access but I felt like I couldn't get enough "Pretty" out of the Access reports (although I understand that it can be done).
Taxi, I have not tried your formula although I will before this thread is dead. I have not yet implemented using the offset and this will be my first lesson.
In regards to this poster's (Nick from Greece) thread. I was trying to figure how to do do what you have apparently accomplished. I was trying to figure out how to do it within the existing columns and thought Conditional formatting would do it but I did not know how to formulate your expression.
Can you or would you try to use that in a conditional format for the 'assumed' column A that is already existing where Nick's reg numbers are being entered?
I suspect that you are capable. I find it tricky getting the correct systax for IF statements within the Conditional formatting dialog box but no problems within a spreadsheet cell.On a side note: I had seen that there were more posts added to this thread from within My Computing.net screen but I was also alerted to it by 'Chnos' whomever that may be I don't know so I also don't know why Chnos alerted me.
Regards,
(Boy am I getting long winded lately:)
Bryan
0
Bryan, good guess, I use FoxBase (db3) in DOS. EXCEL exports Column Header Names as field names. Also works in Excel-2000.
I have used xbase for a while, mostly with an accounting package. I just take a routine and rewrite a few things. I also use BASIC mostly compiled.
Like you, I am not much into using Access. I think windows apps have too much overhead. A lot of times I use windows as a shell manager, having 3 or 4 DOS windows open when I do program development.
0
Chnos is my computing.net's login...it's a little complicated, but i use both "names" in different places.
The way i gave was: u assume ur "A" column contains already existing numbers, and the place u'll put ur new number after verfification. This "A" column has a name to be used in a formula. U choose a row, in column B where u'll always enter ur number to be tested. In another row, next to this first one, u tell it "check if the number i've been entering is present in column A". What do u mean with a conditionnal formatting, i don't see clearly what u want to do..?
0
The formula:
=if(isnumber(lc(-1);vlookup(lc(-1);"name";1;false;"")I guess I don't understand the formula as to what it is saying as a statement. Particularly the "lc". What is "lc"?
What I was wanting to do is use the Conditional formatting dialog box to input a formula to look in all cells above the active cell for duplicates.
I am unable to get the OFFSET function to work within the Conditional formatting box.
Cell Value, Is Equal to,
=$A$2:OFFSET(A3,-1,0)
The above will see A2 only.
Either my syntax is bad or OFFSET can not be used in a defined range within a Conditional format.
=OFFSET(A2:A3,-1,0) will change to A3:A4, A4:A5 and so on. I/we need $A$2 to remain constant.Bryan
0
My Response - it's a very busy day at work today and this is the first chance I've had to check my replies.
Firstly I's really surprised that so many of you have taken so much time and trouble to help with my problem. Really I am grateful and would like to thank all contributers.Phew! Now, when I've got a moment I shall try and put some of your hepful suggestions into practice.......
In the meantime thank you all again
Best regards
Nick
0
Nick,
Do check back because I have a thread on this subject working on the OzGrid Excel forum board. I have it working with numbers with their help and am just clarifying how to use it with data that contains text.
http://ozgrid.com/forum/showthread.php?t=20234 if you want to see it.
Regards,
If you see this 'hidden' message then please advise. The font color setting is #FFFFC8.
Bryan
0
"lc" is the reference when u don't use "as lotus" reference style in excel options.It replace the $ sign for example L(-1)c is a variable reference which reads the line located upper ur cell in the same column, and so on....Send me a sample of what ur doing...my mail is in indentity and u can get it.
0
So 1c(-1) is One C versus the letters LC meaning 1c is the active cell and (-1) would reference one cell above the active cell.
I tried using it but now am getting an error message from Excel that I did not get previously advising me that I can not use a reference to a range of cells within a conditional format. You can only refer to a single cell.
It is Nick's problem that I am working on and not my own. I just happened to use numbers instead of text in my data and the instructions at OzGrid are for numbers but works just fine for numbers. I don't underatand it but it works.
Still no reply there about the text.I'll send my spreadsheet and you can see by adding any number at the bottom of the list it will turn Red if it already exists in that column.
View the Named range and the conditional fomatting.Bryan
0
Yes, plz send it. I want to test it with a small exel database, includind numbers and characters in different fields, so i can see if i can obtain the same alert in the data custom entering form.
0
I did send it on the 29th immediately after posting response #18.
With Subject: http://computing.net/office/wwwboard/forum/3046.html - Bryco
It did not come back as undeliverable either.
Send it again?
Bryan
0
OK, well received . Works OK (exception of number 1 (coz conditionnal formattin is ">1)"), with numbers,letters, and both in the same row. Ok, understood the way of doing.Little bit complicated, but we always learn something in this forum...!
0
My thread at OzGrid did get the reply I was looking for. I haven't tried it yet.
Nick, have you tried it?
Bryan
0
Too simple.
Assuming A1 contains your column Label.Conditional Format A2 as:
Formula is, =COUNTIF(A:A,A2)>1
Format as Red
Click Ok.Right click on A2 and select Copy.
Select A3 and then Ctrl+Shift+Down arrow until it selects all cells in the column.
Right click on A3, select Paste Special, Formats Only.A duplicated entry will change to Red and so will the entry it is duplicating.
Thanks to OzGrid!
Nick, I hope you see this,
Bryan
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
