formula repeated numbers

[max_max]

Hello everyone.
I have to include this formula in C2:C34000

=SE(E(A2<>"";CONTA.SE($A$2:A2;A2)-1);"ripetuto";"") (italian)

=IF(AND(A2<>"",COUNTIF($A$2:A2,A2)-1),"ripetuto","") (english)

It serves to highlight repeated numbers.
I tried to put it in the range but hangs around.
Is there a way to insert it faster?
Thanks in advance.
max_max

[JBeaucaire]

COUNTIF can be a memory hog when you create 10s of thousands against the same data set ad nauseum. If all you need is to spot if a value has occurred already, MATCH is distinctively quicker.

=IF(AND(A2<>"", ISNUMBER(MATCH(A2, $A$1:$A1, 0))), "ripetuto", "")

...copied down ad nauseum.

[max_max]

Hello Jbeaucarie.
It seems faster.
In italian the formula is:

=SE(E(A2<>""; VAL.NUMERO(CONFRONTA(A2; $A$1:$A1; 0))); "ripetuto"; "")

Thank you.
max_max

[JBeaucaire]

As it appears you've reached a conclusion, I've marked this thread SOLVED for you.
FYI, this is done through the Thread Tools located above the first post in this thread. Thanks.

[max_max]

Hello the formula works well.

=IF(AND(A2<>"", ISNUMBER(MATCH(A2, $A$1:$A1, 0))), "ripetuto", "")

only two small change.

In the cells there are data with asterisk
G-CSF 3:55*
H-CSF3.77*
H-GS71.29*
H-MR135.11*
H-MR135.99*
H-MR135.21/S*
H-MR135.278*
The formula recognizes this data as repeated and not exact, perhaps by entering text in the formula?

You can add as well as "repetuto" even "non ripetuto" for cells not duplicated?

Thank you in advance.
max_max

[Beamernsw]

What is that data matching to?

e.g. is H-CSF3.77* matching with H-CSF3.7757888 therefore using the * as a wildcard?
Do you actually need to look for matches with those lines or can you ignore checking lines with an asterix?

If you can ignore checking lines with * for repeats then:-

=IF(OR(A2="",RIGHT(A2,1)="*"),"",IF(ISNUMBER(MATCH(A2, $A$1:$A1, 0)), "ripetuto", "non ripetuto"))

[max_max]

In the column A there are these data.

G-CSF 3:55
G-CSF 3:55*
----------------------
H-CSF3.77
H-CSF3.77*
----------------------
H-GS71.29
H-GS71.29*
----------------------
H-MR135.11
H-MR135.11*
--------------------
H-MR135.99
H-MR135.99*
---------------------
H-MR135.21/S
H-MR135.21/S*
---------------------
H-MR135.278
H-MR135.278*

with asterisk and without asterisk
The formula recognizes this data as a duplicate, is not accurate.
I hope I explained.
max_max

[Beamernsw]

OK, well if you don't need to check for repeats with * then the formula above should work, but if you do need to look for repeats including * then this should do it hopefully:-

=IF(A2="","",IF(ISNUMBER(MATCH(IF(RIGHT(A2,1)="*",A2&"~*",A2), $A$1:$A1, 0)), "ripetuto", "non ripetuto"))

[José Augusto]

Hi
Try these array formula (CSE --> CTRL+SHIFT+ENTER)
=IF( ISNUMBER(MATCH(SUBSTITUTE(TEXT(A2,"@"),"*","|"), SUBSTITUTE($A$1:$A1,"*","|"), 0)),"ripetuto", "")

=IF( ISNUMBER(MATCH(SUBSTITUTE(TEXT(A2,"@"),"*","|"), SUBSTITUTE($A$1:$A1,"*","|"), 0)),"ripetuto", "")

You can subtitute "|" with other char not used, for example CHAR(7)

Regards

[max_max]

Hello to all.
Thank you BeamerNsw, the second formula works.
Thanks to Jose Augusto but "SUBSTITUTE" with "|" it does not work.
Thank you all.
max_max