SOLVED

avoid to repeated data values in excel formula

Copper Contributor

I understand there's a formula you can write down manually in a single cell of a column and then drag down, to avoid repeated data values ​​in excel with the "=COUNT.SI(XXXXXX)=1" but i forgot how to do it correctly. I know there's a function in Data menu > Data validation, but the rank i will use is the WHOLE COLUMN, not just a short rank. That is why I want to manually write the formula down in the second cell (my chart has headers) and then drag the formula down, as my chart fills up. Can anyone can help me, please?

7 Replies

@DifferentMink01 

Are you referring to using this formula:

=IF(COUNTIF($A$1:A1,A1)=1,COUNTIF($A$1:A1,A1),"")

 

To do this?

 

Patrick2788_0-1670270052215.png

 

Hello @Patrick2788! No, that formula is to count items. I'm looking for the avoid repeating data formula when you are filling up a database. Something like this, see column B, please...

 

DifferentMink01_2-1670430196194.png

the name of the client will repeat, but the code of the item in column B must not :( That is the formula I'm looking for =count.if(x:x;x,x)=1, but I don't remember how to write it down inside column B manually and then drag the formula down alongside the whole B column, without the data validation option in the data menu

 

 

best response confirmed by DifferentMink01 (Copper Contributor)
Solution

@DifferentMink01 

The screenshot is a bit blurry, but I believe I understand your request. Please see attached workbook.

I think that is what I'm looking for! ( i did not understand the attached workbook, but I searched in excel for the phrase "conditional formatting" and I got this result: "=if(logical_test,value_if_true,value_if_false") do you know how to do it, please? :3
The conditional formatting is using a formula to determine if the ID counted in a given cell is the first instance encountered. If it's not instance 1, the conditional formatting applies custom format ;;; which is a way of saying don't show cell contents.

@Patrick2788 can you help me make it please? I've tried and tried and i cannot doing it alone :'(

@DifferentMink01 

I may have to see the workbook but using this sample for discussion:

Patrick2788_0-1672852752914.png

There are 3 things critical to getting this to work.

1. Select the entire range that will be affected by the conditional formatting. Do not include the header.

2.  The correct referencing styles for the COUNTIF as seen above.

3. Starting the formula at the correct row number.  Typically, if the header begins in row 2, the formula must start at row 2.  Note how the formula only references row 2.  If your selection is correct and the referencing styles are tight, Excel will apply the rule correctly to the entire selection.

1 best response

Accepted Solutions
best response confirmed by DifferentMink01 (Copper Contributor)
Solution

@DifferentMink01 

The screenshot is a bit blurry, but I believe I understand your request. Please see attached workbook.

View solution in original post