Format a number when that nunber duplicates

[xinex21]

Hi, My name is Rui, i'm from portugal, sorry for my bad english, i hope i can be hunderstood.

I need an excel file to do this:

i have one colune like this:

492
500
773
738
572
492 when i repetat this number i need that the first (492) be formatted with a color and continus like this
200
572 fotmat the first 572
492 formatt the next 492 but the actual number stays withou formatting!


Regards!

[AndyLitch]

Assuming your numbers are in Cells A1:A9 then :
=AND(COUNTIF(A1:A$9,A1)>1,COUNTIF(A$1:A1,A1)=1)

applied as a conditional format will only highlight the first of each duplicate

A1 is the top cell in the range.. A9 the bottom... The above function is applied to cell A1 and then extended down the column.

[newdoverman]

This uses Conditional Formatting.

[xinex21]

This uses Conditional Formatting.

Thanks for the awnser, but i need to when i write a duplicated number the excel format the duplicated numbers on top, but not the number that i wrote in that moment, that number that i wrote only be formatted whne i repeated again below!

[xinex21]

Assuming your numbers are in Cells A1:A9 then :
=AND(COUNTIF(A1:A$9,A1)>1,COUNTIF(A$1:A1,A1)=1)

applied as a conditional format will only highlight the first of each duplicate

A1 is the top cell in the range.. A9 the bottom... The above function is applied to cell A1 and then extended down the column.

Hi thanks, if you can put a file with this formula i apreciate because my excel is in portuguese the formulas was a little different!

[oeldere]

@xinex21

You want to get an solution.

Why should forummembers need to make a file for you?

Some forummember don't want to make a file.

You get better help if YOU add an excel file, without confidentional information, so a forummember can put the formula in the file.

[xinex21]

@xinex21

You want to get an solution.

Why should forummembers need to make a file for you?

Some forummember don't want to make a file.

You get better help if YOU add an excel file, without confidentional information, so a forummember can put the formula in the file.

repetição.xlsx

Sorry, but the file is very simple.

[oeldere]

See the attache file.

[xinex21]

See the attache file.

Is not what i want, i will try to be more specific, this file is to control employers, so each number means an enployer so imagine that 500 is my employer and in day one he work in a shift donīt matter what shift, in the next day 500 will do another shift, but some days after he can make again the same shift and to have no errors i need 500 do this shif and in excel is number stays without formating, but when he repeate this shift the last time that he do this shift format with a color, this is good to me to scale another employer to another shift, so this formula is for a month and the same empleyor do the same shift severel times but i want all the times be formated only the last time not!

Wait you hunderstand my bad english!

Regards!

[newdoverman]

Perhaps this is what you are looking for.

[xinex21]

Duplicates (3).xlsx

Perhaps this is what you are looking for.

Hi this is what i need!

here it is the file but hand made for me, i need it with formulas or conditional formated!

[newdoverman]

Inspiration isn't striking at the moment

[oeldere]

@xinex21

And how did you determine the colors in your sheet (if there is no data) to compare with.

Why is value 492 blue in case 1, 2 and 3 and white in case 4?

I don't see the logic in your file.

[xinex21]

@xinex21

And how did you determine the colors in your sheet (if there is no data) to compare with.

Why is value 492 blue in case 1, 2 and 3 and white in case 4?

I don't see the logic in your file.

Hello, in my file i formated the numbers mannualy, the logic of that its, when i want to put a employer to work i know that employer can do the shift because this employer (492) is free to do it

[DavidJAllen]

xinex21,

There is a conditional formatting option for duplicates. I applied this to a range (A1:A1000). Whenever a duplicate number is added then both duplicates are highlighted, if you delete one of the duplicates then the conditional formatting disappears.

Check out the attached file. I have included a screenshot so you can see which conditional formatting option you need to select.

Does this help?

Regards

David

[newdoverman]

I managed to colour the cells that you have no colour in. Perhaps that will work for you.

[xinex21]

I managed to colour the cells that you have no colour in. Perhaps that will work for you.

Hi, thanks, its almost there can you invert the procedure?
if i put 500 on the file, this number higlights, and the other 500's on top are non formatted, what i need is reverse!

[newdoverman]

I'm getting tired. How's this?

[xinex21]

I'm getting tired. How's this?

Thanks a lot!!! is that i need many thanks, i was almost thinkin thts was impossible!

[newdoverman]

You are welcome. I too was almost thinking that it was impossible. The formula in column B can be deleted if you like as it is only there to show how the Conditional Formula was developed.

[xinex21]

Livro Escala.xlsx

You are welcome. I too was almost thinking that it was impossible. The formula in column B can be deleted if you like as it is only there to show how the Conditional Formula was developed.

Hi again, i have workin on my workshhet and now its like that!

Many thanks once again!
The colmn B are in worksheet hidden, i have seen conditional formula in column A!!! Regards