Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to be noticed/to avoid equal values in a colum
Hi! I am working on a excel sheet that has a long colum with string values. I don't want the colum to contain any equal values. When I write new string values to the colum sometimes I am allerted if the value already excist (as I start writing the complete string appear with what is left marked in black). This is good because then I know if the value already excist in the colum and I don't retype it. However, I am not always allerted and then I end up writing the same value several times. Does anyone know how I can deal with this problem as I dont want the colun to contain any equal values? Thank you for all help. Irene -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to be noticed/to avoid equal values in a colum
See
http://cpearson.com/excel/NoDupEntry.htm In article , Irene wrote: Hi! I am working on a excel sheet that has a long colum with string values. I don't want the colum to contain any equal values. When I write new string values to the colum sometimes I am allerted if the value already excist (as I start writing the complete string appear with what is left marked in black). This is good because then I know if the value already excist in the colum and I don't retype it. However, I am not always allerted and then I end up writing the same value several times. Does anyone know how I can deal with this problem as I dont want the colun to contain any equal values? Thank you for all help. Irene |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to be noticed/to avoid equal values in a colum
I can't get this to work. I follow the given instructions but as I press the Error Alert tab i get a message saying that my formula contain an error. My list is currently in the range of B2:B1270 and each cell contains a string value. In the Data Validation box I choose Custom and type the formula: =COUNTIF($B$2:$B$1270,B2)=1 Anyone know why??? Irene(almost desperate :) -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to be noticed/to avoid equal values in a colum
=COUNTIF($B$2:$B$1270,B2)1 I would conditionally format the cell using a formula, so in cell b2 use the above. then choose a colour say to trun it red. If you type a duplicate the cell will turn red and you should notice it! Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to be noticed/to avoid equal values in a colum
I got the same error message again. I have never done formatting in excel before so as a new beginner I suspect I make some elementary mistake. I have tried to do the formatting both with the Validation and through the Conditional Formatting but maybe I am missing out on something else. Is there something else I must do before the formatting???? I have tried to highlight the cells in the range, highlight all cells in the colum and no highlight of the cells before the formatting but it doesnt seems to make a difference. Irene -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to be noticed/to avoid equal values in a colum
Just select the first cell b2 in this instance. Sometimes excel puts the formula in " " if this is the case they need to be removed and the formula need to start with a = sign once it works it can be copied down to the other cells with copy then past special formats Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to be noticed/to avoid equal values in a colum
Thank you so much for your help, I really appreshiate it :) I don't know what I do wrong but I still can't get it to work. I will try to detailed explained exactely what I am doing, maybe that will help. This is what I have, A list with colum A and B with data ranging from row 2-1270, so A1:B1270 As I make more entrance to the list (in colum *B*)I want to be allerted if the value already excist. I did the following: Put the marker on cell B2. Went to Conditional Formatting Chose Formula Is Typed in formula: =COUNTIF($B$2:$B$1270,B2)1 Chose to put the result in red color. When I press OK the little (annoying) paper clip allert me that the formula contains an error. And this part of the fomula becomes highlighted: $B$1270,B2 -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to be noticed/to avoid equal values in a colum
I just found out what the problem was The reason for why I couldn't get any of the formulas to work is because I am using the Norwegian version of Excel and apparently I need to use a semicolon(;) instead of the comma(,). Glad to have figured this out:) -- Irene ------------------------------------------------------------------------ Irene's Profile: http://www.excelforum.com/member.php...o&userid=34269 View this thread: http://www.excelforum.com/showthread...hreadid=540287 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list values equal in 2 worksheets? | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
colum B=average of last 10 values in A | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions | |||
lookup in colum a and compare values in colum b | Excel Worksheet Functions |