Excel help, can it even do this?

[Moonpie2]

Hi all

I tried to find the answer to this to no avail.
Does anyone know if this is possible?

https://image.~ibb.co~/hp9C8p/fgdfgd.jpg

Basically, I want to put an X in the cell in column C if the cell in column I is highlighted green and then just copy paste all the way down column C.
Sounds simple?

Any help?

 

[Lartyconshayo]

Possibly condtional formatting in reverse ?

 

[Moonpie2]

Unless you have any idea how to do that, then I know I don't.

 

[Lartyconshayo]

https://stackoverflow.com/questions...ditional-formula-based-on-the-color-of-a-cell

 

[Lartyconshayo]

I'm too rusty to do it but seems to be the ballpark alright. That stack overflow link seems pretty same question as yours with an answer

 

[skyripper]

If this is a one-off, Filter by colour, then add a column with a value per colour.
Your formula can use that value as part of the IF statement.

 

[Moonpie2]

That stackoverlow looks promising.
A little convoluted, but might save time down the line.
I'll give it a whirl later. Also potentially the skyripper suggestion too !!

 

[Moonpie2]

Solved quite easily.
Here's the solution.

I was using conditional formatting to highlight the duplicates in column I using..... =COUNTIF(I:I,I1)>1

So then instead of doing another formula to detect the highlighting, someone suggested to forget the conditional formatting in column I and just go straight for column C with.... =IF(COUNTIF(I:I,I1)>1,"X","")

Did the trick nicely

Only problem is that once the duplicates are removed, the Xs also go, so I need to copy and 'paste special' them. No biggie !!

 

[skyripper]

You do know Excel has a Remove Duplicates function?

 

[Moonpie2]

Yes, but I don't want them removing.
Well.... not straight away.....

It's hard to explain

 

[Jamauk]

I used conditional formatting to do this the other way around last week, whereby if cell A1 = "Saturday" then colour grey. I can't remember the code off hand. So as said above, conditional formatting in reverse.